Making VLOOKUP return a value into a cell in vba

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.



  • Hi guys attached above is my code i am trying to conduct a vlookup in vba however im unable to get excel to return the value into the specific cells namely H2,H3,H4. H1 is the value that im searching for. Im also trying to make it search through a list of items hence if the value in cell H1 is not in the list a msgbox appears and upon pressing ok or cancel it returns to the mainpage. Do let me know if theres any errors awaiting your reply and thank you in advance!

    Edited 2 times, last by royUK: Added code tags ().

  • Can you describe the logic of what you are trying to do, in detail, here? Trying to work out your aims by trying to decipher incorrect code is not so easy.

    Cheers, Glenn.


    Beauty is in the eye of the beer-holder.

  • A fragment of your code.


    Code
    ID = Range("H1")
    name = Range("H2")
    eMail = Range("H3")
    Department = Range("H4")
    name = Application.WorksheetFunction.VLookup(ID, Sheet1.Range("A2:D1002"), 2, False)
    eMail = Application.WorksheetFunction.VLookup(ID, Sheet1.Range("A2:D1002"), 3, False)
    Department = Application.WorksheetFunction.VLookup(ID, Sheet1.Range("A2:D1002"), 4, False)

    ID is undeclared, so the 1st statement would assign the value (.Value property) of cell H1 in the active worksheet to the Variant variable ID. The 2nd statement would assign the value of cell H2 to the string variable name. And similarly for eMail and Department. If you want cells H2:H4 replaced by the values from the VLOOKUP calls, you should replace the 2nd and subsequent lines above with something like



    It looks like there are other problems too, such as the For loop possibly iterating over many rows but performing the VLOOKUP call on the same ID value every iteration.

  • Can you describe the logic of what you are trying to do, in detail, here? Trying to work out your aims by trying to decipher incorrect code is not so easy.

    Hi glenn i would like to conduct a search of the persons user ID for example if i were to key in 12345678 , cells H2 , H3 and H4 would have their name , email and their departments placed in respectively. When a vlookup places an invalid value , i would like a msgbox to appear, I am aware of how to do this on the sheet itself by using formulas however have no clue how to proceed via the usage of a vba code do advise me! Attached is the file if you would like to work on it thanks so much!

  • . Do not use Error Handlers until the code is finished and tested. If you do you are hiding error messages which might be usefule.


    2. If you use VBA then use the VBA .Find Function


    Range.Find method (Excel)

  • Why would you want to use VBA? Using inbuilt formulas is the most efficient way.


    I have added code tags in your first post. When posting code on the Forum please use them to make reading and copying code easier.


    Code Tags

  • Hello,


    Attached is your test as a starting point ...


    Hope this will help



    Hi carim may i know what does the res in dim ID , res mean?

  • Hi carim may i know what does the res in dim ID , res mean?


    Hello,


    Thanks for your Thanks :)


    res is a variable which stands for result ...


    It is used to get the row number thanks to Application.Match


    Hope this clarifies

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Have you solved your problem ...


    Or


    Are you still facing an obstacle ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Glad you could fix your problem


    Thanks for your Thanks AND for the Like :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • This example demonstrates using .Find which I would always recommend using in VBA.


    To make it less likely that a missing or wrong ID is entered in the sheet I have used a Data Validation list in H1.


    In my example I have converted the data into a Table, this makes it easier to create a Named Range for the list that will expand as data is added. It also helps in the VBA.


    Onece the ID is found it simply copies the 3 adjacent cells and pastes using Transpose to H2.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!