enumerate numbers between two cells

[blicop]

Hi All,

I need help with this example:
Cell A1 has a value of 1
Cell B1 has a value of 10

On C1 I would like to have 1, 2, 3 , 4, 5, 6, 7, 8, 9, 10

Any help would be highly appreciated.

Thanks!

Basil

 

[wigi]

With an extra function, called a User Defined Function:

Function Enumeration(iFirst As Integer, iLast As Integer) As String
    For i = iFirst To iLast
        Enumeration = Enumeration & ", " & i
    Next
    Enumeration = Mid(Enumeration, 3)
End Function

Add the function to a normal module in your file, and then in any worksheet and any cell, you can use:

=Enumeration(A1,B1)

 

[Rick Rothstein]

Here is a UDF (user defined function) that you can use...

Function NumSeries(StartNumber As Long, EndNumber As Long) As String
  Dim X As Long
  For X = StartNumber To EndNumber
    NumSeries = NumSeries & ", " & X
  Next
  NumSeries = Mid(NumSeries, 3)
End Function

For you example, you would simply use this formula...

=NumSeries(A1,B1)

You can install the UDF by pressing ALT+F11 to go into the VB editor, clicking Insert/Module from it menu bar once there, and then copy/paste the above code into the code window that opened up. That's it... go back to any worksheet and use the UDF as shown above.

 

[wigi]

Omg... !

 

[blicop]

it works! very cool!

now if only vlookup can be used for the enumerated numbers...

 

[wigi]

now if only vlookup can be used for the enumerated numbers...

Well, having multiple numbers or entries in 1 cell is never a good idea in Excel. Did you know how many cells you have on 1 worksheet?

 

[blicop]

not sure if you meant rows - which is 45 in this case..

 

[wigi]

An Excel 2007/2010 worksheet has 1048576 rows. Prior versions had 65536 rows.

Why do you have only 45 rows?

 

[blicop]

excel 2010