Thursday, August 18, 2005

Auto Fill Macro


doakra wrote
I would like to write a macro that would look at a value in a given cell, then begin at another given cell and autofill the cells going down the column with sequential numbers, beginnig at 1 and ending at the number found in the first cell. Is this possible? Thanks!

I replied:

Use Insert | Name | Define and Add to name the ranges the source cell should be called source - if you have more than 1 then source1, source2 etc.
Then name the target cell target - the starting cell you want 1 to go in.

place this code in a module and run it.
Sub autofiller()
'macro written by Jethro Aug 1005
Dim sourcedata As Variant
Dim x As Single
'get source data
Application.Goto reference:="source"
sourcedata = ActiveCell.Value
'go to target cell
Application.Goto reference:="target"
'enter data
For x = 1 To sourcedata
'make sure cell is not at end of sheet
If ActiveCell.Row = 65535 Then Exit Sub
ActiveCell.Value = x
ActiveCell.Offset(1, 0).Activate
Next x
End Sub


Posted by: Jethro | 2:47 PM |