Michael M
12/20/2006 2:56:00 AM
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
>