call to WorksheetFunction fails in 2003

  • Thread starter Thread starter Alex T
  • Start date Start date
A

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
 
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
 
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.
 
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
 
Back
Top