Search Multiple Tables, display found cell row data in userform

[Icesurf3r]

Apologies in advance if this is a long post, but I'll try and include as much detail as possible.

Just in case it may make a difference to any of the code
What I need help with is:

• Coding the search function
• Coding the next/previous function of the search (each has its own button)

I have a Userform which will be used to capture incoming work opportunities. The work opportunities will be split over 5 worksheets, each with a corresponding table eg:

• Worksheet (Tracker)/ Table_Main
• Worksheet (Go)/ Table_Go
• Worksheet(No Go)/ Table_NoGo

Everything so far is working well. But I've hit a bit of a snag when building the search function.

The user has the option of searching by Client or Opportunity name - Selected using a combobox, which in turn populates a second combobox with the available search names using the code below:

Please Login or Register  to view this content.

This makes sure that no duplicate entries appear in the combobox.

Once they've selected the Client or Opportunity name from the second combobox they then click the search button - which is where my issues begin.

I need the search button to accomplish several different things, not necessarily in this order:

1. search through a specific column in all the tables on all the sheets (depending on whether client or opportunity name has been previously selected)
2. The code needs to identify on which sheet or in which table the matching entry was found
3. Display several cell values from the found items row in several text boxes on the userform

Number two is important because not all the tables have the columns in the same order (Which wasn't my idea but came from the powers that be.) For example, The Decision column in the main tracker is Column L, in the other four tables it's K. This affects which cells from the table row populate which textboxes on the Userform.

I did start to build the code (in it's very early form - but couldn't progress as I didn't know how to accomplish 2 and therefore 3)

Please Login or Register  to view this content.

I have included a version of the tracker with any confidential info removed, which will hopefully shed some light on the above.

Any help that anyone can give will be greatly appreciated, as I've been stuck on this for several days now.

[AlphaFrog]

For #2, you've already coded the answers

Please Login or Register  to view this content.

[Icesurf3r]

Thanks AlphaFrog,

Is there a way to identify which table the matching cell was found in (as in the name of the table itself, or worksheet if it's easier)and build a Select Case argument around that??

For example, although tbl is the matching table, if the matching cell is found in Table_Main the offsets will be different to if it's found in Table_Go.

Hope that makes sense?

[AlphaFrog]

Tbl.Name returns the matching table name as does ws.Name for the worksheet.

Please Login or Register  to view this content.

You can reference a table column by its header

Please Login or Register  to view this content.

You can get the intersect of the named column and the matched cell row.

Please Login or Register  to view this content.

Do a search for something like VBA Listobjects and you will find several sites with examples for code to reference and manipulate tables.

[Icesurf3r]

Thanks Alpha,

For anyone else with a similar question, I used a Select Case argument with tbl.Name as illustrated below:

Please Login or Register  to view this content.

Hopefully someone may find this of some use.

(Is there a way to mark this as solved?)