application.worksheetfunction.vlookup

[JulieD]

Hi

for the first time i'm using worksheet functions in vba and was surprised to
find that
k =
application.worksheetfunction.vlookup(pipes(j),Sheets("Database").Range("Pip
e_Number"),2,0)

shows k as empty rather than "erroring" or #NA .. i tried to get a handle on
this in vba help but it didn't seem to explain this behaviour - can anyone
explain to me why and, in some ways, more importantly how i can get "k" to
"error" if the item isn't found?

Thanks
JulieD

 

[fred]

Julie, when I try it certainly does error when the item is not found. I
would suggest that it does find the item in the 1st column but that the cell
in the second column is empty and so this empty value is returned. This is
not an error since the item is found.

Fred

 

[Frank Kabel]

Hi Julie
this happens if the item is not found. One way arround it:

on error resume next
kapplication.worksheetfunction.vlookup(...)
if err.number<>0 then
k="Error"
end if
on error goto 0
msgbox k

 

[JulieD]

Hi Fred

thanks for your reply ... now i know what i did ...(the light's just gone
on!), the first half dozen or so times i ran it it did error and then i made
some changes for another reason and populated lookup value column with the
value prior to this bit of code, which is why it no longer errors out ....
because, as you said, it is there, but the second column is blank.

okay i might have to see if Frank's method will cause it to error out ....

Thanks again
JulieD

 

[JulieD]

Hi Frank

sorry haven't tried your code, (the workbook's at work), but just looking at
it, doesn't this just set the value of k to the word "error", what i'm
wanting to do is force it to have an error and drop to the error handling
routine.

Cheers
JulieD

 

[Tom Ogilvy]

Another way is to use only application as the qualifier

Dim res as Variant
res = Application.Vlookup()
if iserror(res) then
msgbox "Not found"
Else
msgbox "Value returned is " & res
End if

If you use worksheetfunction as the qualifier, then it returns a trappable
error (1004 type error).