Cell Value to become part of RC in macro

[Wall31]

Hi guys! I have a slight conundrum. I have a cell that changes dynamically. This number is needed within a macro's "RC" reference.

If the cell is 3, the reference in the macro should become RC3.

Any insight will be appreciated!

[DocAElstein]

Hi
Here is a simple example: in cell P24 I have the formula
= O24
In RC stuff that would be like in a macro to put it in

Sub Macro4()
Let Range("P24").FormulaR1C1 = "=RC[-1]"
End Sub
Lets say in N24 I had -1
So then this macro would do the same as the last one

Sub Macro4()
Let Range("P24").FormulaR1C1 = "=RC[" & Range("N24").Value & "]"
End Sub

_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
N
O
P

24
-1
0
Worksheet: macro_xlsmSh1efJ20

_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
N
O
P

24
-1
=O24
Worksheet: macro_xlsmSh1efJ20

_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
N
O
P

24
-1
=RC[-1]
Worksheet: macro_xlsmSh1efJ20

To do it a bit better, you should always qualify the worksheet, for example in that last case I had a worksheet of macro_xlsmSh1efJ20 and File of macro.xlsm

Sub Macro4()
Let Workbooks("macro.xlsm").Worksheets("macro_xlsmSh1efJ20").Range("P24").FormulaR1C1 = "=RC[" & Workbooks("macro.xlsm").Worksheets("macro_xlsmSh1efJ20").Range("N24").Value & "]"
End Sub

Or to keep it a bit neater, other ways

Sub Macro4()
With Workbooks("macro.xlsm").Worksheets("macro_xlsmSh1efJ20")
Let .Range("P24").FormulaR1C1 = "=RC[" & .Range("N24").Value & "]"
End With
End Sub


Sub Macro4()
Rem Worksheets info
Dim Ws As Worksheet, Wb As Workbook
Set Wb = Workbooks("macro.xlsm")
Set Ws = Wb.Worksheets("macro_xlsmSh1efJ20")
Rem Do it
Let Ws.Range("P24").FormulaR1C1 = "=RC[" & Ws.Range("N24").Value & "]"
End Sub

The last way is probably the best one to get in the habit of using



Just to try to make it clear.
in your coding, you would usually## need to replace the number with like
" & Ws.Range("N24").Value & "
So in your case you would replace
3
with something like
" & Ws.Range("B1").Value & "
assuming in this examplke that your number 3 was in cell B1

##It might sometimes be a bit more complicated then that, depending on exactly what your code line containing the RC3 looks like


Alan

[Wall31]

Doc you are an absolute legend!!!

I used your first suggestion!


Sub Macro4()
Let Range("P24").FormulaR1C1 = "=RC[-1]"
End Sub

Worked right off the bat. Thanks so much!