Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Macro
untested, but maybe something like this. i also didn't see a formula for column
K, only a copy statement Sub CopyFormula() Dim lrow As Long Dim ws As Worksheet Set ws = Worksheets("price") With ws ..Activate lrow = .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("I13").Formula = "=IF(H13="""","""",G13*H13)" .Range("J13").Formula = "=G13+I13" '.Range("K13").Copy Range("K14:K" & lrow) .Range("L13").Formula = "=IF(H13="""","""",IF(K13=""L"",0,IF(K13=""S"",""" ",J13*$L$301)))" .Range("M13").Formula = "=IF(L13="""","""",L13+J13)" .Range("N13").Formula = "=IF(D13="""","""",D13)" .Range("O13").Formula = "=IF(M13<0,M13/N13,IF(N13="""","""",MROUND((M13/N13),0.01)))" .Range("P13").Formula = "=IF(O13="""","""",O13*N13)" .Range("Q13").Formula = "=P13-M13" .Range("F13:Q13").AutoFill .Range("f13:Q" & lrow) End With Worksheets("MAIN MENU").Activate End Sub -- Gary "Michael M" wrote in message ... 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Macro
tried to split the lines so you wouldn't have a word-wrap issue
Sub CopyFormula() Dim lrow As Long Dim ws As Worksheet Set ws = Worksheets("price") With ws ..Activate lrow = .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("I13").Formula = "=IF(H13="""","""",G13*H13)" .Range("J13").Formula = "=G13+I13" '.Range("K13").Copy Range("K14:K" & lrow) .Range("L13").Formula = _ "=IF(H13="""","""",IF(K13=""L"",0,IF(K13=""S"",""" ",J13*$L$301)))" .Range("M13").Formula = "=IF(L13="""","""",L13+J13)" .Range("N13").Formula = "=IF(D13="""","""",D13)" .Range("O13").Formula = _ "=IF(M13<0,M13/N13,IF(N13="""","""",MROUND((M13/N13),0.01)))" .Range("P13").Formula = "=IF(O13="""","""",O13*N13)" .Range("Q13").Formula = "=P13-M13" .Range("F13:Q13").AutoFill .Range("f13:Q" & lrow) End With Worksheets("MAIN MENU").Activate End Sub -- Gary "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... untested, but maybe something like this. i also didn't see a formula for column K, only a copy statement Sub CopyFormula() Dim lrow As Long Dim ws As Worksheet Set ws = Worksheets("price") With ws .Activate lrow = .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("I13").Formula = "=IF(H13="""","""",G13*H13)" .Range("J13").Formula = "=G13+I13" '.Range("K13").Copy Range("K14:K" & lrow) .Range("L13").Formula = "=IF(H13="""","""",IF(K13=""L"",0,IF(K13=""S"",""" ",J13*$L$301)))" .Range("M13").Formula = "=IF(L13="""","""",L13+J13)" .Range("N13").Formula = "=IF(D13="""","""",D13)" .Range("O13").Formula = "=IF(M13<0,M13/N13,IF(N13="""","""",MROUND((M13/N13),0.01)))" .Range("P13").Formula = "=IF(O13="""","""",O13*N13)" .Range("Q13").Formula = "=P13-M13" .Range("F13:Q13").AutoFill .Range("f13:Q" & lrow) End With Worksheets("MAIN MENU").Activate End Sub -- Gary "Michael M" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Macro
Thanks Gary
I will have a play with yours as well. At least I wasn't too far off the mark. Regards Michael M. "Gary Keramidas" wrote: untested, but maybe something like this. i also didn't see a formula for column K, only a copy statement Sub CopyFormula() Dim lrow As Long Dim ws As Worksheet Set ws = Worksheets("price") With ws ..Activate lrow = .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("I13").Formula = "=IF(H13="""","""",G13*H13)" .Range("J13").Formula = "=G13+I13" '.Range("K13").Copy Range("K14:K" & lrow) .Range("L13").Formula = "=IF(H13="""","""",IF(K13=""L"",0,IF(K13=""S"",""" ",J13*$L$301)))" .Range("M13").Formula = "=IF(L13="""","""",L13+J13)" .Range("N13").Formula = "=IF(D13="""","""",D13)" .Range("O13").Formula = "=IF(M13<0,M13/N13,IF(N13="""","""",MROUND((M13/N13),0.01)))" .Range("P13").Formula = "=IF(O13="""","""",O13*N13)" .Range("Q13").Formula = "=P13-M13" .Range("F13:Q13").AutoFill .Range("f13:Q" & lrow) End With Worksheets("MAIN MENU").Activate End Sub -- Gary "Michael M" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Macro
Thanks Gary
I was aware of that !! Regards Michael M "Gary Keramidas" wrote: tried to split the lines so you wouldn't have a word-wrap issue Sub CopyFormula() Dim lrow As Long Dim ws As Worksheet Set ws = Worksheets("price") With ws ..Activate lrow = .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("I13").Formula = "=IF(H13="""","""",G13*H13)" .Range("J13").Formula = "=G13+I13" '.Range("K13").Copy Range("K14:K" & lrow) .Range("L13").Formula = _ "=IF(H13="""","""",IF(K13=""L"",0,IF(K13=""S"",""" ",J13*$L$301)))" .Range("M13").Formula = "=IF(L13="""","""",L13+J13)" .Range("N13").Formula = "=IF(D13="""","""",D13)" .Range("O13").Formula = _ "=IF(M13<0,M13/N13,IF(N13="""","""",MROUND((M13/N13),0.01)))" .Range("P13").Formula = "=IF(O13="""","""",O13*N13)" .Range("Q13").Formula = "=P13-M13" .Range("F13:Q13").AutoFill .Range("f13:Q" & lrow) End With Worksheets("MAIN MENU").Activate End Sub -- Gary "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... untested, but maybe something like this. i also didn't see a formula for column K, only a copy statement Sub CopyFormula() Dim lrow As Long Dim ws As Worksheet Set ws = Worksheets("price") With ws .Activate lrow = .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("I13").Formula = "=IF(H13="""","""",G13*H13)" .Range("J13").Formula = "=G13+I13" '.Range("K13").Copy Range("K14:K" & lrow) .Range("L13").Formula = "=IF(H13="""","""",IF(K13=""L"",0,IF(K13=""S"",""" ",J13*$L$301)))" .Range("M13").Formula = "=IF(L13="""","""",L13+J13)" .Range("N13").Formula = "=IF(D13="""","""",D13)" .Range("O13").Formula = "=IF(M13<0,M13/N13,IF(N13="""","""",MROUND((M13/N13),0.01)))" .Range("P13").Formula = "=IF(O13="""","""",O13*N13)" .Range("Q13").Formula = "=P13-M13" .Range("F13:Q13").AutoFill .Range("f13:Q" & lrow) End With Worksheets("MAIN MENU").Activate End Sub -- Gary "Michael M" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add a formula using a macro | Excel Worksheet Functions | |||
Formula in Macro | Excel Discussion (Misc queries) | |||
Formula Macro | Excel Discussion (Misc queries) | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Do I need a formula or Macro? | Excel Worksheet Functions |