Referencing a Cell in Macro

[Bob]

I've got the following macro code and what I would like to do is change the
value of [-17] on a a separate sheet -such as Sheet1. For example, this part
of the code in a monthly total so [-17] refers to Feb-08 and in March I would
need to update the macro with [-16] for Mar-08. I would rather update an
excel sheet with this reference that to have to change the macro directly. Is
this possible?

Sub CopyCell1()
Dim X As Long
Dim Sh As Variant
For Each Sh In Array("M", "P", "T", "A", "Mi", "Sm", "H")
For X = 7 To 500
Worksheets(Sh).Range("V" & X).FormulaR1C1 =
"=((RC[-17]/Sheet1!R20C4)*52)"
Next
Next
End Sub

 

[Joel]

Myoff = -17
Worksheets(Sh).Range("V" & X).FormulaR1C1 = _
"=((RC[" & Myoff & "]/Sheet1!R20C4)*52)"

 

[Ken Hudson]

Hi Bob,

Put -17 in cell A1 on Sheet1.

Worksheets(Sh).Range("V" & X) = "=((RC[" & Sheets(1).Range("A1") &
"]/Sheet1!R20C4)*52)"

Hope this helps.

 

[Bob]

Thanks Ken. That works perfectly. If I wanted to add to columns for example
A1 + A2 would I use your code and simply add an + sign?
--
Bob

Hi Bob,

Put -17 in cell A1 on Sheet1.

Worksheets(Sh).Range("V" & X) = "=((RC[" & Sheets(1).Range("A1") &
"]/Sheet1!R20C4)*52)"

Hope this helps.
--
Ken Hudson

I've got the following macro code and what I would like to do is change the
value of [-17] on a a separate sheet -such as Sheet1. For example, this part
of the code in a monthly total so [-17] refers to Feb-08 and in March I would
need to update the macro with [-16] for Mar-08. I would rather update an
excel sheet with this reference that to have to change the macro directly. Is
this possible?

Sub CopyCell1()
Dim X As Long
Dim Sh As Variant
For Each Sh In Array("M", "P", "T", "A", "Mi", "Sm", "H")
For X = 7 To 500
Worksheets(Sh).Range("V" & X).FormulaR1C1 =
"=((RC[-17]/Sheet1!R20C4)*52)"
Next
Next
End Sub