Understanding R1C1
 

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

Understanding R1C1
 

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" wrote:

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

Understanding R1C1
 

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" wrote:

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" wrote:

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

Understanding R1C1
 

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


--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility


"Alex" wrote in message
...
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))"

Understanding R1C1
 

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)
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" wrote:

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

Understanding R1C1
 

Thank you all; much clearer now.

"Alan" wrote:

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)
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" wrote:

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