VLookup question in a macro

[Norm]

Why does an Excel macro stop when a VLookup can't find an
exact match? I would expect it to return N/A in my
variant, but it doesn't. Instead it returns a Run-time
error 1004: Unable to get the VLookup property of the
WorksheetFunction class.

Sample Code:
v = WorksheetFunction.VLookup(r1.Value, ProducerTable, 1,
False)

r1.Value contains producer codes like 61148. I want to
know if that code is in "ProducerTable" which is a one
column range of the valid producer codes.

I was then going to check for ISNA(v) to see if it worked.

OR is there a better way of verifying codes from a range??

THANK YOU!

 

[Bob Flanagan]

Norm, I suspect that you need to do the following:

v = WorksheetFunction.VLookup(range("r1").Value, range("ProducerTable"), 1,
False)

I am assuming ProduceTable is a range name and that you are using the value
of cell R1 on the active sheet. The Range function returns the range
objects that the vlookup needs.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Guest]

ProducerTable is a range variable in the macro ... not a
Named Range in the workbook. Also, r1 is another range
variable that contains the value that I want searched in
the table ... not the coordinates of the cell.

The formula works until I get a value that's not on the
table ... that's the problem.

Norm

 

[Tom Ogilvy]

having a 1 as the fourth argument should minimize the number of times when
the value is not found, but you can handle the error

On error resume next
v = WorksheetFunction.VLookup(r1.Value, _
ProducerTable, 1, False)
if err.Number <> 0 then
msgbox "Not found
exit sub
End if
On Error goto 0
msgbox "value returned is " & v