Question regarding us a for next loop for inputting data into a column

[zoog25]

Hello all here is a question

Say for example i have 2 cells an a column. B2, C2 and column A. Now in B2 i have "p101 and C2 I have "p130". These two are the start and end of a range of numbers from p101 to p130. Each number to be put into column A so A1 = p101 and A30 = p130. Now I now by dragging down, excel does this, but i want to use a vba code because when i have to do it multiple times it gets a little confussing. Please help.

 

[DeBeuz]

Hi,

Try something like this:

Option Explicit


Sub Create_list()
    Dim C As Long
    Dim E As Integer
    Dim Prefix As String
    Dim R As Long
    Dim S As Integer
    Dim Splitpoint As Integer
    Dim Work As String
    
    'Get start
    Work = Range("B2").Value
    'Find splitpoint
    Splitpoint = Len(Work)
    While IsNumeric(Mid(Work, Splitpoint, 1))
        Splitpoint = Splitpoint - 1
    Wend
    Prefix = Mid(Work, 1, Splitpoint)
    S = CInt(Mid(Work, Splitpoint + 1))
    E = CInt(Mid(Range("C2").Value, Splitpoint + 1))
    R = 1
    For C = S To E
        Range("A" & R).Value = Prefix & C
        R = R + 1
    Next C
End Sub

Paul