Creating a list of numbers using Loop

[Johnwayne]

Hi, my question is pretty simple.

i just need to know how to i create a list of numbers in Column B range from B10 to B200.

i will key in a number in A1, lets say 50
once i key 50 in "A1"
excel will loop and input numbers to B1 to B200
from 1 to 50 and it will stop once it reach 50.
eg.

B1 will show 1
b2 will show 2
b3 will show 3
up to 50 and will stop there

can it be done, please advise. im quite new in the loop function

 

[Richard Schollar]

Hi

When you say "will stop at 50" do you mean then that you will only have values in B1:B50? Do you need to do this in a loop? You could use the AutoFill method which would be faster and more succinct.

 

[Norie]

No need for a loop.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Application.EnableEvents = False
        Range("B:B").ClearContents
        Range("B1") = "1"
        Range("B1:B" & Target.Value).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
            Step:=1, Stop:=Target.Value, Trend:=False
    End If
    Application.EnableEvents = True
End Sub

 

[Johnwayne]

norie..

Can it be done using the loop function
what if my input is in A1 but the cell i want it to be numbered start from B3 onwards...
?

 

[Norie]

Yes it could be done using a loop, but why use a loop when you don't need one?

Is there something more to what you want to do?

As to starting in B3, easily done.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Application.EnableEvents = False
        Range("B:B").ClearContents
        Range("B3") = "1"
        Range("B3").Resize(Target.Value).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
            Step:=1, Stop:=Target.Value, Trend:=False
    End If
    Application.EnableEvents = True
End Sub