VLOOKUP returns #N/A and causes VBA routine to halt

[tschebye]

I'm an absolute newcomer to Excel 2007 VBA (Started on my first
project 24 hours ago).

I'm trying to parse some data I have downloaded. I use VLOOKUP to
search for specific strings. It works fine if the string is present,
but if the string is not present then VLOOKUP returns #N/A which seems
to cause the execution of my routine to be halted. I have to tried to
wrap IsNA() around VLOOKUP, but my routine is still halted. I have
also tried to add "On Error Resume Next" but that seems to cause the
following check (IsNA) to fail (i.e. #N/A is not detected). I assume
the #N/A value gets wiped out by the "On Error Resume Next Statement".
How do I get Execl to continue executing when #N/A is returned, but at
the same branch out if #N/A was returned?

Here is what I want to do: I want to search my downloaded data for a
specific string. If the string is present then I want to grap the
associated data (and store them somewhere). If the string is not
present then I want to go and search for the next string and the whole
thing repeats itself until I get thru all my patterns.

 

[Dave Peterson]

If you use application.worksheetfunction.vlookup(), you have to code around
runtime errors.

Dim res as variant
on error resume next
res = application.worksheetfunction.vlookup(something, somerange,3, false)
if err.number <> 0 then
res = "not found"
err.clear
end if
msgbox res

=======
I find that using application.vlookup easier to understand and use:

Dim res as variant
res = application.vlookup(something, somerange,3, false)
if isserror(res) then
res = "not found"
end if
msgbox res

=======
The same technique applies to the =match() worksheet function, too.