Hi

for the first time i'm using worksheet functions in vba and was surprised to
find that
k =
application.worksheetfunction.vlookup(pipes(j),She ets("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

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

"JulieD" wrote in message
...
Hi

for the first time i'm using worksheet functions in vba and was surprised
to
find that
k =

application.worksheetfunction.vlookup(pipes(j),She ets("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

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


-----Original Message-----
Hi

for the first time i'm using worksheet functions in vba
and was surprised to
find that
k =
application.worksheetfunction.vlookup(pipes(j),Sh eets
("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


.

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


"fred" wrote in message
...
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

"JulieD" wrote in message
...
Hi

for the first time i'm using worksheet functions in vba and was
surprised
to
find that
k =


application.worksheetfunction.vlookup(pipes(j),She ets("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

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

"Frank Kabel" wrote in message
...
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


-----Original Message-----
Hi

for the first time i'm using worksheet functions in vba
and was surprised to
find that
k =
application.worksheetfunction.vlookup(pipes(j),Sh eets
("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


.

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).

--
Regards,
Tom Ogilvy

"Frank Kabel" wrote in message
...
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


-----Original Message-----
Hi

for the first time i'm using worksheet functions in vba
and was surprised to
find that
k =
application.worksheetfunction.vlookup(pipes(j),Sh eets
("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


.