Help with Worksheetfunction

Posted by Juan Pablo on September 07, 2001 9:28 AM

I'm trying to find a value using the Vlookup function, but using it directly in VBA as Worksheetfunction.Vlookup, but it keeps telling me "Impossible to find the Vlookup property of the Worksheetfunction class.

Here's the code

Cells(F1,C1+2) = WorksheetFunction.VLookup(CStr(Cells(F1, C1)), Hoja1.Range("G3", "I123"), 3, 0)

What's wrong ?

Thanks
Juan Pablo

Posted by AB on September 07, 2001 11:30 AM

Well lets see...

I doubt you need the CStr function in there and the range reference of Hoja1.Range("G3","I123") should really look like this:
Hoja1.Range("G3:I123")

This is my version of your formula:

Set Fn = Application.WorksheetFunction
Cells(F1, C1 + 2) = Fn.VLookup(Cells(F1, C1), Sheet1.Range("G3:I123"), 3, False)

Seems to work OK.

I'm assuming the "F1" and "C1" are simply variables and not cell references. That would be a whole nother problem.

Regards,
AaronThe Excel Logic Page

Posted by Juan Pablo on September 07, 2001 12:15 PM

I think i know what the error is, the range as you mention it, works too, but it makes no difference. It generates the error when the Vlookup can't find a result, in other words, the same as in Excel resulting in #N/A...

I tricked the procedure, by inserting a On Error Resume Next, because it wouldn't recognize either a condition, IF IsError(Worksheetfunction...) then Cells() = "" else Worksheetfunction. This didn't work either.

Any suggestions ?

Juan Pablo