Search an Excel Workbook 4 Multiple Criteria

October 7, 2011 at 13:05:22
Specs: Windows XP
I am looking for how to write a macro for a search of up to 11 different cells that would contain criteria of what the search would return for results. Each portion of the criteria would refine the data rows that would be returned. I have the Workbook setup with multiple tabs that indicate the first criteria that would be refined and probably the only required field I would need to have inputted for sure. After that the other 10 fields refine the data pulled from that tab. Each cell that would have a value to be search would reference a column in the worksheet. If the search finds that keyword/value it would return that whole row of data back to a row under the criteria input fields on the first worksheet.

I can try to post a copy of the workbook I am trying to write this macro for if needed, but not sure how to do that just yet.

See More: Search an Excel Workbook 4 Multiple Criteria

Report •

October 7, 2011 at 18:15:42
Here's where you lost me...

"Each cell that would have a value to be search would reference a column in the worksheet. If the search finds that keyword/value it would return that whole row of data"

First you say that the search will reference "a column" then you say you want to return "that row".

What row?

I think you need to provide a little more detail and perhaps an example.

If you click on the blue line at the end of this post, you'll find instructions on how to post data in this forum.

Perhaps if posted a sample of your data and an example of a search string and the desired result we could be of some assistance.

BTW...when you say "I am looking for how to write a macro..." does that mean that you know VBA and just need some help getting started or are you looking for a compete set of code that will do what you need?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

October 7, 2011 at 18:57:42
Thanks Derby for the quick response.

I know a little VBA, and I know the basics to get around. I am not that familiar with the longer code required to program a search like this though.

My data is split among many sheets in the workbook. Each sheet represents a location. In each location there is a list of inventory with multiple criteria to be able to search that inventory. Lets say it would look like this:

Sheet is called WHSE1
There are rows of data similar to this:
Part# Description Category Cost Vendor Account#

On the first tab of the workbook I want to have a search list setup to search all these fields with the Warehouse being a required field as the first protocol for the search (so the search knows what tab to start looking for the criteria). This way if someone puts in .05 in cost and Clown in Category i want it to return all rows of data in warehouse 1 that are clowns with a value of $.05. Each tab or warehouse will have hundreds or thousands of lines of data, so we are trying to find a faster way to bring data back to the first tab with a search instead of using filtering criteria on every tab when needed.

If possible it would be great to put in the cost to be a range, but not sure how the input would need to be for that or how it would work.

Report •

October 8, 2011 at 02:53:10
First off, as soon as I see "many sheets" with "hundreds or thousands of lines of data" I begin to think that a database program would probably be better than an Excel workbook.

Second, when I see "I know a little VBA" I lean even further towards a database program that already has these search features built in, along with many other features to help you keep track of your data.

Asking for code in a free help forum such as this may work out at the beginning, but if you can't maintain the code or adapt it to changes in your business model, then that could have an impact on your bottom line.

Finally, when I see this type of request, I am often only seeing the tip of the iceberg. "OK, thanks that does what I asked for. Now can you add this or make it do that?" In fact, in your first post you asked for a code that searched for data, but in your second post you asked that the code search for data within a cost range - an additional requirement was added before we even got started.

Added requirements can sometimes mean nothing more than an extra line or 2 of code. However, sometimes the additional requirements mean that the code should be entirely rewritten to avoid ending up with inefficient code with all sorts of bolt-ons that make the code hard to understand and maintain.

All that said, I'd be willing to give it a shot, but I can't get involved in a long term "add this, change that" type of situation.

Are the users entering the data manually or using drop downs?

I would think that drop downs would be the best way to go since that would eliminate data entry errors and ensure that they only entered data that would actually be found. I'm not saying that there must be $.05 Clowns in inventory at all times, but we don't want the users entering $.05 Clownz and thinking that there are no $.05 Clowns because they misspelled Clowns.

Finally, I would need Column and Row information before I could offer anything that we might expect to work - unless you just want some "generic" code that would show the process of how to choose the correct worksheet, then filter the criteria down so that the correct rows are returned.

One last question: Let's say the user searches for all data related to $.05 Clowns in Warehouse 1 and the data is returned Then the user searches for all data related $1.00 Elephants in Warehouse 3, should all the data related to the $.05 Clowns be cleared from the results sheet?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question