Populate Listbox with all rows of a dynamic array where elements of a single array match.

I currently have a userform that, upon intialization, creates a public dynamic array based on a table range, test data currently 45 rows by 22 columns.

On the userform there is a textbox01 that allows a user to enter text, this text is then entered into an array base on single words, i.e. textbox01 sting = "Hello how are you" would populate the array with 4 single words.

What I am trying to do is to create a routine that populates listbox01 with only rows of the dynamic array where all element of the single array are present in the dyna,ic array

Example

Dynamic Array Row 15 = "One" "Two" "Three" "Four" "Five" "Six" "Seven" "Eight" "Nine" "Ten"

Sindle array contains "Two", "Five" and "Eight", resulting in the row 15 of the array being populated into listbox01.

At the moment my code only searches the first word entered into textbox01 which if match is match is made the listbox is populated, My Current Code is;

Please Login or Register  to view this content.

I reckon you oughta create a 1 dimensional array that joins each row of your SDVar array into one string separated by a delimiter, then you can just filter that array repeatedly for each of the four items in turn plus the delimiter (so filter the array for item1, then filter the filtered array for item2 and so on) then the resulting array will contain all the items that match everything. then loop that array splitting the text on the delimiter and add to the listbox.

you have to go row by row

---------- Post added at 05:53 AM ---------- Previous post was at 05:43 AM ----------

basic idea is this

Please Login or Register  to view this content.

note this assumes you are matching the words against columns-i.e. one word per cell.

as I mentioned in my post the code assumes one word per cell as in your initial post. since that is not the case you need

Please Login or Register  to view this content.

but depending on the search data you may get additional matches (eg one of your search terms matches part of a word such as searching for 'ford' and the data contains 'afford')

loop through v1DData and use additem on the listbox, then split each item in v1DData (using Split to reverse the Join) into a temporary array and populate the listbox using whichever columns you want.

I've converted the code to a function and added a userform to demonstrate

I apologize - there was an error in the code

Please Login or Register  to view this content.

I suggest you don't use the change event as it will trigger the code with every keystroke. note when entering the PK07 XBB you must have the space in the middle for it to match (the code will treat each part as a separate word but I figure the chances of both parts appearing separately in a different row are small?)

two options are to populate the initial array with only the columns you want (and in the order you want) or to loop through the array the function returns using AddItem instead of using List to put the whole array in at once. for the second option for instance

Please Login or Register  to view this content.

what is 'stockdata'?

how and where is that variable declared and initialized?

my bad-you need

Please Login or Register  to view this content.

as the list property is zero based. hope that works as I need to get some sleep now :-)