I wouldn't do this.

What happens if the value that should be returned is 0?

And if you don't change res to 0 before you do the =vlookup(), it won't change
to 0. It'll still be what it was befo

Dim res As Long
res = 111
On Error Resume Next
res = Application.VLookup("someval", somerangehere, 2, False)
If res = 0 Then
MsgBox "no match"
Else
MsgBox res
End If
On Error GoTo 0



Patrick Molloy wrote:

no real need to test the worksheetfunction for an error as by default, the
value will be zero

dim res as long
on error resume next
res = application.vlookup("someval", somerangehere, 2, false)
if res = 0 then
msgbox "no match"
else
msgbox res
end if
on error goto 0

"Dave Peterson" wrote in message
...
There is a difference with a few functions on how errors are treated
between:

Application.vlookup() (and application.match())
and
application.worksheetfunction.vlookup()
(and application.worksheetfunction.match())

If you don't use the .worksheetfunction portion, then you can test the
returned
results for an error:

Dim res as variant 'could be an error
res = application.vlookup("someval", somerangehere, 2, false)
if iserror(res) then
msgbox "no match"
else
msgbox res
end if

===========
But when you use worksheetfunction.vlookup(), then you'll have to program
against a run time error.

dim res as long 'or string or even variant
on error resume next
res = application.vlookup("someval", somerangehere, 2, false)
if err.number < 0 then
err.clear
msgbox "no match"
else
msgbox res
end if
on error goto 0

=========
And since it looks like you're trying to find if there's a match (since
you
bring back what's in column 1 of the table). I'd use:

Dim res as variant
res = application.match(cRegion, rngfsc.columns(1),0)
if iserror(res) then
'not there
else
'it's on row # Res of the first column of rngfsc.
end if


VBANovice wrote:

Hello,

I have a code that is looking for a Vlookup. I know that some of the
queries are N/A# and to have On Error Resume Next error handling.
The code is perfectly working in one computer and is generating "1004 run
time error : Unable to get the Vlookup propert of the WorksheetFunction
class error" in another.

I have reloaded the Excel and Repaired, Diagnosed Excel, restored 2-3
days
before from the backup. None yielded any cure.

I work on XP Pro, have both 2003 & 2007 version generate the same fault.
It seems as if On Error Resume is doomed.

Any suggestions or perhaps remedies mostly welcomed and highly
appreciated.

Regards, VBA Novice.

--

Dave Peterson

--

Dave Peterson