Error message when adding 2 columns with Macro
I have the following macro which I want to place in column V7 which adds the
totals in E7 and D7 divides by 8 and then multiplies by 52. I am able to execute with just E but when I add in D I get error messages either compile error or expecting end of statement. I will also need to do a macro which adds 3 columns together so I would like to know if what holds true for 2 columns also holds true for 3 as far as syntax. Sub CopyCell2() Dim X As Long Dim Sh As Variant For Each Sh In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8") For X = 7 To 501 Worksheets(Sh).Range("V" & CStr(X)).Formula = _ "=SUM((E" & CStr(X) & "+" (D" & CStr(X)& "/" & Sh & "!$A$505)*52" Next Next End Sub -- Thanks. Bob |
Error message when adding 2 columns with Macro
Try this approach
Sub stantial() Dim X As Long Dim Sh As Variant For Each Sh In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8") For X = 7 To 501 Worksheets(Sh).Range("V" & X).FormulaR1C1 = "=((RC[-17]+RC[-18])/8)*52" Next Next End Sub Mike "Bob" wrote: I have the following macro which I want to place in column V7 which adds the totals in E7 and D7 divides by 8 and then multiplies by 52. I am able to execute with just E but when I add in D I get error messages either compile error or expecting end of statement. I will also need to do a macro which adds 3 columns together so I would like to know if what holds true for 2 columns also holds true for 3 as far as syntax. Sub CopyCell2() Dim X As Long Dim Sh As Variant For Each Sh In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8") For X = 7 To 501 Worksheets(Sh).Range("V" & CStr(X)).Formula = _ "=SUM((E" & CStr(X) & "+" (D" & CStr(X)& "/" & Sh & "!$A$505)*52" Next Next End Sub -- Thanks. Bob |
Error message when adding 2 columns with Macro
Thanks Mike - thats exactly what I needed.
-- Bob "Mike H" wrote: Try this approach Sub stantial() Dim X As Long Dim Sh As Variant For Each Sh In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8") For X = 7 To 501 Worksheets(Sh).Range("V" & X).FormulaR1C1 = "=((RC[-17]+RC[-18])/8)*52" Next Next End Sub Mike "Bob" wrote: I have the following macro which I want to place in column V7 which adds the totals in E7 and D7 divides by 8 and then multiplies by 52. I am able to execute with just E but when I add in D I get error messages either compile error or expecting end of statement. I will also need to do a macro which adds 3 columns together so I would like to know if what holds true for 2 columns also holds true for 3 as far as syntax. Sub CopyCell2() Dim X As Long Dim Sh As Variant For Each Sh In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8") For X = 7 To 501 Worksheets(Sh).Range("V" & CStr(X)).Formula = _ "=SUM((E" & CStr(X) & "+" (D" & CStr(X)& "/" & Sh & "!$A$505)*52" Next Next End Sub -- Thanks. Bob |
Error message when adding 2 columns with Macro
your welcome
"Bob" wrote: Thanks Mike - thats exactly what I needed. -- Bob "Mike H" wrote: Try this approach Sub stantial() Dim X As Long Dim Sh As Variant For Each Sh In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8") For X = 7 To 501 Worksheets(Sh).Range("V" & X).FormulaR1C1 = "=((RC[-17]+RC[-18])/8)*52" Next Next End Sub Mike "Bob" wrote: I have the following macro which I want to place in column V7 which adds the totals in E7 and D7 divides by 8 and then multiplies by 52. I am able to execute with just E but when I add in D I get error messages either compile error or expecting end of statement. I will also need to do a macro which adds 3 columns together so I would like to know if what holds true for 2 columns also holds true for 3 as far as syntax. Sub CopyCell2() Dim X As Long Dim Sh As Variant For Each Sh In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8") For X = 7 To 501 Worksheets(Sh).Range("V" & CStr(X)).Formula = _ "=SUM((E" & CStr(X) & "+" (D" & CStr(X)& "/" & Sh & "!$A$505)*52" Next Next End Sub -- Thanks. Bob |
All times are GMT +1. The time now is 08:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com