Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Macro Calculation
I've got the following macro and instead of dividing by 8 I would like to
divide by Sheet1!$D$22 and then multiply by 52. However when I put that in place of the 8 I get a run time error. What am I missing? -- For Each Sh In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8") For X = 7 To 500 Worksheets(Sh).Range("W" & X).FormulaR1C1 = "=((RC[-19]+RC[-18])/8*52" Next Next End Sub Thanks. Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Macro Calculation
You were missing a parethesis at the end. You also need R1C1 format for all
references to other cells. Worksheets(Sh).Range("W:X").FormulaR1C1 = _ "=((RC[-19]+RC[-18])/Sheet1!R22C4*52)" "Bob" wrote: I've got the following macro and instead of dividing by 8 I would like to divide by Sheet1!$D$22 and then multiply by 52. However when I put that in place of the 8 I get a run time error. What am I missing? -- For Each Sh In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8") For X = 7 To 500 Worksheets(Sh).Range("W" & X).FormulaR1C1 = "=((RC[-19]+RC[-18])/8*52" Next Next End Sub Thanks. Bob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Macro Calculation
Thanks Joel. That works perfectly. Just for my future reference does the "4"
in R22C4 indicate column "D"? -- Bob "Joel" wrote: You were missing a parethesis at the end. You also need R1C1 format for all references to other cells. Worksheets(Sh).Range("W:X").FormulaR1C1 = _ "=((RC[-19]+RC[-18])/Sheet1!R22C4*52)" "Bob" wrote: I've got the following macro and instead of dividing by 8 I would like to divide by Sheet1!$D$22 and then multiply by 52. However when I put that in place of the 8 I get a run time error. What am I missing? -- For Each Sh In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8") For X = 7 To 500 Worksheets(Sh).Range("W" & X).FormulaR1C1 = "=((RC[-19]+RC[-18])/8*52" Next Next End Sub Thanks. Bob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Macro Calculation
I think that "W" & X was really an address of a single cell in column W.
W7, W8, ..., W500 I don't think that the OP wanted to fill all of W:X with that formula 494 times. Worksheets(Sh).Range("W" & X).FormulaR1C1 = _ "=((RC[-19]+RC[-18])/Sheet1!R22C4*52)" To the OP: You could also fill the entire range and drop the loop: For X = 7 To 500 Worksheets(Sh).Range("W" & X).FormulaR1C1 = "=((RC[-19]+RC[-18])/8*52)" Next X Becomes: worksheets(Sh).range("W7").resize(494, 1).formulaR1C1 _ = "=((RC[-19]+RC[-18])/8*52)" Joel wrote: You were missing a parethesis at the end. You also need R1C1 format for all references to other cells. Worksheets(Sh).Range("W:X").FormulaR1C1 = _ "=((RC[-19]+RC[-18])/Sheet1!R22C4*52)" "Bob" wrote: I've got the following macro and instead of dividing by 8 I would like to divide by Sheet1!$D$22 and then multiply by 52. However when I put that in place of the 8 I get a run time error. What am I missing? -- For Each Sh In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8") For X = 7 To 500 Worksheets(Sh).Range("W" & X).FormulaR1C1 = "=((RC[-19]+RC[-18])/8*52" Next Next End Sub Thanks. Bob -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Macro Calculation
Those last two formulas should have been Joel's version. (I copied pasted from
the wrong line.) Dave Peterson wrote: I think that "W" & X was really an address of a single cell in column W. W7, W8, ..., W500 I don't think that the OP wanted to fill all of W:X with that formula 494 times. Worksheets(Sh).Range("W" & X).FormulaR1C1 = _ "=((RC[-19]+RC[-18])/Sheet1!R22C4*52)" To the OP: You could also fill the entire range and drop the loop: For X = 7 To 500 Worksheets(Sh).Range("W" & X).FormulaR1C1 = "=((RC[-19]+RC[-18])/8*52)" Next X Becomes: worksheets(Sh).range("W7").resize(494, 1).formulaR1C1 _ = "=((RC[-19]+RC[-18])/8*52)" Joel wrote: You were missing a parethesis at the end. You also need R1C1 format for all references to other cells. Worksheets(Sh).Range("W:X").FormulaR1C1 = _ "=((RC[-19]+RC[-18])/Sheet1!R22C4*52)" "Bob" wrote: I've got the following macro and instead of dividing by 8 I would like to divide by Sheet1!$D$22 and then multiply by 52. However when I put that in place of the 8 I get a run time error. What am I missing? -- For Each Sh In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8") For X = 7 To 500 Worksheets(Sh).Range("W" & X).FormulaR1C1 = "=((RC[-19]+RC[-18])/8*52" Next Next End Sub Thanks. Bob -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation Problem | Excel Discussion (Misc queries) | |||
Calculation problem? | Excel Discussion (Misc queries) | |||
Calculation problem | Excel Programming | |||
Calculation Problem | Excel Discussion (Misc queries) | |||
Macro Ceiling & Floor Calculation Problem... | Excel Programming |