Tags: absolute, attempt, beta, cells, excel, formular1c1, including, latest, microsoft, msdn, notation, r1c1, reference, row, selection, software, syntax

Absolute reference in R1C1 notation

On Microsoft » Microsoft Excel

2,077 words with 2 Comments; publish: Fri, 23 May 2008 09:23:00 GMT; (306125.00, « »)

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

All Comments

Leave a comment...

  • 2 Comments
    • Too easy. I used to know that.

      Thanks,

      Tim C

      "Bob Phillips" <bob.phillips.excel.questionfor.info.tiscali.co.uk> wrote in message

      news:%23w5dhVrYDHA.2308.excel.questionfor.info.TK2MSFTNGP12.phx.gbl...

      > 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

      >

      > "Tim C" <timclainc.excel.questionfor.info.yahoo.com> wrote in message

      > news:evRCO5qYDHA.652.excel.questionfor.info.tk2msftngp13.phx.gbl...

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

      #1; Fri, 23 May 2008 09:24:00 GMT
    • 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

      "Tim C" <timclainc.excel.questionfor.info.yahoo.com> wrote in message

      news:evRCO5qYDHA.652.excel.questionfor.info.tk2msftngp13.phx.gbl...

      > 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

      >

      #2; Fri, 23 May 2008 09:25:00 GMT