Populate range with vba code

[erikalcala]

Hi, I've been looking how I could populate a range with a vba expression but I haven't had any success, I've checked the sections on loops and arrays but I can't come up with a solution
Any help would be much appreciated, I wish I could populate a specific range lets say N10:N50 with values starting from X and ending in Y but with a step constraint

I tried this with no success

[FONT=monospace]'create the array[/FONT]
[FONT=monospace]Dim Tvect As Variant[/FONT]
[FONT=monospace]Dim Trange As Range[/FONT]
[FONT=monospace]Dim countvar As Integer[/FONT]
[FONT=monospace]Tstep = 50[/FONT]
[FONT=monospace]countvar = Range("N10").Value 'the value of the cell N10[/FONT]
[FONT=monospace]For countvar = X To Y [/FONT]
[FONT=monospace]Range("N10" & countvar).Value = countvar + Tstep 'starting from N10 and downwards[/FONT]
[FONT=monospace]Next countvar[/FONT]

[FONT=monospace]'writing to worksheet[/FONT]
[FONT=monospace]'Set Trange = Range("M10")[/FONT]
[FONT=monospace]'Set Trange = Prange.Resize(UBound(Tvect), 1)[/FONT]
[FONT=monospace]'Trange .Value = Application.Transpose(Tvect)[/FONT][COLOR=#333333]

[/COLOR]

 

[alansidman]

If I'm understanding, then maybe something like this;

Sub AutoNumberFill()
    Dim x As Long
    Dim rn As Long
    Dim cn As String, sn As Long
    cn = InputBox("Which Column to fill?")
    rn = InputBox("How many rows to fill?")
    sn = InputBox("Which row to start fill?")


    Application.ScreenUpdating = False
    Application.StatusBar = "Macro Running"
    x = InputBox("What is the starting number?")
    Range(cn & sn).Select
    Do Until ActiveCell.Row = rn + sn
        If ActiveCell.EntireRow.Hidden = False Then
            ActiveCell.Value = x
            x = x + 50
        End If
        ActiveCell.Offset(1).Select
    Loop
    Application.ScreenUpdating = True
    Application.StatusBar = "Completed"
End Sub

 

[erikalcala]

Many thanks alansidman, I tried your solution but I get an error when using

Sub AutoNumberFill()
    Dim x As Long
    Dim rn As Long
    Dim cn As String, sn As Long
    cn = InputBox("Which Column to fill?")     ' I used A1
    rn = InputBox("How many rows to fill?")   ' I used 10
    sn = InputBox("Which row to start fill?")   ' I used A1




    Application.ScreenUpdating = False
    Application.StatusBar = "Macro Running"
    x = InputBox("What is the starting number?")
    Range(cn & sn).Select
    Do Until ActiveCell.Row = rn + sn
        If ActiveCell.EntireRow.Hidden = False Then
            ActiveCell.Value = x
            x = x + 50
        End If
        ActiveCell.Offset(1).Select
    Loop
    Application.ScreenUpdating = True
    Application.StatusBar = "Completed"
End Sub

I got a runtime error 13 type mismatch

The solution in other forum was

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">MyStart = 10
MyEnd = 33
MyStep = 3

Range("N6").Value = MyStart
Range("N6:N50").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=MyStep, Stop:=MyEnd, Trend:=[COLOR=darkblue]False[/COLOR]</code>

And is working nice, I'm looking forward to learn as much excel as I can this upcoming months

Many thanks

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-programming-vba-macros/1220059-populate-range-with-vba.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[alansidman]

Your responses should have been A, 10 and 1. Because you entered A1, 10 and A1, you got a mismatch.

 

[erikalcala]

Won't happen again, thanks for your solution alansidman