Use Visual Basic to fill a range in a column with numbers

[jdmaybee]

As an old Lotus user much of my thinking is based on the way it worked. I have used Excel for some time now and know about the little black square I can use to drag a progression of numbers down to whatever I like, but how do I write the proceedure in a macro?

 

[Hui]

Select your input range

Autofill

[pre]

Range("E3:E6").Select
Selection.AutoFill Destination:=Range("E3:E15"), Type:=xlFillDefault

[/pre]

You can choose different autofill types from:


xlFillCopy Copy the values and formats from the source range to the target range, repeating if necessary.


xlFillDays Extend the names of the days of the week in the source range into the target range. Formats are copied from the source range to the target range, repeating if necessary.


xlFillDefault Excel determines the values and formats used to fill the target range.


xlFillFormats Copy only the formats from the source range to the target range, repeating if necessary.


xlFillMonths Extend the names of the months in the source range into the target range. Formats are copied from the source range to the target range, repeating if necessary.


xlFillSeries Extend the values in the source range into the target range as a series (for example, '1, 2' is extended as '3, 4, 5'). Formats are copied from the source range to the target range, repeating if necessary.


xlFillValues Copy only the values from the source range to the target range, repeating if necessary.


xlFillWeekdays Extend the names of the days of the workweek in the source range into the target range. Formats are copied from the source range to the target range, repeating if necessary.


xlFillYears Extend the years in the source range into the target range. Formats are copied from the source range to the target range, repeating if necessary.


xlGrowthTrend Extend the numeric values from the source range into the target range, assuming that the relationships between the numbers in the source range are multiplicative (for example, '1, 2,' is extended as '4, 8, 16', assuming that each number is a result of multiplying the previous number by some value). Formats are copied from the source range to the target range, repeating if necessary.


xlLinearTrend Extend the numeric values from the source range into the target range, assuming that the relationships between the numbers is additive (for example, '1, 2,' is extended as '3, 4, 5', assuming that each number is a result of adding some value to the previous number). Formats are copied from the source range to the target range, repeating if necessary.

 

[jdmaybee]

Thank you so much! I joined your forum from home so can't get you at work so I spent the whole day trying to understand how this works and in five minutes with your help I'm on my way!! Can I have two user accounts for the forum?

 

[jdmaybee]

I decided to see how the other autofill techniques worked and ran the following:


Sub FormatFill()

'

' FormatFill Macro

' Macro recorded 6/29/2011 by John D. Maybee

'

Range("E1").Select

Selection.AutoFill Destination:=Range("E5:E15"), Type:=xlFillFormats

End Sub


The first time it worked and then I tried a destination range of "E5:F16" and it failed giving me a run time error '1004'


Autofill method of Range class failed.


Even after returning to "E5:E15" I can't get it to work again.

Why would that be?

 

[Hui]

I would change it to:

[pre]

Sub FormatFill()
Range("E5").Select ' make sure its at start of your data range
Selection.AutoFill Destination:=Range("E5:E15"), Type:=xlFillFormats
End Sub

[/pre]

So if you have values from E5:E9, make sure that the range of cells which are past the small Black Square are blank eg: E10:E15

 

[jdmaybee]

Thank you I get it now.