Filling down a formula

[Aussiebear]

I guess there's many ways to skin a cat as evidenced by the three following examples, but which way is considered better and why?

Example 1 Using Defined fomula then Autofill

Sub Fill_Down()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
With Sheet1
.Range("B2").Formula = "=H1-G1"
.Range("B2").AutoFill .Range("B2:B" & lastrow)
End With
Application.ScreenUpdating = True
End Sub


Example 2 Defines a formula in the range

Sub Fill_Down()
Dim lngLastRow As Long
lnglastrow = cells(rows.count, "A").end(xlup).row
Application.ScreenUpdating = False
Range("B4:B" & lnglastrow).formula = "=H1-G1"
Application.ScreenUpdating = True
End Sub


Example 3 I'm guessing assumes the formula is already in cell B2?

Sub Fill_Down()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
With ActiveSheet
.Range("B2:B").AutoFill Destination:=.Range("B2:B" & lastrow)
End With Application.ScreenUpdating = True
End Sub

[mdmackillop]

Hi Ted,
I wouldn't use #1. No advantage in 2 steps.
#2 or #3 would depend upon the existence of the formula before running the macro, e.g. it may exist as part of a template, so use #3, otherwise #2

[Aussiebear]

So in using #3, I will need to provide a hidden row containing the formula if we are to build a dynamic table/range, else use #2 where the table/range is being built on the fly?

And in the case where multiple columns are involved, do I simply need to loop across the row containing the formulas?

[GTO]

Hi both ya'll :-)

I'm sure I do this less than either of you, but for what it's worth, I like #2 the best. No worries about formatting (borders ans such).

Not sure about that last bit Ted, but you can plunk the formulas in at once, if that was the question?

Sub example()
Range("C2:D10").Formula = Array("=A2-B2", "=C2*1.5")
End Sub

[Aflatoon]

If you have calculation set to manual, the Fill versions will not calculate (until you tell them to), but #2 will. Whether that is useful or no, will depend on the circumstance.

[Aussiebear]

Good point Aflatoon