Ghost in the Machine - 1004 Vlookup & On Error Duo Trouble

[VBANovice]

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.

 

[Jacob Skaria]

Are you sure VLOOKUP() is getting a match..If there is no match this will
return error.

Application.WorkSheetFunction.Vlookup()

If this post helps click Yes

 

[VBANovice]

Dear,

I am afraid this is definetly not a cure for my Ghost.

There is already an On Error Resume Next part.

Ghost in the Machine

Anyhow, thanks for the effort.

Regards,

NoviceVBA

 

[Gary''s Student]

Say A1 contains:
cat

and F1 thru G11 contain:

dog 1
cat 2
mouse 3
rat 4
house 5
flea 6
flower 7
fish 8
tree 9
bush 10
car 11

then in the worksheet the formula:
=VLOOKUP(A1,F1:G11,2,FALSE) will return a 2


In VBA, use Application and not Application.Worksheet function.

Also make sure Vlookup understands the type of the arguments:

Sub vlookupDemo()
Range("B9").Value = Application.VLookup(Range("A1"), Range("F1:G11"), 2, 0)
End Sub

will also find the same 2

 

[Dave Peterson]

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

 

[Patrick Molloy]

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]

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 before:

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

 

[Patrick Molloy]

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.

 

[VBANovice]

Dear Gurus,

Thank you for your replies and efforts.

I have the Vlookup statement working. Thanks for the full in depth
tutorials. Very helpfull yet, doesnt help solving the issue. Still it is not
working.

The problem ( in simplified wording is ;

I have a code working in one laptop.
In machine A, the code is running with the same data sheets.
In machine B, the code is not working with the same data sheet.
They have both XP Pro OS, 97 and 2007 Excel versions loaded.

I receive the 13 Error Code when I run your lines of code, where I expected
not to get any error messages because you have included "On Error" statements.

I understand that the On Error statement is not taking charge of the errors
in the machine B. I am trying to understand why ? and how to resolve this
issue.

I tried to reinstall Excel but it did not solve the problem either.

Still if I am asking for your help and time for a cure to my Ghost in the
machine.

Thanks and regards,

VBANovice.

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