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

Thanks Roger
I went down that path, but thought creating the formula first, might open
other worm cans.

Regards
Michael M

"Roger Govier" wrote:

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

Roger
Sorry, I misunderstood your comment.
I thought you were suggesting putting ther formulae in the spreadsheet
first, rather than using code.
I will try your line of code, it makes simple sense really !!
Regards
Michael M

"Roger Govier" wrote:

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