Logic of multiple search criteria. Help Please!

[longbow75]

Hi,
I apologize if this has been explained before but I haven't seen it on the Site.I'd appreciate if someone could give me an example of combining multiple search options. What I'd like to do is on a userform I ask user to define search criteria such as Temperature, pressure, flow and chemical name. I had only one search filter before and that was the chemical name but it's huge and now I need to filter out more sheets based on temp, press and flow. For these three I have text boxes.
The problem is the probability of finding what user enters is slim to none. For instance user may enter 56.6 for temperature so I need to define the search such as <100 ; 100<x<200 and >200.
Same thing applies to pressure as well.

This is what I had for name search:

Public sh As Worksheet
Public sh1 As Worksheet

Dim chname as string  ' chemical name
chname = TextBox1.Text
For Each sh In Worksheets
 If chname = sh.Range("B3").Value Then
 
      Set sh1 = sh
      ListBox1.AddItem (sh1.Name)  ' add the sheet on which it finds the chemical 
            
   End If
  
Next

If it was a constant number I guess it would be easier to search but how do I go to search for < or > values?

By the way Temperature will be on A1 pressure on A2 and flow on A3 of each sheet.

My logic says that first search for chemical name as I do above then look what user entered for temp check A1 if it is less than 100 then look A1 if A1 is less than 100 then it is true if not forget about the first sheet you find go to the second one look for T and P and look what user entered for T and P and so forth.

Thus the sheets found must match 4 different criteria.

I think I should be using nested for next and if then but I haven't seen any examples and basically I'm stuck.

Any help or lead would be greatly appreciated!!!

[Apps]

Hi longbow,

On the assumption that the entries for the other variables are also in other textboxes, if you don't want to define the search variables up front (e.g. limit them to searching for something defined as '<100' or '100-200' or '200+') then you could use something quick like the below which searches for any results that are +/- 50% of their keyed search criteria..?

So keying '100' in pressure would return a match if the pressure found on the worksheet was anywhere between 50 and 150?

Not sure if this helps you any...?


[VBA]

Public sh As Worksheet
Public sh1 As Worksheet

Dim chname as string ' keyed chemical name
dim xTemp as integer ' keyed temperature
dim xPress as integer ' keyed pressure
dim xFlow as integer ' keyed flow

chname = TextBox1.Text
xTemp = val(TextBox2.Text) 'converts keyed strings to integers
xPress = val(TextBox3.Text) ' so we can play around with them
xFlow = val(TextBox4.Text)

For Each sh In Worksheets

'ensure that all search criteria is met by referencing cells on each sheet
If chname = sh.Range("B3").Value _
and sh.Range("$A$1")>=(xTemp-(xTemp/2)) and sh.Range("$A$1")<=(xTemp+(xTemp/2)) _
and sh.Range("$A$2")>=(xPress-(xPress/2)) and sh.Range("$A$2")<=(xPress+(xPress/2)) _

and sh.Range("$A$3")>=(xFlow-(xFlow/2)) and sh.Range("$A$3")<=(xFlow+(xFlow/2)) _

Then

Set sh1 = sh
ListBox1.AddItem (sh1.Name) ' add the sheet on which it finds the chemical

End If

Next

[/VBA]

[longbow75]

Hi Apps,
Thanks a lot for your solution!!
I guess I could do it as you pointed out. However, how would I make sure even if the user doesn't enter a value it skips that variable and still searches. I added or statement to the if then block but it doesn't seem to work.
Appreciate your help!