UNable to get vlookup property of worksheet function class

[DeanP]

I'm attempting to run the code below, but I get this worksheet function class error. Have tried many different ways, same error. Debugging shows Error 2042 on Range L1. Don't know what to do.

I am looking up the value in K1 in Dashboard worksheet - which is the number 1 in my Validation worksheet in cells B2:C13. The number 1 is in B2, and the value to be returned is in C2 = January. I want it to be shown in L1 Lon the Dashboard worksheet. They do exist.


Range("L1") = Application.WorksheetFunction.VLookup(Sheets("Dashboard").Range("K1"), Sheets("Validation").Range("B2:C13"), 2, False)

Any ideas appreciated.

[offthelip]

It works fine in my version of Excel (2007) on windows 7

[Paul_Hossler]

Works fine using Win10 and Office 365

I did add the "Sheets("Dashboard")." since otherwise it uses L1 on whatever sheet is active




Option Explicit

Sub test()
Sheets("Dashboard").Range("L1") = Application.WorksheetFunction.VLookup(Sheets("Dashboard").Range("K1"), Sheets("Validation").Range("B2:C13"), 2, False)
End Sub





Make sure that K1 and B2:B13 are the same type and you're not mixing String and Long

[DeanP]

Thank you for all your help. I still have the same problem, so it must have to do with string and long? The lookup value and the result value are formatted as 'general' in my worksheet. I'm guessing I have to declare a variable, but not sure what/how?

[Paul_Hossler]

Thank you for all your help. I still have the same problem, so it must have to do with string and long? The lookup value and the result value are formatted as 'general' in my worksheet. I'm guessing I have to declare a variable, but not sure what/how?

Possibly, but it'd be easier to see if you could attach a small piece of the troublesome workbook -- just enough data and macro to demo the issue

[DeanP]

I decided not to use a pivot table at all. Thanks again for your help.

[RosieDaniela]

Thanks for the help as well