Vlookup in VBA returning 'Vlookup property of worksheet function class'

  • Hi, I am hoping I can get some help. I am completely self taught from books and the good people at Google, so if some of my terminology/code looks 'newbie' like, there's a reason.

    I have been writing a vba program in excel for a contract management system. I have a quite detailed userform that populates and then returns (upon searching In Column A for a textbox value) the rest of the textbox/combobox data from the excel list.

    It has worked perfectly over the past with few days over multiple tests of example data, now I have completed the program, and using the proper data, it is now returning the seemingly well known 'Unable to get the VLookup Property of the worksheet functionclass'

    There are 23 textboxes that the Vlookup is populating, and I am using a named range "Lookup" which I learned from a Youtube clip! Get me!

    I have cut the first bit of the sub below if you can help me find the error please. The 'Red' text is the yellow highlight in the debug.

    Honestly, it has been working fine all week with example data.

    Thank you in advance for your assistance. :)

  • Hi, me again.

    Sorry, managed to fix it, but I could do with some assistance on something else if possible.

    The fix - I had a set of quotation marks missing from the named range reference, stopping the lookup, it happened when I had to add the INDIRECT reference in to the OFFSET named range.

    The named range also is used with a listbox that displays a list of the contracts that match part of the input textbox. THis works great and populates the listbox properly etc, however, I want to be able to click the correct record in the listbox and this be the selected record that I want to populate TextBox1 in another userform with. So, question is, how do I take the selected item in a listbox and extract the first column data in this to populate a Textbox in another userform?

    I have found a couple of solutions to this online but none work.

  • You should have read the Forum Rules by now.

    Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.


    your code goes between these tags


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.


    There are more efficient ways of achieving this than using VLOOKUP, I have posted several solutions to similar problems on the Forum.

    Also, if this question is solved then ask your additional question in a new Thread.