R1C1 Property in VBA module

[SOS]

Hi all,

I am in my spreadsheet and attempting to automate it. I want to plac
a formula in (for example) cell D3 which says =C3*B3.
I have the following line of code where D3 is the Active Cell:

ActiveCell.Formula = "=RC[-1]*RC[-2]

and it works

However I then want a formula in E3 to say C3-C2.

My question is how to reference cell C2 if RC[-2] refers to C3 an
RC[-3] refers to B3.

Hope I've made myself clear

Many thanks

Seamu

 

[Guest]

hi,
=rc[-2]-r[1]c[-2]
for r (row)-positive numbers are # of rows down
negative numbers are # of rows up
for c (column)-positive numbers are # of columns to right
negative numbers are # of columns to left

 

[Harald Staff]

Hi Seamus

It works like this: Numbers are absolute addresses, numbers in [ ] and no
numbers are relative

R1C2 is Row 1 column 2
RC2 is This row column 2
R[1]C2 is This row plus 1 column 2
R[-1]C2 is This row minus 1 column 2
R[-1]C[-2] is This row minus 1 this column minus 2

so
R[-1]C[-2]-RC[-2]
if I understand you right.

HTH. Best wishes Harald

 

[SOS]

Excellent. Thanks to you both for such quick replies (as always on thi
forum). I have now fixed the formulae and they work just fine.

Seamu

 

[Tom Ogilvy]

The others have given you a good explanation of R1C1 notation. To
implement, I went into E3 and put in the formula

=C3-C2

Then when to tools=>Options and select the general tab, then checked R1C1
Reference file and looked at the formula. It produced:

=RC[-2]-R[-1]C[-2]

also, when assigning a formula to a cell with R1C1 notation you should do

Range("E3").FormulaR1C1 = "=RC[-2]-R[-1]C[-2]"

or you could just do

Range("E3").Formula = "=C3-C2"

While
Range("E3").Formula = "=RC[-2]-R[-1]C[-2]" does work, it is not reliable
in every case, so it is better to use FormulaR1C1.

When using Formula or FormulaR1C1 with the correct argument, it doesn't make
any difference what reference mode Excel is in.