How to I make an absolute reference using r1c1 format in VBA for Excel?

Question

I am writing a macro for Excel using VBA (r1c1 format) and I want to reference a cell that is always in position E3. Is there a syntax I can use that will make an absolute reference in r1c1 format? Or is there is a way to switch to A1 and back to r1c1?

I looked everywhere and I can't find the answer...thanks in advance.

Answer 1

If I stick =E3 into G5 and =$E$3 into G6 and then start a VB window and in the immediate window do this:

? ActiveSheet.Range("G5").Formula
=E3
? ActiveSheet.Range("G5").FormulaR1C1
=R[-2]C[-2]
? ActiveSheet.Range("G6").Formula
=$E$3
? ActiveSheet.Range("G6").FormulaR1C1
=R3C5

So the R and C make it relative to the current cell. You need to use square brackets when the number is negative otherwise Excel thinks you are subtracting a number from an invalid cell reference.

EDIT: It is worth mentioning that the reference is handled differently when absolute vs. relative.

• For relative references you are counting from the cell the formula is in. E3 is R[-2]C[-2] away from G5. i.e. 2 rows up, 2 column left.

• For absolute values you are counting from the top left corner. So E3 is R3C5. i.e. 3 rows down, 5 columns over. (thanks to @GeorgeDooling for the clarification)