Vlookup Error

[MJRay]

Hello,

I have the following code to perform a lookup:
mtch = Application.WorksheetFunction.VLookup( _
Worksheets("Nabanco").Cells(nr, 14).Value, _
Worksheets("RDMTemp").Range("A1:T5000"), _
20, False)
The error I get is "Run time error 1004, Unable to get the vlookup property
of the WorksheetFUnction class.

Thanks,
Mike

 

[Toppers]

Check there is a (valid) value in Cells(nr,14) . If it is 0 you will get
your error message. Otherwise seems to work OK for me.

HTH

 

[Glenn Ray]

If it's likely that no vlookup result will be found, you'll want to trap the
error something like this:

Sub xxx()
On Error GoTo ErrorHandler
strSRC = Application.WorksheetFunction.VLookup(string, range, column,
False)

'normal code here
'end of normal code
Exit Sub

ErrorHandler:
If Err = 1004 Then 'couldn't find a match
MsgBox "No results could be found", vbOKOnly, "Error"
Err.Clear
End If

End Sub

 

[Dave Peterson]

If you drop the .worksheetfunction, you can test for the error:

dim mtch as Variant 'could return an error

mtch = Application.WorksheetFunction.VLookup( _
Worksheets("Nabanco").Cells(nr, 14).Value, _
Worksheets("RDMTemp").Range("A1:T5000"), _
20, False)

if iserror(mtch) then
'it returned an error
else
'no error
end if