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,c olumn,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!

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

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



"Mike Rediger" wrote in message
news:5UQBb.6442$US3.1042@okepread03...
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,c olumn,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!

Hi Mike,

Mike Rediger wrote:
*
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

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



---
Message posted from http://www.ExcelForum.com/

Perfect!

Thanks for your help!

"Colo" wrote in message
...
Hi Mike,

Mike Rediger wrote:
*
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

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



---
Message posted from http://www.ExcelForum.com/