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


"Alex T" wrote in message
om...
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

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.

In article ,
"Trevor Shuttleworth" wrote:

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


"Alex T" wrote in message
om...
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

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


"J.E. McGimpsey" wrote in message
...
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.

In article ,
"Trevor Shuttleworth" wrote:

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


"Alex T" wrote in message
om...
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

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

--
Regards,
Tom Ogilvy

Trevor Shuttleworth wrote in message
...
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


"J.E. McGimpsey" wrote in message
...
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.

In article ,
"Trevor Shuttleworth" wrote:

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


"Alex T" wrote in message
om...
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

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

Regards

Trevor


"Tom Ogilvy" wrote in message
...
as J.E. said, qualify Vlookup with Application instead of
Worksheetfunction.

--
Regards,
Tom Ogilvy

Trevor Shuttleworth wrote in message
...
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


"J.E. McGimpsey" wrote in message
...
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.

In article ,
"Trevor Shuttleworth" wrote:

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


"Alex T" wrote in message
om...
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