Copy multiple formulas to used range

[Patti]

Is there a more efficient way to copy multiple formulas to the used range of
a worksheet? I may have as many as 15 formulas in row 2, and they may not
be in contiguous columns.

This is what I'm doing now.

Thanks,
Patti

Sub copyFormulas()

Dim lstRow As Long
Dim sourceRange As Range
Dim fillrange As Range

Range("e2").Formula = "=VALUE(D2)"
Range("g2").Formula = "=IF(ISBLANK(F2),""Check"",VALUE(F2))"
Range("h2").Formula = "=IF(A3=A2,IF(E3<G2,(E3+1)-G2,E3-G2),"" "")"

lstRow = Cells(Rows.Count, "A").End(xlUp).Row

Set sourceRange = ActiveSheet.Range("e2")
Set fillrange = ActiveSheet.Range("e2:e" & lstRow)
sourceRange.AutoFill Destination:=fillrange

Set sourceRange = ActiveSheet.Range("g2")
Set fillrange = ActiveSheet.Range("g2:g" & lstRow)
sourceRange.AutoFill Destination:=fillrange

Set sourceRange = ActiveSheet.Range("h2")
Set fillrange = ActiveSheet.Range("h2:h" & lstRow)
sourceRange.AutoFill Destination:=fillrange

End Sub

 

[Dave Peterson]

I'd just fill the range:

Sub copyFormulas()

Dim lstRow As Long
lstRow = Cells(Rows.Count, "A").End(xlUp).Row

ActiveSheet.Range("e2:e" & lstRow).formula _
= "=VALUE(D2)"

ActiveSheet.Range("g2:g" & lstRow).Formula _
= "=IF(ISBLANK(F2),""Check"",VALUE(F2))"

ActiveSheet.Range("h2:h" & lstRow).Formula _
= "=IF(A3=A2,IF(E3<G2,(E3+1)-G2,E3-G2),"" "")"

End Sub

 

[Patti]

That is much better!

The method that I used was something I've seen suggested in the NG (though
not necessarily for multiple formulas). Your way is so much cleaner, it
leaves me wondering if there is ever a reason that one would want to use the
AutoFill at all.

Anyway, thanks for the help Dave.

Patti

 

[Dave Peterson]

Maybe to copy formats, too???

That is much better!

The method that I used was something I've seen suggested in the NG (though
not necessarily for multiple formulas). Your way is so much cleaner, it
leaves me wondering if there is ever a reason that one would want to use the
AutoFill at all.

Anyway, thanks for the help Dave.

Patti