Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-17-2018, 12:12 PM
14spar15 14spar15 is offline Formula for number sequence Windows XP Formula for number sequence Office 2000
Advanced Beginner
Formula for number sequence
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default Formula for number sequence

I had earlier posted this on the Excel forum someone suggested I try the Excel Programming forum.


I’m trying to find a formula to create a sequence of numbers. The starting number would be in A1 and the ending number in B1. So if A1 = 88 and B1= 93 the result in the formula cell (C1) would be 88 89 90 91 92 93. I tried using =Row(Indirect(A1”;”&B1)) and I could get the correct results. When I record the process for a Macro (which is what I really need) it at first seems to work but when I change A1 or B1 to another range it does not work. When I look at the recorded code I am getting this.

Sub Macro1()
Range("C1").Select
ActiveCell.FormulaR1C1 = "88;89;90;91;92;93"
End Sub


I tried changing it this and got an error


Sub Macro1()
Range("C1").Select
ActiveCell.FormulaR1C1 = "ROW(INDIRECT(A1&":"&B1))"
End Sub

This was when I was thinking just a simple formula where there is a A1 +1 loop until the results equal B1 then break loop might be easier but it seems like this is way beyond my capabilities also and the formula is not so simple.
Reply With Quote
  #2  
Old 03-17-2018, 06:52 PM
NoSparks NoSparks is offline Formula for number sequence Windows 7 64bit Formula for number sequence Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

You're not clear on what you're wanting the result to be,
88 89 90 91 92 93 is not the same as 88;89;90;91;92;93
but you can make it whatever you want in a macro.

This macro will fire when you type a new value into A1 or B1.
You've given no ground rules for the numbers in A & B column so I just put some in that may or may not be practical for your purpose.

Right click the sheet tab, select View Code and paste this into the sheet module.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer, str As String
    
'limit to single cell
If Target.Count > 1 Then Exit Sub
'limit to row 1
If Target.Row <> 1 Then Exit Sub
'limit to columns A and B
If Target.Column > 2 Then Exit Sub

'clear anything existing in C
'disable events so this macro doesn't call itself when it changes a cell
Application.EnableEvents = False
Cells(Target.Row, "C").ClearContents
're-enable events
Application.EnableEvents = True

'make sure both cells have numbers
If Not IsNumeric(Cells(Target.Row, "A")) Or Not IsNumeric(Cells(Target.Row, "B")) Then Exit Sub
'make sure A cell is smaller than B cell
If Cells(Target.Row, "A") >= Cells(Target.Row, "B") Then Exit Sub

'put together what's to go into C1
For i = Cells(Target.Row, "A").Value To Cells(Target.Row, "B").Value
    str = str & ";" & i
Next i

Application.EnableEvents = False
'write the string to C, remove the leading ;
Cells(Target.Row, "C").Value = Mid(str, 2)
Application.EnableEvents = True

End Sub
Let me know if this is something you can work with.
Reply With Quote
  #3  
Old 03-18-2018, 03:08 PM
14spar15 14spar15 is offline Formula for number sequence Windows XP Formula for number sequence Office 2000
Advanced Beginner
Formula for number sequence
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Thanks for the help here. I appreciate the extra time you spent documenting the process. The checks and bounds placed in the code makes this run rock-solid. There is a slight problem here as I kind of over simplified what I was trying to do here. What I’m doing here is part of several sequences. You had instructed me to put this into a sheet module so that it will automatically fire when the A1 and B1 values are entered. I actually need to run the macro with a button on the worksheet and I’m most familiar with seeing the macro in the macro list. I’m trying to convert this code to do just that and I keep running into errors. Also the cell locations are L4 for the low-value number, M4 for the high-value number and N4 for the results.
Reply With Quote
  #4  
Old 03-18-2018, 04:31 PM
NoSparks NoSparks is offline Formula for number sequence Windows 7 64bit Formula for number sequence Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Knowing you'll be using a button simplifies things.
Completely delete that previous event code from the sheet module and paste this into the button assignment.
Code:
Sub Button1_Click()

Dim i As Integer, str As String

'clear anything existing in N
Cells(4, "N").ClearContents
'make sure both cells have numbers
If Not IsNumeric(Cells(4, "L")) Or Not IsNumeric(Cells(4, "M")) Then Exit Sub
'make sure L cell is smaller than M cell
If Cells(4, "L") >= Cells(4, "M") Then Exit Sub
'put together what's to go into N
    For i = Cells(4, "L").Value To Cells(4, "M").Value
        str = str & ";" & i
    Next i
'write the string to N, remove the leading ;
Cells(4, "N").Value = Mid(str, 2)

End Sub
Macros that are Private do not show in the macro dialogue.
Neither do any with parameters within the brackets.
This is good to know because with no sheet names used code always uses the active sheet.
No problem when run from the button on the sheet but you probably don't want somebody accidentally running it from the macro dialogue when a different sheet is active.

Hope this helps.
Reply With Quote
  #5  
Old 03-18-2018, 07:00 PM
14spar15 14spar15 is offline Formula for number sequence Windows XP Formula for number sequence Office 2000
Advanced Beginner
Formula for number sequence
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Perfect... Thank you
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula for number sequence Create a sequence of numbers using a formula 14spar15 Excel Programming 9 03-19-2018 01:15 AM
Formula Assistance for restricting the number of for analysis gspikesr Excel 3 05-02-2016 11:28 PM
Formula for number sequence Need an excel formula to remove dashes in a number sequence jyfuller Excel Programming 3 02-27-2013 10:32 PM
Formula for number sequence Formula to add number of pallets shipped piglovindillard Excel 2 08-13-2012 06:40 AM
Formula for number sequence Excel sheet Sequence Number belloffice Excel 1 02-25-2010 02:41 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft