Gary Keramidas
12/20/2006 2:16:00 AM
tried to split the lines so you wouldn't have a word-wrap issue
Sub CopyFormula()
Dim lrow As Long
Dim ws As Worksheet
Set ws = Worksheets("price")
With ws
..Activate
lrow = .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("I13").Formula = "=IF(H13="""","""",G13*H13)"
.Range("J13").Formula = "=G13+I13"
'.Range("K13").Copy Range("K14:K" & lrow)
.Range("L13").Formula = _
"=IF(H13="""","""",IF(K13=""L"",0,IF(K13=""S"","""",J13*$L$301)))"
.Range("M13").Formula = "=IF(L13="""","""",L13+J13)"
.Range("N13").Formula = "=IF(D13="""","""",D13)"
.Range("O13").Formula = _
"=IF(M13<0,M13/N13,IF(N13="""","""",MROUND((M13/N13),0.01)))"
.Range("P13").Formula = "=IF(O13="""","""",O13*N13)"
.Range("Q13").Formula = "=P13-M13"
.Range("F13:Q13").AutoFill .Range("f13:Q" & lrow)
End With
Worksheets("MAIN MENU").Activate
End Sub
--
Gary
"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:%23oYoMv9IHHA.2236@TK2MSFTNGP02.phx.gbl...
> untested, but maybe something like this. i also didn't see a formula for
> column K, only a copy statement
>
> Sub CopyFormula()
> Dim lrow As Long
> Dim ws As Worksheet
> Set ws = Worksheets("price")
> With ws
> .Activate
> lrow = .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("I13").Formula = "=IF(H13="""","""",G13*H13)"
> .Range("J13").Formula = "=G13+I13"
> '.Range("K13").Copy Range("K14:K" & lrow)
> .Range("L13").Formula =
> "=IF(H13="""","""",IF(K13=""L"",0,IF(K13=""S"","""",J13*$L$301)))"
> .Range("M13").Formula = "=IF(L13="""","""",L13+J13)"
> .Range("N13").Formula = "=IF(D13="""","""",D13)"
> .Range("O13").Formula =
> "=IF(M13<0,M13/N13,IF(N13="""","""",MROUND((M13/N13),0.01)))"
> .Range("P13").Formula = "=IF(O13="""","""",O13*N13)"
> .Range("Q13").Formula = "=P13-M13"
> .Range("F13:Q13").AutoFill .Range("f13:Q" & lrow)
> End With
> Worksheets("MAIN MENU").Activate
> End Sub
>
> --
>
>
> Gary
>
>
> "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
>
>