Multiple search parameter, mult inputbox?

[RobLmrcpvf]

Hi,
I'm new to vba, and need some advice or clear help on how to do this. Due to enduser preference, knowledge, and software installed on our machines, I'm using Excel 2007 to do the following. I have a worksheet I am trying to make user friendly that enables the enduser to essentially search and identify material as well as create pricing after an analytical choice is made. Adjacent to material id info fields, are 2 fields per each warehouse that identify first, Available balance, and secondly identifies the unit Cost of that item at that warehouse. Beyond that are fields in which the enduser will enter cost and markup information that they choose, and there is also a field with a live formula to build the resale based on those entries.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
The worksheet contains identifiable data such as attributes as well as complete id info such as part numbers and description from columns A-F. The inventory balances and their associated cost continue from column G-AF. Columns AG-AK are the columns that will allow entries by the enduser based on the information they analyze from the preceding columns.
<o> </o>
Essentially what I would want to try next is to build a VBA search feature that allows the enduser to enter multiple search attributes, and then find the first best match with the ability to find the next match, the next match after that and so on. It would be similar to the built in Excel Find function, but we are looking for multiple boxes to enter in multiple attributes. Auto-filter is just simply not enough. We will be using the filter in addition as an option for some folks, but we need a built in feature that is user friendly. I'm dealing with people that do not know Excel or Access nor do they care to learn it. Moreover, the multiple search boxes, are similar to what they use now on our system. So we are trying to simulate something close to what they already know and are comfortable using.
<o> </o>
I'm not sure where to even start on this. I've read about inputboxes and sort of felt like that might be the right direction, but I don't know how to build them with the proper coding. In addition, I'm not sure if we need more attribute fields in the table, as to serving a better functionality with these multiple search boxes. I do know that current searches in our system include attributes as I have mentioned. They have several fields in the search function, and if they were looking for a
2" 150# widget class A carbon steel, in 3 or so fields they would enter 2" in one, 150# in the next, and perhaps carbon steel in the last. They have up to 5 fields to enter attributes into, so that is what I am looking to mimic. The system search also "returns" the matches, however, I'm looking to find the first match in an open table of data, then find next and so on. It is because of the user needing to enter data in AG-AK that I draw my concern on how we search and arrive at the matches. We are using this worksheet to easily locate material, balance, cost, and then to create pricing to upload into the system based on chosen cost/balance data. The whole idea is to consolidate what is already a cumbersome process in our system of multiple screens by single item data search, and then to use the worksheet to provide easy to use similar functions that require little training in Excel. Perhaps these are conflicting ideas, and perhaps the return of matching data is the best route. These are the things I need feedback on as well as any coding that will help us achieve what we are trying to do.
<o> </o>
Thanks,
Rob

 

[ChrisM]

Input boxes are probably not ideal in this case, they pop up one at a time and lock up the screen. Would be kind of weird to go through five in a row, plus they may want to revisit one.

Sounds like you need a userform with five textboxes where the user can enter their search values. Then a command button they can click when ready to search.

Use VBA to search the worksheet for the values, then return all the matches to a second worksheet.

 

[RobLmrcpvf]

Ok thanks, thats a start. I guess I am at loss how to build that code. I can get the userform up and running with the command button and text boxes. However, I need to please have an example of a code that would use the data in the 5 boxes to search the worksheet or range.

That definitely helps me understand, and I had actually created such a user form like you had mentioned. I just need some help to get the right code to make it functionable.

Thanks,
R

 

[ChrisM]

Well, the true gurus would probably tell you to use autofilter still. You can set the criteria in VBA, filter, then copy and paste that filtered range to a new worksheet and show that to the user, for example.

If you don't have a lot of rows of data, say no more than 2 or 3 thousand, you can do it the worst way, by looping through your data range and using five nested If...Then statements to test each row for each of your five criteria.

A faster way of this second method is to copy your data range into an Array, and then loop through the array in a similar manner. Looping through arrays is much faster than looping through a worksheet.

Sample looping code might look something like this:

dim i as long

for i = 1 to 1000
    With sheets("DataSheet")
              If .cells(i,1) = Textbox1.Text Then
                     If .cells(i,2) = Textbox2.Text then
                             'write these results to your 'answer' sheet
                     end if
              end if
    End With
Next i