Absolute reference in R1C1 notation

[Tim C]

Excel 2003 Beta

What is the syntax for including an absolute reference within R1C1 notation?

My latest attempt:

Cells(Selection.Row, 3).FormulaR1C1 = "=TRIM(SUBSTITUTE(RC[-2],A3,""""))"

But Excel adds single quotes around the A3.

Thanks,
Tim C

 

[Tim C]

Too easy. I used to know that.

Thanks,
Tim C

Tim,

That's because you've said use R1C1 notation, and then passed it A1
notation, so it thinks A1 is a string.

Try

Cells(Selection.Row, 3).FormulaR1C1 = "=TRIM(SUBSTITUTE(RC[-2],R3C1,""""))"

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks

Excel 2003 Beta

What is the syntax for including an absolute reference within R1C1 notation?

My latest attempt:

Cells(Selection.Row, 3).FormulaR1C1 = "=TRIM(SUBSTITUTE(RC[-2],A3,""""))"

But Excel adds single quotes around the A3.

Thanks,
Tim C

 

[steve]

Tim,

You are instructing Excel to use R1C1 notation and it doesn't recognize A3
as a cell reference. If it is one that use R3C1 instead. If A3 is text
than use ""A3""

As for Absolute Reference R3C3 is absolute R[3]C[3] is relative
The difference is in the brackets.

steve