Michael M
12/20/2006 1:46:00 AM
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" <MichaelM@discussions.microsoft.com> wrote in message
> news:F54537FE-998D-4AB0-A122-585EE0E93145@microsoft.com...
> > 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
>
>
>