#1
|
|||
|
|||
Create a sequence of numbers using a formula
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 but I could not figure out how to deal with array results, such as copying the results and using in another application. I found some work arounds to use the array result but when I tried recording this into a macro it did not work correctly so I just want to go to a simple formula. Any ideals? Thanks |
#2
|
|||
|
|||
If you want the numbers between value in A1 and value in B1 as an array, try:
=ROW(INDIRECT(A1&":"&B1)) This formula does not require array-entering. Select the cell with above formula and press F2, then F9 and you should see the numbers between 88 and 93 if you have 88 in A1 and 93 in B1. |
#3
|
|||
|
|||
Thanks for taking the time to respond here. I was messing with that approach for hours last night but I don’t have a very good understanding of how arrays work. When I ran that my results were ={88;89;90;91;92;93}. When I record the process for a Macro 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" Range("D2").Select End Sub I tried changing it this and got an error Sub Macro1() Range("C1").Select ActiveCell.FormulaR1C1 = "ROW(INDIRECT(A1&":"&B1))" Range("D2").Select 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. |
#4
|
|||
|
|||
I am not sure what you want if you can't use the formula:
=ROW(INDIRECT(A1&":"&B1)) If I shall try to do more please explain in more detail what you want. What exactly do you expect to appear in C1? |
#5
|
|||
|
|||
What I need is a macro that I can run after data is entered into A1 and B1 and end up with the expected results in C1. The =ROW(INDIRECT(A1&":"&B1)) formula works fine if I run it manually but when I try to record the process into a macro I run into problems. The code that is in the recorded macro seems to be tied to the results of the A1 and B1 values that were used in the macro that was recorded so if I change A1 or B1 I do not get the expected results.
|
#6
|
|||
|
|||
Then you should have asked in the Excel Programming forum.
I guess you can do what you want with a formula, but it is difficult to say as you don't exactly specify what you want in C1 |
#7
|
|||
|
|||
If you want
88;89;90;91;92;93 in C1 you can use the following array formula (on condition you have Office 365): =TEXTJOIN(";",TRUE,(ROW(INDIRECT(A1&":"&B1)))) Array formulas must be completed by holding down Ctrl and Shift before pressing Enter. |
#8
|
|||
|
|||
What I’m wanting in C1 is it to start with A1 and end with B1 and everything in between values (in whole numbers). I don’t mind using =ROW(INDIRECT(A1&":"&B1)) but I need to do this in a macro. If A1=5 and B1=8 and the macro in run C1 = 5 6 7 8. If A1=11 and B1=13 and the macro is run C1=11 12 13. I don’t mind the fact that the numbers were separated by “:” as I can substitute those out.
|
#9
|
||||
|
||||
Moved to programming section as per request
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#10
|
||||
|
||||
New thread was created by OP on the same subject. Thread closed
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sequence numbers after each print | MauricioPillon | Excel | 0 | 02-15-2018 08:11 AM |
Wildcards - Sequence of Numbers at the Beginning of Each Paragraph | qubie | Word | 1 | 11-30-2017 02:03 PM |
How to assign numbers to letters and then create a formula to add them together for a rota | Mskapri | Excel | 6 | 04-01-2017 03:05 PM |
Need Help Deleting Numbers in Hour Format and Numbers in Sequence | rsrasc | Word VBA | 1 | 10-12-2016 02:49 PM |
How to Create a Locked Sequence of Buttons | kcackin | PowerPoint | 0 | 01-24-2011 02:43 PM |