PDA

View Full Version : Userform Search help!



bpaddock
07-14-2010, 02:47 PM
Hello All,

I'm new to VBA so bare with me. I am trying to setup a userform that will allow the user to enter data and search my "Data" sheet and output the resulting rows into the "Results" sheet. The user form will have a combination of check boxes and txt boxes with one search button at the bottom. I want it to be able to search each criteria and narrow down the results if found, if nothing is entered in the box I just want it to skip over it and move on to the next search field.

What is the best way to approach this, there are so many different ways I've seen people do it. Will multiple .find's work for that many criteria? Or will something else work better. I really don't want to use a filter since that defeats the point of the userform.

Attached is the workbook containing the "Data" and "Results" sheet. I have already created the user form UserData. I have also added a Search button on the "Results" sheet to start the macro. The results should be copied into the 20th row of "Results".I want the first 4 check boxes to correspond to column D starting at row 12. I need the check boxes, if checked to search for a string with the same label beside it.

If someone can just help me out getting started, I know i can fill in the rest. I just don't know how to setup a multi search criteria and filter the results and output them to another sheet.

Attached is the Workbook in 2003 Excel format.

Thanks,

Brian

mikerickson
07-14-2010, 08:04 PM
...I really don't want to use a filter since that defeats the point of the userform...
I was going to suggest that you use AdvancedFilter. Its perfect for this (once the merged cells in the header row are fixed).

About "defeating the point of the userform". As I understand it, the purpose of the userform is to get the results.
A UF is a good way to allow a non-Excel savy user to set up a Criteria Range for Advanced Filter. I see no conflict between using a filter and a userform.

bpaddock
07-15-2010, 05:05 AM
mikerickson,

Thanks for replying. So in other words, I could use a userform to allow the user to input data the Advanced filter will use to sort which rows, columns etc. No searching commands needed, correct?

Also, how is Advanced filter setup, I'll be looking at tutorials and such, but how do i connect the userform to the Advanced filter?

Can you give me an example perhaps? Also, you mentioned the merged rows would be a problem, is that because each of the headers i wanted filtered will hold an equation or reference? What would you do to remedy this situation?

thanks,

Brian

mikerickson
07-16-2010, 07:04 AM
Here is an example.
I changed some of the Data so that there was more differnece between the kinds of materials. For testing, it helps to have some CO2 product that is not also H20 and vice versa.
I also added a named range. In practice this could be replaced with a dynamic formula.

Most importantly, I changed your column headers.
The goal was to get each column to have its own unique header. Also to remove the merged cells.

As written it interprets multiple checkboxes as AND. If both CO2 and H2O are checked it will show those lines that are both CO2 and H2O. If you want OR it will have to be rewritten.

mikerickson
07-16-2010, 07:42 AM
I forgot to add. The criteria range is deleted at the end of the routine. You might want to comment out that line.