Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy range without changing formulas? | Excel Discussion (Misc queries) | |||
How copy a range o cells without adjusting formulas? | Excel Discussion (Misc queries) | |||
How do I copy formulas but using the same range each time I copy | Excel Worksheet Functions | |||
Excel copy formulas using non contiguous range | Excel Discussion (Misc queries) | |||
Range COPY function - how to copy VALUES and not formulas | Excel Programming |