R1C1 v A1

[andy_hammer2001(remove)]

Hi

Just wondering if there is a real difference in using R1C1 as opposed to
A1? is it just a matter of personal preference or are there times in
vba where you would refer to R1C1 over A1?

Thanks

Andy

 

[Conrad Carlberg]

Hi Andy,

As you imply, VBA plays a part. Occasionally I find it necessary in VBA to
refer to a column that's well to the right of column A, using notation of
the .Cells(i,j) sort. This is usually when I'm looping through cells.

I've never been able to maintain a mental association of a column letter
with a column number once I get beyond F; probably some sort of synaptic
deficiency. So I temporarily switch from A1 to R1C1 and check out the column
number -- this has proven more reliable than counting it out on my fingers.
T.O. (not the Philly receiver) confesses that he does this, too.

Apart from that, I've never encountered a good reason to use R1C1 as a
matter of course.

 

[Harald Staff]

Hi Andy

Try this little experiment. Let's make a multiplication table in A1 style.
Enter numbers 1 to 10 in cells A2:A11.
Enter numbers 1 to 10 in cells B2:K2.
Formula in Cell B2:
=$A2*B$1

Fill this right and down. Cell K11 will read
=$A11*K$1

Now switch to R1C1. Cell K11 will switch to
=RC1*R1C
.... and so will all the other 99 formula cells. They all contain identical
formulas.

Apart from the beauty of this, it is a true gift if you use VBA code to
generate formulas. You don't have to care about where you are.

Say, if you need to sum all numbers above a cell, then you must either know
the cell location, here A12, to construct the formula
=SUM(A1:A11)
or just switch to R1C1, where this will work in absolutely any cell below
row 1:
=SUM(R1:R[-1])

Personally I switch back and forth between the two a lot. Although I like
the clarity of R1C1, I find relative referenciong in R1C1 almost impossible
to read and audit. This:
=SUM(R[-12]C[-7]:R[-10]C[-7])
rarely makes sense to me, this is the same and very clear:
=SUM(B2:B4)

I believe that Excel internally handles all formulas as english R1C1. So
when you reach the limit of a formula length, it is not obious what the
limit exactly is in a foreign language Excel and/or in A1 style.

HTH. Best wishes Harald

 

[Conrad Carlberg]

Okay, Harald, you cite a pretty good reason. I never thought of the relative
referencing issue.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005

Hi Andy

Try this little experiment. Let's make a multiplication table in A1 style.
Enter numbers 1 to 10 in cells A2:A11.
Enter numbers 1 to 10 in cells B2:K2.
Formula in Cell B2:
=$A2*B$1

Fill this right and down. Cell K11 will read
=$A11*K$1

Now switch to R1C1. Cell K11 will switch to
=RC1*R1C
... and so will all the other 99 formula cells. They all contain identical
formulas.

Apart from the beauty of this, it is a true gift if you use VBA code to
generate formulas. You don't have to care about where you are.

Say, if you need to sum all numbers above a cell, then you must either know
the cell location, here A12, to construct the formula
=SUM(A1:A11)
or just switch to R1C1, where this will work in absolutely any cell below
row 1:
=SUM(R1:R[-1])

Personally I switch back and forth between the two a lot. Although I like
the clarity of R1C1, I find relative referenciong in R1C1 almost impossible
to read and audit. This:
=SUM(R[-12]C[-7]:R[-10]C[-7])
rarely makes sense to me, this is the same and very clear:
=SUM(B2:B4)

I believe that Excel internally handles all formulas as english R1C1. So
when you reach the limit of a formula length, it is not obious what the
limit exactly is in a foreign language Excel and/or in A1 style.

HTH. Best wishes Harald

Hi

Just wondering if there is a real difference in using R1C1 as opposed to
A1? is it just a matter of personal preference or are there times in
vba where you would refer to R1C1 over A1?

Thanks

Andy

 

[Harald Staff]

Oops. Should read B1:K1 of course.

 

[Guest]

http://chacocanyon.com/smm/readings/references.shtml
or go to excel help and enter
cell reference

 

[andy_hammer2001(remove)]

Many thanks Conrad, Harald and Mike. Much appreciated.

Cheers

Andy