Error 1004-Unable to get VLookup property of worksheet function class 1

[AirZebra]

Hi,

I am fairly new to VBA and am having trouble getting a VLookup function to work (to be honest I don't really understand the error message). The code goes like this:

Dim LastPeriodNo As Variant
Dim CODAS_Periods As Range
Set CODAS_Periods = Workbooks("Personal.xls").Worksheets("CODASPeriods").Range("A3:F54")

LastPeriodNo = Application.WorksheetFunction.VLookup(Now(), CODAS_Periods, 2, True)[\CODE]

When I run this I get the error message:
"Run Time Error 1004
Unable to get the VLookup property of the worksheet function class"

It should hopefully look up today's date in a table and get a corresponding period number from the second column.  Can anyone explain what this really means and if it is possible to get around it?

Regards,

Alan

 

[rmikesmith]

Alan,

Try this altering your code to read like this:

LastPeriodNo = Application.VLookup(Now(),
                    CODAS_Periods, 2, True)

i.e., remove "WorksheetFunction". I'm not sure why this works...it seems to be contrary to the documentation.


Regards,
M. Smith

 

[AirZebra]

Thanks Mike,

It has changed the error message to Run time error 13 - type mismatch. Should I use a date function to extract the date from the Now() function? or is it saying something else?

Regards,

Alan

 

[AirZebra]

Sorry Mike, Ignore that last post - it is a different bit of the code that is bringing up the error message.

Alan

 

[cRODEEkrank]

Actually, I'm having the same problem. I've used the "Application.VLookup" and now receive a "Type Mismatch" error message after trying to use "Application.WorksheetFunction..." and receiving the "VLookup not found" error message.

Here's my code:
dim pcn as variant
dim lngFinGdNum as long
dim rngVLookup as string

rngVLookup = "A:B"
lngFinGdNum = 18291517

pcn = Application.VLookup(lngFinGdNum, rngVLookup, 2)

Any help with this would be great. Thanks in advance!

 

[rmikesmith]

cRODEEkrank,

If you check the online help for VLookup you will see that where you have rngVLookup in the function call, Excel is expecting a range or range name. In fact, you are supplying a string, hence the Type Mismatch error. You could substitute something like:

Dim rngVLookup as Range

Set rngVLookup = Range("A:B")

Hope this helps
M. Smith