Worksheetfunction.vlookup and #value error

[Guest]

Dear all,

I got a Master Sales worksheet and 5 individual sales worksheet. I would
like to check whether all the items in the Master worksheet has been updated
in individual one. I write a CheckSales function which has two parameters,
one is the vendor name which I use to determine the worksheet to do the
vlookup(all the worksheet structure are identical and column D(4) contains
the invoice #) and the other parameter is the invoice # (text format) which
is unique. The following is the code for the vlookup

If WorksheetFunction.IsNA( _
WorksheetFunction.VLookup _
(Invoice, Worksheets(Vendor_Work_sheet).Columns(4), 1, 0)) Then
CheckSales = "Pls update"
Else
CheckSales = "OK"
End If

The function works in some cases. As I call different worksheet in the
code, is there anything I should be aware? I found the code can be dead when
it run to vlookup statement during debugging, but strangely, not all the time!

If the function works, it will give me OK when the invoice is found; but
when the invoice is not found, instead of returning "Pls update"; it gives us
#value! I cannot figure out the problem.

Thank you

BL

 

[Niek Otten]

I think VBA doesn't give ISNA a chance to be executed; it stops on the error
in VLOOKUP straightaway.
If you want to trap the error, you'll have to use VBA error trapping, not a
worksheet function.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Guest]

Thank Niek,

That is what I believe, as when I set up a variable to store the result for
the vlookup value, there is a Run-time Error 1004 complaining the
Application-defined or object-defined error when there is no value found.

Any suggestion how to evaluate the error using VBA! Your help is much
appreciated. Thank you.

BL