R1C1 versus A1

  • Thread starter Thread starter Stuart Grant
  • Start date Start date
S

Stuart Grant

Can anyone tell me the advantages of using R1C1 cell references instead of
A1 - or point me to a web site dealing with it.

I changed one fairly large workbook to R1C1 but am finding a few
disadvantages - not all formulas or expressions seem to accept it - Range()
for instance.

Stuart
 
Remember that this setting is for excel--not VBA.

Kind of close to the R1C1 reference style in VBA would be cells(x,y).

And it won't matter if you're in A1 or R1C1 style in your code when you do:

activesheet.range("a1").formula = "=b1+c1"

But it will matter if you tried:
activesheet.range("a1").formulaR1C1 = "=b1+c1"

because =b1+c1 isn't close to being R1C1 reference style.

=======
Personally, the only times I use R1C1 is:

1. If I want to know the column numbers some set of columns (and I don't want
to put =column() in any helper cell.

2. I want to see if my formulas are consistent. If you turn on r1c1, you can
usually just eyeball the formulas when you're going down a column to see if they
look the same. (Tools|Options|view|formulas is nice for this check, too.)
 
Thank you, Dave. Very helpful. The Workbook has lots of VBA code and that
is where I have getting into trouble. I think I would be better changing it
back.
Problem is I have a lot of references where the contents of a cell is like
=R[-9]C5.
I assume I would have to change by hand.
Stuart
 
If you toggle that R1C1 setting, then won't your formulas change to A1 style?

I don't quite understand why you'd have to do anything by hand.

Stuart said:
Thank you, Dave. Very helpful. The Workbook has lots of VBA code and that
is where I have getting into trouble. I think I would be better changing it
back.
Problem is I have a lot of references where the contents of a cell is like
=R[-9]C5.
I assume I would have to change by hand.
Stuart
Click to expand...
 
Back
Top