#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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.
|
#4
|
|||
|
|||
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 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. |
#5
|
|||
|
|||
Perfect... Thank you
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
Need an excel formula to remove dashes in a number sequence | jyfuller | Excel Programming | 3 | 02-27-2013 10:32 PM |
Formula to add number of pallets shipped | piglovindillard | Excel | 2 | 08-13-2012 06:40 AM |
Excel sheet Sequence Number | belloffice | Excel | 1 | 02-25-2010 02:41 PM |