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".
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
RE: Excel VBA - Vlookup in a loop
Run-time error '1004'
Unable to get the Vlookup property of the WorksheetFunction class.
Thanks
S.
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
