Consulting

Results 1 to 6 of 6

Thread: UNable to get vlookup property of worksheet function class

  1. #1
    VBAX Regular
    Joined
    Nov 2018
    Location
    London, U.K.
    Posts
    85
    Location

    UNable to get vlookup property of worksheet function class

    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.

  2. #2
    VBAX Expert
    Joined
    May 2016
    Posts
    597
    Location
    It works fine in my version of Excel (2007) on windows 7

  3. #3
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,391
    Location
    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
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Regular
    Joined
    Nov 2018
    Location
    London, U.K.
    Posts
    85
    Location
    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?

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,391
    Location
    Quote Originally Posted by DeanP View Post
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    VBAX Regular
    Joined
    Nov 2018
    Location
    London, U.K.
    Posts
    85
    Location
    I decided not to use a pivot table at all. Thanks again for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •