How to create a consecutive between 2 number in excel

[Saher Naji]

for example, I have 33 in cell A1, and 40 in cell B1, I want a formula in cell C1, to write an arithmetic sequence from 33 to 40 like this: 33, 34, 35, 36, 37, 38, 39, 40

 

[Saher Naji]

actually the numbers I have like this: for example: 33-38 in cell A1, I tried to right this formula: "=RIGHT(B1,2)-LEFT(A1,2)" the result will be 5, I thought that I can use this number to generate a sequence starting from 33 (5) times to reach to 38, but I did not find a formula to help me

 

[lrobbo314]

One way using a VBA custom function.

ExpandRange.xlsm
	A	B	C
1	33	40	33, 34, 35, 36, 37, 38, 39, 40
Sheet3

Cell Formulas
Range	Formula
C1	C1	=CONSEC(A1,B1)

Function CONSEC(ln As Integer, rn As Integer)
With CreateObject("Scripting.Dictionary")
    For i = ln To rn
        .Add i, True
    Next i
    CONSEC = Join(.keys, ", ")
End With
End Function

 

[Saher Naji]

Thanks
Is it because I don't have Office 2019, or I entered the VBA code somewhere wrong?

 

[lrobbo314]

No, it should work fine on 2013. You get into the VB editor by hitting Alt+F11. The go to Insert, then select Module. Paste the code there, then use the formula like normal.

 

[Saher Naji]

That's great, and working very well, but could I apply this into one cell, the original data I have in one column, like this:
Check column A please,
as you see I hided Columns A and B, which contain (Left) and (Right) formulas

 

[Saher Naji]

I'm sorry, it's working,
I used your formula with small adjustment and it's working perfect

=CONSEC(LEFT(A1,2),RIGHT(A1,2))

Thanks a lot♥

 

[kweaver]

@Irobbo314's function is what you use per your last post or have the function handle it.
If you always use 2-digits, either way will work.

Function CONSEC2(ln As Variant)
Dim rn As Integer
rn = 1 * Right(ln, Len(ln) - InStr(1, ln, "-"))
ln = 1 * Left(ln, InStr(1, ln, "-") - 1)
With CreateObject("Scripting.Dictionary")

    For i = ln To rn
        .Add i, True
    Next i
    CONSEC2 = Join(.keys, ", ")
End With
End Function

 

[Saher Naji]

@Irobbo314's function is what you use per your last post or have the function handle it.
If you always use 2-digits, either way will work.

Function CONSEC2(ln As Variant)
Dim rn As Integer
rn = 1 * Right(ln, Len(ln) - InStr(1, ln, "-"))
ln = 1 * Left(ln, InStr(1, ln, "-") - 1)
With CreateObject("Scripting.Dictionary")

    For i = ln To rn
        .Add i, True
    Next i
    CONSEC2 = Join(.keys, ", ")
End With
End Function

what about the formula in this case?
I tested it with the same formula, I changed it from CONSEC TO CONSEC2, but the result: (#VALUE!)

 

[kweaver]

Without seeing what you used, it's difficult to tell. It worked on 2010 for me.

Book1
	A	B
1	3-5	3, 4, 5
2	5-12	5, 6, 7, 8, 9, 10, 11, 12
3	15-20	15, 16, 17, 18, 19, 20
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=consec2(A1)

Since you indicated you were using a dash between the values, be sure you have the cells formatted as text; otherwise, Excel will treat them as dates.