Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Macro
You could also just assign the same formula to each cell in the column:
Sub CopyFormula() Dim lrow As Long with worksheets("Price") lrow = .Cells(.Rows.Count, "F").End(xlUp).Row .range("F13:F" & lrow).Formula = "=IF(OR(D13="""",D13=0),"""",G13/D13)" .range("H13:H" & lrow).Formula = "=IF(D13="""","""",$I$5)" 'and so on... end with End Sub And there's no need to select or activate any worksheets in your posted code. Michael M wrote: Hi All I have written a macro for copying formulae down a number of columns and it works perfectly. However, now that all is working fine, I was curious as to whether it could be written differently / more efficiently. I know that "if it ain't broke, don't fix it" applies here, but it is more for my ongoing VBA knowledge than anything else. Any comments would be greatly appreciated. Regards Michael M Sub CopyFormula() Dim lrow As Long Worksheets("Price").Activate lrow = Worksheets("Price").Cells(Rows.Count, "F").End(xlUp).Row range("F13").Formula = "=IF(OR(D13="""",D13=0),"""",G13/D13)" range("F13").Copy range("F14:F" & lrow) range("H13").Formula = "=IF(D13="""","""",$I$5)" range("H13").Copy range("H14:H" & lrow) range("I13").Formula = "=IF(H13="""","""",G13*H13)" range("I13").Copy range("I14:I" & lrow) range("J13").Formula = "=G13+I13" range("J13").Copy range("J14:J" & lrow) range("K13").Copy range("K14:K" & lrow) range("L13").Formula = "=IF(H13="""","""",IF(K13=""L"",0,IF(K13=""S"",""" ",J13*$L$301)))" range("L13").Copy range("L14:L" & lrow) range("M13").Formula = "=IF(L13="""","""",L13+J13)" range("M13").Copy range("M14:M" & lrow) range("N13").Formula = "=IF(D13="""","""",D13)" range("N13").Copy range("N14:N" & lrow) range("O13").Formula = "=IF(M13<0,M13/N13,IF(N13="""","""",MROUND((M13/N13),0.01)))" range("O13").Copy range("O14:O" & lrow) range("P13").Formula = "=IF(O13="""","""",O13*N13)" range("P13").Copy range("P14:P" & lrow) range("Q13").Formula = "=P13-M13" range("Q13").Copy range("Q14:Q" & lrow) Worksheets("MAIN MENU").Activate End Sub -- Dave Peterson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Macro
Thanks Dave
The more examples I see, the more obvious it becomes that my code was a bit "basic" and I have to stop using the Activate. It's just a habit I got into when I started Thanks to all and regards Michael M "Dave Peterson" wrote: You could also just assign the same formula to each cell in the column: Sub CopyFormula() Dim lrow As Long with worksheets("Price") lrow = .Cells(.Rows.Count, "F").End(xlUp).Row .range("F13:F" & lrow).Formula = "=IF(OR(D13="""",D13=0),"""",G13/D13)" .range("H13:H" & lrow).Formula = "=IF(D13="""","""",$I$5)" 'and so on... end with End Sub And there's no need to select or activate any worksheets in your posted code. Michael M wrote: Hi All I have written a macro for copying formulae down a number of columns and it works perfectly. However, now that all is working fine, I was curious as to whether it could be written differently / more efficiently. I know that "if it ain't broke, don't fix it" applies here, but it is more for my ongoing VBA knowledge than anything else. Any comments would be greatly appreciated. Regards Michael M Sub CopyFormula() Dim lrow As Long Worksheets("Price").Activate lrow = Worksheets("Price").Cells(Rows.Count, "F").End(xlUp).Row range("F13").Formula = "=IF(OR(D13="""",D13=0),"""",G13/D13)" range("F13").Copy range("F14:F" & lrow) range("H13").Formula = "=IF(D13="""","""",$I$5)" range("H13").Copy range("H14:H" & lrow) range("I13").Formula = "=IF(H13="""","""",G13*H13)" range("I13").Copy range("I14:I" & lrow) range("J13").Formula = "=G13+I13" range("J13").Copy range("J14:J" & lrow) range("K13").Copy range("K14:K" & lrow) range("L13").Formula = "=IF(H13="""","""",IF(K13=""L"",0,IF(K13=""S"",""" ",J13*$L$301)))" range("L13").Copy range("L14:L" & lrow) range("M13").Formula = "=IF(L13="""","""",L13+J13)" range("M13").Copy range("M14:M" & lrow) range("N13").Formula = "=IF(D13="""","""",D13)" range("N13").Copy range("N14:N" & lrow) range("O13").Formula = "=IF(M13<0,M13/N13,IF(N13="""","""",MROUND((M13/N13),0.01)))" range("O13").Copy range("O14:O" & lrow) range("P13").Formula = "=IF(O13="""","""",O13*N13)" range("P13").Copy range("P14:P" & lrow) range("Q13").Formula = "=P13-M13" range("Q13").Copy range("Q14:Q" & lrow) Worksheets("MAIN MENU").Activate End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a formula/Macro | Excel Discussion (Misc queries) | |||
Formula/macro help | Excel Programming | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Macro Formula | Excel Discussion (Misc queries) | |||
Help on Macro or Formula | Excel Discussion (Misc queries) |