Populate range with vba code

erikalcala

New Member
Joined
Jan 19, 2017
Messages
3
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
Code:
[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]


 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,344
Office Version
  1. 365
Platform
  1. Windows
If I'm understanding, then maybe something like this;

Code:
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

New Member
Joined
Jan 19, 2017
Messages
3
Many thanks alansidman, I tried your solution but I get an error when using

Code:
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:
<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
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,344
Office Version
  1. 365
Platform
  1. Windows
Your responses should have been A, 10 and 1. Because you entered A1, 10 and A1, you got a mismatch.
 

Forum statistics

Threads
1,136,331
Messages
5,675,156
Members
419,552
Latest member
jsanjur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top