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:
Nothing was happening. So I recorded a macro performing the function on one of the cells and amended to the following code:
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?
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![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:
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?