Problem with Macro Calculation

[Bob]

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

 

[Joel]

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]

Thanks Joel. That works perfectly. Just for my future reference does the "4"
in R22C4 indicate column "D"?


--
Bob

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

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]

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

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

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]

Those last two formulas should have been Joel's version. (I copied pasted from
the wrong line.)

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

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

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