Function Add-in troubleshooting-Vlookup

[Mike Rediger]

I use Vlookup extensively and attempted to add a new function, Vlook.

The function should 1) set the 4th argument to false, and 2) return an empty
string if the regular formula would return an error.

#2 is what I'm having a problem with.

Here's my code

Function vlook(cell,range,column)
vlook = Application.WorksheetFunction.vlookup(cell,range,column,FALSE)
if Application.WorksheetFunction.IsError(vlook) then vlook = ""
End Function


The code works for non-errors, however, returns #VALUE! instead of "" if the
formula cannot be resolved.

Help!

 

[Mike Rediger]

Sorry, I mistyped a line of code, my code is as follows:

Function vlook(cell,range,column)
vlook = Application.WorksheetFunction.vlookup(cell,range,column,FALSE)
if Application.WorksheetFunction.IsError(vlook) = TRUE then vlook = ""
End Function

 

[Colo]

Hi Mike,

*
The code works for non-errors, however, returns #VALUE! instead of ""
if the
formula cannot be resolved.

Help! *

Works for non errors? Do you have a line "On Error Resume Next"
somewhere?

Anyway, "Application.WorksheetFunction.VLookup" need error trap to work
for non errors like this.

Please try this code.


Code:
--------------------

Function vlook(cell, range, column)
On Error Resume Next
vlook = Application.WorksheetFunction.VLookup(cell, range, column, False)
If Err.Number <> 0 Then vlook = ""
End Function

--------------------

 

[Mike Rediger]

Perfect!

Thanks for your help!

Hi Mike,



Works for non errors? Do you have a line "On Error Resume Next"
somewhere?

Anyway, "Application.WorksheetFunction.VLookup" need error trap to work
for non errors like this.

Please try this code.


Code:
--------------------

Function vlook(cell, range, column)
On Error Resume Next
vlook = Application.WorksheetFunction.VLookup(cell, range, column, False)
If Err.Number <> 0 Then vlook = ""
End Function