R1C1 v A1

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

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.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"andy_hammer2001(remove)" <"andy_hammer2001(remove)"@yahoo.com.au> wrote in
message news:OhLHuzvzFHA.3756@tk2msftngp13.phx.gbl...
> 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

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

"andy_hammer2001(remove)" <"andy_hammer2001(remove)"@yahoo.com.au> skrev i
melding news:OhLHuzvzFHA.3756@tk2msftngp13.phx.gbl...
> 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

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


"Harald Staff" <innocent@enron.invalid> wrote in message
news:#ulr85xzFHA.464@TK2MSFTNGP15.phx.gbl...
> 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
>
> "andy_hammer2001(remove)" <"andy_hammer2001(remove)"@yahoo.com.au> skrev i
> melding news:OhLHuzvzFHA.3756@tk2msftngp13.phx.gbl...
> > 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
>
>

Oops. Should read B1:K1 of course.

"Harald Staff" <innocent@enron.invalid> skrev i melding
news:%23ulr85xzFHA.464@TK2MSFTNGP15.phx.gbl...

> Enter numbers 1 to 10 in cells B2:K2.

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

"andy_hammer2001(remove)" <"andy_hammer20" wrote:

> 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
>

Many thanks Conrad, Harald and Mike. Much appreciated.

Cheers

Andy