Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Closed Thread
 
Thread Tools Display Modes
  #1  
Old 03-16-2018, 10:39 PM
14spar15 14spar15 is offline Create a sequence of numbers using a formula Windows XP Create a sequence of numbers using a formula Office 2000
Advanced Beginner
Create a sequence of numbers using a formula
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default 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  
Old 03-17-2018, 03:50 AM
xor xor is offline Create a sequence of numbers using a formula Windows 10 Create a sequence of numbers using a formula Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

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  
Old 03-17-2018, 09:13 AM
14spar15 14spar15 is offline Create a sequence of numbers using a formula Windows XP Create a sequence of numbers using a formula Office 2000
Advanced Beginner
Create a sequence of numbers using a formula
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

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  
Old 03-17-2018, 09:44 AM
xor xor is offline Create a sequence of numbers using a formula Windows 10 Create a sequence of numbers using a formula Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

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  
Old 03-17-2018, 10:08 AM
14spar15 14spar15 is offline Create a sequence of numbers using a formula Windows XP Create a sequence of numbers using a formula Office 2000
Advanced Beginner
Create a sequence of numbers using a formula
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

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  
Old 03-17-2018, 10:19 AM
xor xor is offline Create a sequence of numbers using a formula Windows 10 Create a sequence of numbers using a formula Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

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  
Old 03-17-2018, 10:53 AM
xor xor is offline Create a sequence of numbers using a formula Windows 10 Create a sequence of numbers using a formula Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

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  
Old 03-17-2018, 10:56 AM
14spar15 14spar15 is offline Create a sequence of numbers using a formula Windows XP Create a sequence of numbers using a formula Office 2000
Advanced Beginner
Create a sequence of numbers using a formula
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

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  
Old 03-18-2018, 01:15 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Create a sequence of numbers using a formula Windows 7 64bit Create a sequence of numbers using a formula Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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  
Old 03-19-2018, 01:15 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Create a sequence of numbers using a formula Windows 7 64bit Create a sequence of numbers using a formula Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Closed Thread

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sequence numbers after each print MauricioPillon Excel 0 02-15-2018 08:11 AM
Create a sequence of numbers using a formula 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:28 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