I've previously described how to use a form to search for a distinct value, like a unique ID.
You don't have to use a table, you can just as easily search a range.
Find/FindNext allow you to specify a text string and they will find all occurrences. So if you search for "Boo" that will match "Booth", "Boothroyd", "Booker" etc. In fact it will match any string with "boo" anywhere in it, like "Naboo".
The way that Find/FindNext work is to search through a given range until the end of that range is reached. At this point, the search 'wraps around' to the start of the range and continues searching.
So to stop an infinite search loop the code must store the address of the first matching cell and then test each subsequent cell until it gets back to the first cell again.
The Source Data
I've created some dummy data in a table called Table1.
Using a userform, I want to be able to search for someone by either their first name, last name, location or department.
I'll be returning the results, the entire matching row, into a ListBox in the userform.
You can extend the capability of the form by allowing it to modify or delete the records from the table. But for the purposes of this example, I'm just writing the search components.
The Search Userform
A simple form design with four text boxes where you can enter what you want to search for.
Results are listed in the central ListBox and the three buttons do exactly what they say they will.
To start with, because I am restricting the search to only one thing at a time - you can't search for both first name and last name - when you enter something into one text box, all the others are cleared.
If you don't specify anything to search for, you'll get this friendly message.
After entering your search term, any matching records are listed.
If nothing matches, the form tells you so.
This is a standalone userform and code that can be adapted or inserted into your own project. Let me know via the forum if you have any questions or issues using the sample workbook that you can download at the top of this post.