VBA Code in Excel 2010 performing VLOOKUP using .FormulaR1C1

[raivyne]

Hi Everyone. I was trying to code a macro to do a VLOOKUP in the K cell of each used row and it wasn't working. here is the code that I used:

Set R2 = Intersect(sh4.Range("J:J"), sh4.UsedRange)

For rc2 = R2.count To 2 Step -1
    
On Error Resume Next

Cells(rc2, 11).FormulaR1C1 = "=VLOOKUP(RC[-9],[RESTRICTIONS.xlsx]SIC![B][COLOR=#ff0000]$A:$B[/COLOR][/B],2,FALSE)"

Next

Nothing was happening. So I recorded a macro performing the function on one of the cells and amended to the following code:

Set R2 = Intersect(sh4.Range("J:J"), sh4.UsedRange)

For rc2 = R2.count To 2 Step -1
    
On Error Resume Next

Cells(rc2, 11).FormulaR1C1 = "=VLOOKUP(RC[-9],[RESTRICTIONS.xlsx]SIC![COLOR=#ff0000][B]C1:C2[/B][/COLOR],2,FALSE)"

Next

The above code works, and shows the formula in each J cell to be "=VLOOKUP(B2,[RESTRICTIONS.xlsx]SIC!$A:$B,2,FALSE)"

I don't understand why using C1:C2 worked, and using the actual columns did not. Can someone please enlighten me?

 

[steve the fish]

C1 is column 1 (A) C2 is column 2 (B) using R1C1 notation which VBA uses to handle absolute or relative referencing.

 

[raivyne]

Yep, I'm a moron.

I've been staring at this code too long... thank you for setting me straight!