userform for multiple search functions

[simi_uk]

Hi all,

i'm trying to put together a UserForm that will allow me to search multiple sheets in the same workbook. I have the userform built, and currently it consists of multiple input/text boxes and a single search button.

Each textbox will, eventually, be assigned to a single column on the workbook, say for example that Column A is 'Part Number' and Column B is 'Description' (this will be set the same across the entire sheet).

My idea is that if i typed data into both input boxes, the search would check all sheets and report back (as per the Excel "Find All" function) any hits based on the information supplied in the multiple input strings.

does this make sense and, more importantly, is it possible?

 

[simi_uk]

**bump**

 

[simi_uk]

***2nd bump***

i'm assuming nobody can help??...anyone, please?

 

[mikerickson]

"Is it possible?" Yes

Is it easy?
That would depend on how similar the sheets are.

Do all the sheets have the same headers in the same columns starting in the same row?
or
Do all the sheets have the same headers, but it variable order?
or
Do some of the sheets lack some of the headers?

Are there any sheets to exclude from being searched?
Do you want the user to be able to control which sheets are to be searched?

How do you want the Found rows indicated?
By Going To them (like Excel's Find) or by listing them, allowing the user to choose which to GoTo.

Yes, it is possible.

(By the way, do you want the option of finding partial matches as well as whole cell matches.)

 

[simi_uk]

Hi Mik,
really, i have so much appreciation for your reply, i really didn't think it'd draw such a blank from what is, IMO, the most informative Excel & VBA forum on the web....

In reply to your numerous questions:

sheets have the same headers in the same columns starting in the same row?
or
Do all the sheets have the same headers, but it variable order?
or
Do some of the sheets lack some of the headers?

All Sheets are identical in make up, with all columns in order and under the same headers.

Are there any sheets to exclude from being searched?
Do you want the user to be able to control which sheets are to be searched?

No sheets should be excluded, and the user should NOT be able to choose those searched (hopefully that saves some work!)

How do you want the Found rows indicated?
By Going To them (like Excel's Find) or by listing them, allowing the user to choose which to GoTo.

Yes, it is possible.

(By the way, do you want the option of finding partial matches as well as whole cell matches.)

Listing the actual finds would really be helpful, allowing the data in the cells to be displayed so that the best match could be selected. As for partial matches, these again would be very much beneficial, as woul dthe use of wild-cards in the search.

Hopefully i'm not complicating it overly, and that it's not too much of an ask, but it'd really help me with my work!

 

[mikerickson]

One approach might be to take the user input from the userform to fill an AdvancedFilter criteria range which will be applied to each worksheet.

 

[simi_uk]

again, i really appreciated your input on this topic, so many thanks.

would you be able to suggest how i go about putting it together? I've had very limited experience of Excels functions.. i have used the Auto-Filter function previously, but only via the drop-down menu at the top, never in a VBA code.

Any further input will be grately appreciated.

 

[mikerickson]

You mentioned that you have the userform "built". I assume that means that you have the controls placed and formatted.

You may have put the cart before the horse. What kind of search are you trying to preform. Name-Smith and State-CA or Name-Smith or Jones and State-CA or AZ.

How many records (total) do you expect to return from each search? Is this to find one record or to find many many similar records?

All these questions should be answered before the userform is built so the appropriate controls can be used.
For example, if you want the capability to look for State=CA or State=AZ then you might want ComboBoxes (to selecte between Name,State,Status, etc) in addition to TextBoxes (to enter CA or AZ or..)

 

[simi_uk]

well, the userform is essentially a single input box and search button awaiting the VBA back-room code to function....

Ideally, if i searched "Battery" and hit the 'Search' command, the return would show me all the various batteries listed within my workbook. Fir the record, my workboox contains 26 sheets, A-Z and is, for want of a better word, a full inventory with part numbers and alternate part descriptors, for example:

Description: "Battery 'AA' type" Part Number: "1234-1234-1234" Alternate Name: "1.5V AA btry"

Description: "Battery 'AAA' type" Part Number: "1234-2234-3234" Alternate Name: "1.5V AAA btry"

Description: "Battery 'C' type" Part Number: "1244-3234-4244" Alternate Name: "1.5V C btry", "C cell"

Description: "Battery '9v' Part Number: "4544-1334-8234" Alternate Names: "9V btry", "Square btry"

So, what i'm looking for (using the above example as a reference) is to be able to search for multiple terms (in this case) "1.5V" and "battery" and for the search then to show me ALL 1.5V batteries as a match, but not the other batteries listed, i could then select the correct one as required to check stock levels, type, make etc. etc. etc.

am i making this too complicated??​

 

[simi_uk]

***bump***

assistance with the above would be grately appreciated.