VLookup adding another column to the search

Question

I am rather new to VBA and I am having trouble with a VLOOKUP code.

I have 3 sheets. Sheet2 (Supplier) contains the part number & supplier, Sheet 3 (Cost) contains the part number & cost. Sheet1 (Part Numbers) contains a standard part number list. The data in Sheets2 and 3 are updated regularly. I want the VBA to look for the part number (Sheet1.ColumnA) in the supplier list (Sheet2.ColumnA) and paste the supplier name (Sheet2.ColumnC) into Sheet1.ColumnB. Once I figure out how to do this I will then modify the code to do similar with the cost and put it in Sheet1.ColumnC.

Here's what I have so far:

Sub Lookup()

Application.ScreenUpdating = False

Dim Lr As Integer

Lr = Worksheets("Part Numbers").Cells(Rows.Count, 1).End(xlUp).Row ' last row

For i = 1 To Lr ' last row

Worksheets("Part Numbers").Cells(i + 1, 2) = "=VLOOKUP(RC[-1],'Supplier'!A:C,3,0)"

  If i = "" Then Exit For

Next i

End Sub

When I execute this code the Sheet1.ColumnB populates (Taken from cell A)

"=VLOOKUP(A2,'Supplier'!A:B:B,3,0)"

Can anyone tell me Why is it showing A:B:B when I enter A:C?

What are you trying to do with the line If i = "" Then Exit For? Seems like it will never do anything. The condition should never come back true. — Mark Balhoff
Thanks. I've read a lot of feeds on coding and it's probably something I picked up without understanding it properly — Kel

Mark Balhoff Mark Balhoff · Accepted Answer · 2014-07-16T05:29:14

My understanding has always been that mixing R1C1 and A1 notations is risky and likely to produce unexpected and undesirable results as Excel decides how to interpret the formula. Try the following:

Worksheets("Part Numbers").Cells(i + 1, 2) = "=VLOOKUP(RC[-1],'Supplier'!C1:C3,3,0)"

Or:

Worksheets("Part Numbers").Cells(i + 1, 2) = "=VLOOKUP(A" & (i + 1) & ",'Supplier'!A:C,3,0)"

In this particular case, I would probably go with the latter method just because C1:C3 is a little confusing at a glance as to whether it is A1 or R1C1 notation. If you aren't always looking up based on column A, you could determine the column letter from the column index with a small conversion method.

VLookup adding another column to the search

1 Answers