CODEWITHSUNDEEP
vbaexcel
frogjockey
frogjockey

Reputation: 161

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

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.

Upvotes: 16

Views: 71361

Answers (4)

Jon
Jon

Reputation: 53

R3C5

No brackets = absolute

Hitting f4 in the Cell window when editing a formula will toggle relative or absolute

Upvotes: 1

Regret
Regret

Reputation: 51

The R1C1 format can be used both for absolute and relative references. R1C1 without brackets is absolute, when you use brackets it is relative.

R3C5 always points at E3 no matter where you use it. As you already know, R[3]C[5] always points 3 down and 5 to the right of your current location.

As George Dooling already said in one of the comments, the reason R-3C-5 is meaningless is because you are off the sheet. The sheet starts at R1C1 and the numbers only goes up.

As to your second question, you can use the A1 format by enclosing it with quotes. R3C5 can be written as "E3" within FormulaR1C1 without problems.

Upvotes: 3

Jerry Jeremiah
Jerry Jeremiah

Reputation: 9618

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)

Upvotes: 27

dra_red
dra_red

Reputation: 442

Sometimes the 'Application.ConvertFormula' function is useful for going between reference styles. For example, adding this to the immediate window:

debug.print application.ConvertFormula("=PRODUCT(O7,P$7)",xla1,xlr1c1)

returned this:

=PRODUCT(RC[-5],R7C[-4])

That is a trivial example but when you have a monster formula on your hands...

Also, switching between reference styles in VBA is done via:

application.ReferenceStyle = xlA1

or

application.ReferenceStyle = xlR1C1

Upvotes: 3

Related Questions