Formula Macro
Hi Michael
You could create your formulae, then have a single copy statement at the
end to copy down
Range("F13:Q13").Copy range("F14:F" & lrow)
--
Regards
Roger Govier
"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
|