Find, FindALL, VBA

  • Hello, I'm looking to code a sub that will find values in a sheet based on the part number and vendor number.
    Part number = x
    Vendor number = y
    Result = z


    When I click a command button the sub will:


    1. search for variable X on another sheet in column A
    2. once value is found in column A, the corresponding row in column B will be checked to see if it matches Y.
    3. If it matches then store the result in z. If it doesn't match find the next match in A and repeat the check to see if B matches Y.
    4. Loop until something is found that matches X and Y. If nothing is found then input "no results" into z.


    Any suggestions?


    Thanks!

  • Re: Find, FindALL, VBA


    Quote from skywriter;767774

    What's z?


    The next column over, same row?


    An array?



    z is going to be a string that contains the returned value from the find. Column A.


    Thanks!

  • Re: Find, FindALL, VBA


    Let's try it again.


    We are searching in column A for a part number, if we find it we look into the next column for a match to the vendor number, if we find it, bingo we have a jackpot.


    Now again, what do you want to return?


    What is in the string?


    The word, "yes it was found", the address of the cell in column B that matches the vendor number?


    What do you want to return?

  • Re: Find, FindALL, VBA



    Now that I think about it, let's return the value in columns A and B. The reason we're doing this is because the same part falls under multiple vendors and I'm searching for a match with one specific vendor. :) thank you.

  • Re: Find, FindALL, VBA


    Here's the code and there's a sample workbook included.

  • Re: Find, FindALL, VBA


    Okay so I tried to loop it so I can run the script on a list of part number and it's only working for the first entry and then stops. I must need to add or delete a line of code here.



  • Re: Find, FindALL, VBA


    Okay, here goes. I had to remove some tabs and confidential info. Column A is the search criteria. Clicking commandbutton9 executes the scripts...this is just a test to make sure the scripts work. I'm going to use them elsewhere in the sheet. It is also drawing the vendor number from K11. Some of the buttons will error because of the tabs I removed.


    Thank you!

  • Re: Find, FindALL, VBA


    Let me know if you have any issues or need any other help with this workbook. :cool:

  • Re: Find, FindALL, VBA


    I'm not getting that on the sample data and I can't read that image.


    When I zoom into it, it just gets blurry.


    Why not just post the code so I can see if you changed something that would cause that?

  • Re: Find, FindALL, VBA


    Ah sorry, I don't recall changing anything but you never know.


  • Re: Find, FindALL, VBA


    Try this version. :cool:


Participate now!

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