mine was a suggestion only, to open up ideas.

generally I'd wrap all worksheet functions that raise errors in a VBA
function. I accept your argument re zero values, however, when returning an
error its not difficult to decide upon a number like -999 or text that
denotes an issue...after all, the developer should know what is expected and
should be able to decide what constitutes good error handling and
housekeeping.


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