Copy multiple formulas to used range
 

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

Copy multiple formulas to used range
 

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

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

Copy multiple formulas to used range
 

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" wrote in message
...
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 wrote:

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

Copy multiple formulas to used range
 

Maybe to copy formats, too???

Patti wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson