Error message when adding 2 columns with Macro

[Bob]

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

 

[Mike H]

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]

Thanks Mike - thats exactly what I needed.
--
Bob

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

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

 

[Mike H]

your welcome

Thanks Mike - thats exactly what I needed.
--
Bob

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

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