Search workbook for entered value between two cell values and enter results into UserForm

    • New

    Good morning all,

    Hopefully someone with a lot more brains than me can help. I have managed to 'cobble' together a few automated workbooks which satisfy my needs in the past (pasting open source code together until I achieve my aim). However as I am no VBA coder I am at a loss with this one. I have quite a complicated worksheet (although I have simplified it for this question).

    Basically the user presses a button which opens a search box. The user enters a search value, and I would like this value to be checked against two cells (number Min and Number Max) Columns B & C on my attached workbook. If the users enter value >= to the Number Min AND <= Number Max, then I would like to return the Unique ID (Column A) into my UserForm in the TxtReturnValues box. If there are multiple returns I would like them listed under each other in the TxtReturnValues box.

    Keeping my fingers crossed someone can help (If you can understand what I am after ;)

    • New

    Unfortunately, while simplifying the workbook is usually a good thing, not so in this case. To code for searches and the like the structure of the sheet usually needs to be known...


    For that reason alone, the attached workbook has a very simple loop/compare 'search'. It just loops through the used range in Col A, compares the values in Cols B & C. If a match is found the ID is added to a variables called 'Results'.


    At the end of the loop the textbox is populated with Results (or a 'Nothing found' message).


    It's inefficient but easy to understand. If you want something that works with your actual workbook then upload a copy complete with sample data (About the same number of rows as the original workbook).


    Some other UI changes have also been made including adding a Listbox which is a better control to use for something like search results. you can 'select' (or highlight) individual items and also (by selecting a checkbox) scroll the worksheet to the selected item - added to to show how it could be used, not saying it must be used ...


    The worksheet now has 10,000 rows to test speed of operation, in case you're wondering.

    • New

    Hi Cytop,

    Firstly many thanks for your speedy response. This is the first time I have ever requested help from a forum (and I'm really old!!!). What you have completed looks very much what I'm after. I will have a play with it tonight (working nightshift) and if I have any issues I will upload the full sheets as requested..

    Once again many thanks. :)

Participate now!

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

Register Yourself Login