CODEWITHSUNDEEP
vbaexcelexcel-2016
Skyblue
Skyblue

Reputation: 35

What does this line of FormulaR1C1 mean?

I get what FormulaR1C1 means from this link.
But I couldn't get it when it was used with an ampersand & in VBA and Vlookup here in this line:

.Range(Cells(2, 3), Cells(2, 79)).FormulaR1C1 = "=VLOOKUP(RC1&R1C,POR_Vert!C4:C6,3,FALSE)"

Upvotes: 0

Views: 1898

Answers (2)

CLR
CLR

Reputation: 12279

RC1 translates to this row, column 1. R1C translates to row 1, this column.

So if you're writing this formula to cells(2,3) (which is $C$2) then it is interpreted as:

"=VLOOKUP($A2 & C$1,POR_Vert!$D:$F,3,FALSE)"

So, for example, if $A2 contains "Searc" and C$1 contains "hKey" then it would be the equivalent of:

"=VLOOKUP(RC1 & R1C,POR_Vert!C4:C6,3,FALSE)"         'becomes..
"=VLOOKUP($A2 & C$1,POR_Vert!$D:$F,3,FALSE)"         'becomes..
"=VLOOKUP("Searc" & "hKey",POR_Vert!$D:$F,3,FALSE)"  'becomes..
"=VLOOKUP("SearchKey",POR_Vert!$D:$F,3,FALSE)"

Upvotes: 1

Rory
Rory

Reputation: 34065

& is the concatenation operator so the lookup value is column A of the current row with row 1 of the current column appended to it. So in C2, you are taking the value of A2 and appending C1 to the end, then looking up the result.

Upvotes: 0

Related Questions