Trying to save rows to an array to fill a listbox

I would like to find rows (in my spreadsheet table) that pass my If statement
Add each of those rows that pass, to an array
and use that array to fill a listbox (Each row on a new line/index)

I can't seem to quite get everything to fit together between the "Type Mismatch" and the "Subscript out of range" errors. I suspect the problem is somewhere in the array declarations but as I mentioned, I've been unable to find a successful combination but another issue may involve my attempting to display multiple cells as each Listbox item.

I've tried both reading the row cell by cell (which I've currently commented out)
and reading the entire row (which may NOT work since I expect it will squish everything together and make it unreadable when displayed as a listbox choice).

The latest version of the subroutine included below. My hope is that someone here can point me in the right direction. As ALWAYS, guidance would be greatly appreciated.

Please Login or Register  to view this content.

I did not look at all your code, nor did I test run it, however, which line is throwing these errors?

Type Mismatch

Subscript out of range

keep in mind that you CANNOT throw entire rows into array elements, 1 dimensional or 2 dimensional. not that I know of anyway. however, you can do other things:

1) concat all the cell data from the entire row into 1 string and throw it to arrays.
2) throw each cell's contents into different array dimensions. but this would become seriously complex.

A copy of your worksheet would be a great help.
Help us to help you.
See big yellow banner at the head of the page.
torachan.

Small amount of brain fog today, then suddenly realised I had seen something familiar recently
If the app is still similar i.e.(your form appears on the blank sheet that has your launch button).
This would be your active page and not containing any data that your are searching, hence subscript out of range.
Rather than trying to construct another array think laterally, you have a successful array, remove from it those rows that do not meet the criteria.
You could also use a filter on the table and populate the listbox with the visible rows from the table.
With an up to date workbook to work with an example can be given.
torachan.

YES! It should look very familiar to you. I made myself a test copy of your code so that I could experiment with it and try to learn how it works. I was able to separate out the "m1:" or "m2:" etc. from the wives first names into a new textbox but collecting the data and adding it into a new listbox has stumped me. I've tried a few different approaches and the search for "sons" nearly works except that each field is on a different line in the listbox instead of all fields for the match on the same line as you did in lbo1. I considered attempting to contact you directly but decided that you'd already been more than generous with your time and I shouldn't bother you.

I've been selecting the first name, Peter, to test with because he had both brothers and sons to try to gather for my display. Oh... on a similar note, I could never find where the array Nary was declared.

Thanks Adam I was only aiming to grab the first 6 columns of the row. As for the errors, I kept changing the lines that had errors in a blind effort to resolve them. Torachan is familiar with the original code that I was attempting to follow but obviously did not understand well enough to duplicate the process.

Well saying that someone doesn't understand your work well enough to duplicate the process isn't a nice thing to say but that's not a problem. So what exactly are you asking? Are you asking for my help or his or both? Or rather, do you even need more assistance at this point?

Adam, I'm very sorry. It was never my intention to offend anyone. I'm just interested in learning how to combine the use of controls with tables. Please feel free to disregard this problem.

Well my last post didn't mean the same thing and I'm sure anybody welcomes you to ask any question you want on this board. Including me and I'll try to answer anything I can. But if you don't want my help further that's fine with me too but you can continue to ask questions in this thread because I know there are many other professionals looking at it besides me.

@Adam, you really must knock the chip off your shoulder.
re-read post #6, you have completely read out of context @imaquila was inferring she did not understand NOT that you did not understand.
Maybe an apology for your brusque reply would not go amiss.
torachan.

@imaquila, I see there has been no solution posted.
If you can explain the actual breakdown of the 'branch' code so that I fully understand it.
I will come back with a solution.
torachan.

Thanks so much for your time!

"A" represents the earliest known ancestor in a family branch and each additional character represents a generation descended from "A". So the first character added to "A" represents a child of "A" ie. A1, A2, and A3
If "A2" were to have children, they would be identified as A21, A22 etc. So a Branch ID of A3112 is a means to trace the direct parental line of the "2" (on the far right) starting with their father "1" (to the left) who's father in turn was "1" (of the previous generation), who's father was "3", who's father was "A".

Your interest is appreciated!

Tested my one old brain cell to the limit.
I think I finally grasped the permutations.
I have used filtering to a hidden sheet, then used that to populate the second listbox rather than building the array, it was simpler.
The only oddity that I gave up on was the group of brothers also includes the search subject person.
torachan.

torchan, Thank you very much for your assistance. I've been looking it over, and I think I'm going to have to accept that I will not going be able to understand exactly how you worked this magic using tables. Maybe someday, I'll come across a book or article about working with tables but until then, I'll content myself with the knowing that Tables are useful for MUCH more than just sorting!

Thanks so MUCH for your help.

You are welcome.
Below a couple of links that prove very useful.

https://www.thespreadsheetguru.com/b...t-excel-tables

https://www.snb-vba.eu/VBA_Autofilter_en.html

These look to be GREAT sources of information! Thank you so MUCH for taking the time to share them! I'm looking forward to studying these pages and see what I can absorb from them!

I wish I could give you more stars!!!