How to gather multiple values from UserForm to locate any matches in spreadsheet

I'm attempting to build a routine that will compare values from a UserForm with multiple columns of a spreadsheet and return all rows that match all of those values which were not blank.

I'm not entirely certain how to go about it but I've started with a Search Subroutine and created a couple arrays

Please Login or Register  to view this content.

I've stored the values for any NON-Empty fields into both Fields() and Col()

My hope was to cycle through each row in my spreadsheet, comparing the values stored in the Fields() array with the corresponding Col()array to indicate which column in the row to compare.
Unfortunately some Fields are often unknown and as a result, have no value entered, so they are skipped as is the Col value.
I'm having difficulty, visualizing how to handle those inevitable gaps.

If only Fields(0), Fields(3) and Fields (5) have values stored and the same for Col(), how can I later, specifically call up those parts of the array for comparison to my spreadsheet.

Any assistance would be welcomed and appreciated.

I've been trying to add an attachment but once I've selected it on my computer, I'm asked to choose from Saving my own file or Opening my own file. I've tried both and neither uploads the file that I can see listed, to this website. Either a step in the instructions is missing or there's a glitch in the process.

I guess the file transferred even though I had no indication at my end that it worked.

I should explain that I got tired of typing in the search parameters over and over again so I used the Value property for my textboxes to save myself time when testing. Unfortunately now, when I Run the UserForm from the Debugger, the UserForm loads and unloads before I can click the Search button to activate my Sub cmdSearch_Click() where most of my code resides.

I didn't understand about the BEFORE and After sheets but basically the current Search parameters Match a record in my datasheet and my goal is to have that and any other matches display in my SearchResults sheet.

I'd be grateful for any guidance that you can share.

Give this alternative approach a go.
The six upper comboboxes do the initial filter/search loading the listbox with a small number of matches.
Selecting in the listbox displays all the relative fields on the form.
torachan.

That is VERY Elegant solution! Thank you so MUCH! This is far superior to what I hoped for. Two questions for you regarding this format...
Is there a way to sort the listbox values in order to make them easier to find?
Is it possible to add a field to the listbox display, that is NOT a search field? When a match is found and displayed, I'd like to include the value in Column B which identifies the branch of the family for that individual.
THANK YOU SO VERY MUCH!!

mehmetcik, Thank you so much for all your time, effort and especially for the helpful code examples. I must admit that most of it remains a bit "over my head" but I'm hopeful that if I study it a while longer, anything there that I can understand, will be an enormous help down the road.

I noticed that you added a new column to the spreadsheet "Wife?". I thought I should explain that the only reason that I used a checkbox on my UserForm, to specifically identify wives, was so that when comparing the text entered in as a wife's first name to search for, it would be compared to the wife's first name WITHOUT the prefix of "m#: " that distinguishes first wives from second wives and etc.

The attached now has alpha-numeric sorted lists on comboboxes and listbox.
The comboboxes are populated by cascading the sequence of data from the table, the table columns have to be contiguous.
The extra textbox is populated from column "O", you can alter that if you wish without affecting the filtering.
torachan.

torachan, This is AMAZING and VERY helpful (even if the search RESULTS does not yet return the Family Branch that the individual descends from)! I will study your code and attempt to imitate how you directed the spreadsheet data to the extra textbox which is now populated by any notes and do the same for the family branch ID for the individual located. In the meantime, THANK YOU SO VERY MUCH for all the generous time, energy and wisdom that you've put into this project!

Look for the code below and change the "14" (Me.t15.Text = .List(sc, 14))to which column you require.
Bearing in mind listbox references are zero based so sheet column 15 is listbox column 14.

Please Login or Register  to view this content.

Happy to report that it did NOT take me long to figure out how to add my Family Branch ID to information returned with matches. Thanks again for all your assistance!