Searching utilizing "And" and "Or"

[Tills13]

This isn't a "how do I code it" so much as a "how do I go about it" kind of thing. I just need some more brains to help me wrap my head around what I'm going to be writing for the company i'm working for this summer.

Let's begin. What am I trying to do: Well, last summer, I wrote a company-wide database for Job Order sheets using Excel. I spent a few weeks writing the backend and building up a basic entry/saving system, but couldn't finish the search function due to school starting up again. Fast forward to now. They've asked me to complete the search function, but they have a very strict set of criteria that they want.

It must: Search for up to three things at the same time. Be able to perform multiple criteria searches using "And"s and "Or"s. Be quick. And the usual, you know, actually find stuff and return it.

In any case, I wrote the code for the userform yesterday. A nice fancy expanding solution that dazzled the bossman. The "and" and "or" options are radio buttons situated underneath the top and middle search field, so you can have Search1 AND Search2 Or Search3, and all the various combinations of one or the other.

Here's where I'm stuck. Without a giant "If" ladder, which is where I'm headed now, is there any easier way to do this? I jotted some psuedo-code down on a piece of paper, and I couldn't figure out a way around having redundant, duplicate code.

The issue is that I have multiple pathways that are going to be utilizing the same code. You can have one search term, which is easy, or you can have two search terms, which is either an addition to the first criteria or a separate case, but ultimately, you're executing the same block of code that you would with only one term.

 

[imav]

Could you post come code so we can see the what you are working towards?

- imav

 

[Tills13]

Could you post come code so we can see the what you are working towards?

- imav

I've only written a basic search sofar, with only one term and no "and"/"or" functionality, to show off the potential of the sheet in a demo.

Here's what I have:

Private Sub SearchInit_Click()
Application.ScreenUpdating = False
For a = 2 To Count - 1
If InStr(LCase(Sheets("Database").Cells(a, InCol).Value), LCase(SearchField.Value)) > 0 Then
Sheets("Data Analysis").Cells(Returns, 1).Value = Sheets("Database").Cells(a, 1).Value
Sheets("Data Analysis").Cells(Returns, 2).Value = Sheets("Database").Cells(a, 2).Value
Sheets("Data Analysis").Cells(Returns, 7).Value = Sheets("Database").Cells(a, 11).Value
Sheets("Data Analysis").Cells(Returns, 12).Value = Sheets("Database").Cells(a, 10).Value
Sheets("Data Analysis").Cells(Returns, 15).Value = Sheets("Database").Cells(a, 8).Value
Sheets("Data Analysis").Cells(Returns, 17).Value = Sheets("Database").Cells(a, 18).Value
Sheets("Data Analysis").Cells(Returns, 19).Value = Sheets("Database").Cells(a, 17).Value
Sheets("Data Analysis").Cells(Returns, 21).Value = Sheets("Database").Cells(a, 77).Value
Returns = Returns + 1
End If
Next a
Application.ScreenUpdating = True
Unload Search
End Sub

This is the code that performs the populating and the searching. It pulls from a "Database" sheet that's hidden from the user using "InCol" as a way to differentiate between the different criteria options (search by PO#, Lease, Plant, etc.) Those are assigned when you change the value of the combo box:

Private Sub SearchOptions_Change()
With SearchOptions
If .Value = "Customer" Then InCol = 2
If .Value = "Purchase Order" Then InCol = 11
If .Value = "Lease" Then InCol = 10
If .Value = "PM" Then InCol = 18
If .Value = "Date Required" Then InCol = 17
If .Value = "Plant" Then InCol = 8
End With
End Sub