Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Cell in Macro
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 -- Thanks. Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Cell in Macro
Myoff = -17
Worksheets(Sh).Range("V" & X).FormulaR1C1 = _ "=((RC[" & Myoff & "]/Sheet1!R20C4)*52)" "Bob" wrote: 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 -- Thanks. Bob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Cell in Macro
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 "Bob" wrote: 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 -- Thanks. Bob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Cell in Macro
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 "Ken Hudson" wrote: 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 "Bob" wrote: 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 -- Thanks. Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing a cell in a macro | Excel Programming | |||
Sheet referencing in a macro | Excel Programming | |||
Sheet referencing in a macro | Excel Programming | |||
Indirect Referencing in a Macro | Excel Programming | |||
File referencing in macro | Excel Programming |