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

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