PDA

View Full Version : [SOLVED] STRUGGLING VLOOKUP in VBA



Vanzuck
03-27-2019, 08:18 AM
I've 2 questions I'm not able to solve from myself
They regard the usage of Vlookup in VBA, within an Excel module.
First question: I do want just to have the row number of the item found
Second question: what is the easiest way to manage the "Found/Not Found" ?
I did the following but, I'm not proud of it:

LookedItem = "Not Found"
On Error Resume next
LookedItem = Application.WorksheetFunction.Vlookup(arguments)

thanks

p45cal
03-27-2019, 03:37 PM
You could try Application.Match which will return a number (or an error in the assigned variable (don't use WorksheetFunction.Match)), then check for the error in the assigned variable.
Or
Dim myCell as Range
Set myCell = range.find(arguments)
If myCell is Nothing then myRow="Not found" else myRow=myCell.row

If you've got that in a loop you need to Set myCell=Nothing each time.

Vanzuck
03-28-2019, 03:05 AM
In the meantime of your reply I tried the MATCH and FIND functions, but your suggestion is better, a lot better
So I appreciated a lot your help, and going to mark SOLVED
thanks



You could try Application.Match which will return a number (or an error in the assigned variable (don't use WorksheetFunction.Match)), then check for the error in the assigned variable.
Or
Dim myCell as Range
Set myCell = range.find(arguments)
If myCell is Nothing then myRow="Not found" else myRow=myCell.row

If you've got that in a loop you need to Set myCell=Nothing each time.