Understanding R1C1

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I'm trying to figure out what this code does. Can someone tell me what
Column(R6C[5]) indicates? Is that cell E6? Thanks. The formula seems to be
working, but it formula is extracting data from column N. Does Column(R6[5])
indicate column N? Thanks for your help.

If Selection.Locked = False Then ActiveCell.FormulaR1C1 =
"=if(ISERROR(VLOOKUP(RC3,IPDD!IPDD,(Column(R6C[5]) -
column(R6C3)+1),FALSE)),"" "",VLOOKUP(RC3,IPDD!IPDD,(Column(R6C[5]) -
column(R6C3)+1),FALSE))"
 
The C[-1] refers to one row to the left of current cell

I believe you meant to say "one column to the left of current cell"


Alan



dmoney said:
Here is an example with explanation:
ActiveCell.FormulaR1C1 = "=R[2]C[-1]"

The R[2] refers to two rows down from current cell
The C[-1] refers to one row to the left of current cell

the bracketed number represents the number of row or column from the current
cell. If you were on cell c6 and input a formula on b5 then the r would be
-1 and the c would be -1

HTH

Devin

Alex said:
I'm trying to figure out what this code does. Can someone tell me what
Column(R6C[5]) indicates? Is that cell E6? Thanks. The formula seems to be
working, but it formula is extracting data from column N. Does Column(R6[5])
indicate column N? Thanks for your help.

If Selection.Locked = False Then ActiveCell.FormulaR1C1 =
"=if(ISERROR(VLOOKUP(RC3,IPDD!IPDD,(Column(R6C[5]) -
column(R6C3)+1),FALSE)),"" "",VLOOKUP(RC3,IPDD!IPDD,(Column(R6C[5]) -
column(R6C3)+1),FALSE))"
Click to expand...
Click to expand...
 
This is called R1C1-style referencing.

RC is Row/Column addressing. Brackets make it relative to the location of
the formula at runtime. No brackets along with a row/column integer
reference make it absolute.

Range("B1").Select
ActiveCell.FormulaR1C1 = "=R1C1"
refers to row 1, column 1, and will add the formula "=$A$1". Note the $
which make it absolute as there are no brackets.

ActiveCell.FormulaR1C1 = "=RC1"
refers to the current row, column 1 during runtime.

Range("K5").Select
ActiveCell.FormulaR1C1 = "=RC[5]"
adds "=P5" to the cell, or 5 columns from K.

R1C1-style is powerful. If you wanted to, say for example, add the SUM
function below two columns of data of varying lengths, R1C1 would let you in
one action. Say you had:

Column A Column B
100 444
200 541
300 852
400 <sum>
500
<sum>

Column A has more rows than column B, and if we wanted the SUMs to be
directly below the last data rows, one line of code does it:

Range("A6, B4").FormulaR1C1 = "=SUM(R1C:R[-1]C)"
 
E6 would be:

Cells(6,5)

or you can just reference it as Range("E6")

Your formula can't possibly work. References you are using, like
"Column(R6C[5]) & column(R6C3)", are invalid references and will error.
Column(R6C[5]) - column(R6C3)+1)
Click to expand...
This is the reference that you are using to denote which column value to
return for the lookup. This is invalid. You need to look at Vlookup
instructions again and restructure your formula to your needs or post better
instructions about what you are trying to lookup, where you are looking, and
what column to return if found.


Alan


Alex said:
I'm trying to figure out what this code does. Can someone tell me what
Column(R6C[5]) indicates? Is that cell E6? Thanks. The formula seems to be
working, but it formula is extracting data from column N. Does Column(R6[5])
indicate column N? Thanks for your help.

If Selection.Locked = False Then ActiveCell.FormulaR1C1 =
"=if(ISERROR(VLOOKUP(RC3,IPDD!IPDD,(Column(R6C[5]) -
column(R6C3)+1),FALSE)),"" "",VLOOKUP(RC3,IPDD!IPDD,(Column(R6C[5]) -
column(R6C3)+1),FALSE))"
Click to expand...
 
Thank you all; much clearer now.

Alan said:
E6 would be:

Cells(6,5)

or you can just reference it as Range("E6")

Your formula can't possibly work. References you are using, like
"Column(R6C[5]) & column(R6C3)", are invalid references and will error.
Column(R6C[5]) - column(R6C3)+1)
Click to expand...
This is the reference that you are using to denote which column value to
return for the lookup. This is invalid. You need to look at Vlookup
instructions again and restructure your formula to your needs or post better
instructions about what you are trying to lookup, where you are looking, and
what column to return if found.


Alan


Alex said:
I'm trying to figure out what this code does. Can someone tell me what
Column(R6C[5]) indicates? Is that cell E6? Thanks. The formula seems to be
working, but it formula is extracting data from column N. Does Column(R6[5])
indicate column N? Thanks for your help.

If Selection.Locked = False Then ActiveCell.FormulaR1C1 =
"=if(ISERROR(VLOOKUP(RC3,IPDD!IPDD,(Column(R6C[5]) -
column(R6C3)+1),FALSE)),"" "",VLOOKUP(RC3,IPDD!IPDD,(Column(R6C[5]) -
column(R6C3)+1),FALSE))"
Click to expand...
Click to expand...
 
Back
Top