ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Formula Macro (https://www.excelbanter.com/excel-programming/379745-re-formula-macro.html)

Gary Keramidas
Formula Macro
 
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" 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



Gary Keramidas
Formula Macro
 
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
...
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" 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

Michael M
Formula Macro
 
Thanks Gary
I will have a play with yours as well.
At least I wasn't too far off the mark.

Regards
Michael M.

"Gary Keramidas" wrote:

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" 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




Michael M
Formula Macro
 
Thanks Gary
I was aware of that !!
Regards
Michael M

"Gary Keramidas" wrote:

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
...
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" 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


All times are GMT +1. The time now is 06:02 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com