VBA Code in Excel 2010 performing VLOOKUP using .FormulaR1C1

raivyne

New Member
Joined
Jul 29, 2014
Messages
13
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:

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?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
C1 is column 1 (A) C2 is column 2 (B) using R1C1 notation which VBA uses to handle absolute or relative referencing.
 
Upvote 0
Yep, I'm a moron.

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

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top