call to WorksheetFunction fails in 2003

[Alex T]

Folks,

for some reason the following code fails in Excel 2003

Application.WorksheetFunction.VLookup(x, y, z, false)

with error

"Unable to get the Vlookup property of the WorksheetFunction class"

What gives ?

And yes, I have checked the various security options in the Macro Security Tab.

Any pointer appreciated

--alexT

 

[Trevor Shuttleworth]

Alex

you need something like:

Sub test()
a = "not found"
On Error Resume Next
a = WorksheetFunction.VLookup(Range("A1"), Range("B1:C10"), 2, False)
On Error GoTo 0
MsgBox a
End Sub

The error is not very informative but basically it means that the lookup
value wasn't found in the lookup table.

Regards

Trevor

 

[J.E. McGimpsey]

On earlier versions of XL, VLookup did not play well as a member of
the WorksheetFunction collection. This worked though:

a = Application.VLookup(...)

I haven't tried it in XL03, so I don't know if the problem is the
same.

If the error was simply that the lookup value wasn't found, VLookup
would return Error 2042 to a, not give a run-time error.

 

[Trevor Shuttleworth]

JE

I don't have Excel 2003 so this is tested in Excel 2000 and, without the 'On
Error Resume Next', I get:

Run-time error '1004':
Unable to get the VLookup property of the WorksheetFunction class

which seems to concur with the OP.

This is with "x" in cell A1 and "a" to "c" in B1 to B3, "d" to "f" in C1 to
C3.

A value of "a" in cell A1 returns a "d" in the MsgBox as expected.

Can you help me to construct an example which will fail with error '2042' ?

Regards

Trevor

 

[Tom Ogilvy]

as J.E. said, qualify Vlookup with Application instead of Worksheetfunction.

 

[Trevor Shuttleworth]

Ah, reading what I expected to see, not what was written.

Regards

Trevor