Using VLookup with VBA

Posted by julio mata on January 15, 2002 10:42 AM

Hi everybody!
This is my problem:
I'm programming a report, so I've to seek for values in a worksheet called listconcept. I use the object application.worksheetfunction.vlookup, like this:

Set Range = ListConcept.Range("A1:C20")
Result = Application.WorksheetFunction.VLookup(VALUE, Range, 3, False)

So VBA generates error 1004, saying that is impossible to obtain the property Vlookup of the Worksheetfunction class

thanks

Posted by Juan Pablo G. on January 15, 2002 12:21 PM

That's the same as getting a #N/A! in the worksheet. Try dimming Result as a Variant and change your code to this

Result = Application.VLookup(Value,Range,3,False)

That way you'll get either the result or an error, but the program WON'T crash. You can test Result with the IsError() function

Juan Pablo G.

Posted by Julio Mata on January 16, 2002 12:34 PM

Gracias Juan Pablo
Lo que hice fue poner

On Error Resume Next
result=-1
Result = Application.WorksheetFunction.VLookup(VALUE, Range, 3, False)
if result&LT;&GT;-1 then
'Accion a tomar
end if

ya te había visto en el foro de Microsoft de Excel, pero hasta ahora entré a este sitio, y la verdad es mucho mejor, porque aquí si me respondieron rápidamente.

Muchas Gracias