Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grouping columns - error message | Excel Worksheet Functions | |||
Why do I get an error message hiding excel columns | Excel Worksheet Functions | |||
Error message when adding a name | Charts and Charting in Excel | |||
hiding columns in Excell error message | Excel Discussion (Misc queries) | |||
Adding an error message at close of file when criteria are met | Excel Discussion (Misc queries) |