Quick Links ▼

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA - Vlookup in a loop

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Excel VBA - Vlookup in a loop

Resolved · Urgent Priority · Version 2007

Reply Wed 1 Dec 2010, 08:56Delegate Steve said...

Steve has attended:
Excel VBA Intro Intermediate course

Excel VBA - Vlookup in a loop

What is the best way to use the vlookup worksheet function in a loop. For each row in a sheet I want to look up a particular value which is stored in second sheet, return the desired value to the designated target cell, them move down to the next row etc.

I have tried the following but get errors in the code:

intNumRowCount = Sheets("Transactions").Range("A4").CurrentRegion.Rows.Count

'counts number of rows in sheet "Transactions" from cell A4

For intRowCount = 1 To intNumRowCount

Sheets("Transactions").Cells(intRowCount, 16).Value = _
Application.WorksheetFunction.VLookup(Sheets("Transactions").Range("A4").Cells(intRowCount, 6), Sheets("Data").Range("A4:F432"), 6)

intRowCount = intRowCount + 1

Where sheets "Transactions" column 16 is where I want to place the result, based on looking up the value in "Transactions" column 6 in sheets "Data".

For upcoming training course dates see: Pricing & availability

Reply Thu 2 Dec 2010, 12:17Trainer Stephen said...

RE: Excel VBA - Vlookup in a loop

Hi Steve

Thanks for your question

Could you please advise me as to the nature of the errors that you get, i.e error number and dexcription

Thanks

Stephen

Reply Thu 2 Dec 2010, 12:24Delegate Steve said...

RE: Excel VBA - Vlookup in a loop

Run-time error '1004'
Unable to get the Vlookup property of the WorksheetFunction class.

Thanks

S.

Reply Thu 2 Dec 2010, 12:40Trainer Stephen said...

RE: Excel VBA - Vlookup in a loop

Hi Steve

Thanks for that

I had a similar problem a few months back. The first thing I would do is I would give the lookup table a range name and use that in the code rather than the range reference.

This might solve the problem as it will make the references absolute.

If this doesn't work, let me know and we can explore other options

Regards

Stephen

Reply Fri 3 Dec 2010, 15:14Delegate Steve said...

RE: Excel VBA - Vlookup in a loop

I tried what you have suggested by highlighting the range in the spreadsheet and giving it a name. Then using this name in the code instead of the cell references and I still get the same error message.

Thanks

S

Reply Tue 7 Dec 2010, 11:38Delegate Steve said...

RE: Excel VBA - Vlookup in a loop

I have manged to get the answer I require using another online forum.

Excel articles

· Importing Useful Information From The Web To Excel
· 5 Microsoft Excel Charts for Creating Stunning Business Documents
· Unsupported features in Excel 2007
· Streamlining Excel To Save Time
· MS Excel: Excel At Making The Most Of Your Christmas

Please browse our web site to find out more about
excel classes and other Microsoft training courses.

Excel tip:

Display pictures on Chart Data Point

Replacing a single chart data point bar with a picture.
Step 1: Left click on a bar. Then, wait, and do a second single click on the bar. This will select just one data point.

Step 2: Right click on the bar and select Format Data Point.

Step 3: On the fill effects tab, choose a picture. Browse for a picture for that bar. Indicate if you want it to be stretched or stacked. Repeat for each bar.

View all Excel hints and tips