VBA Search for Multiple Criteria

Copper Contributor

Hello,

 

Need assistance in understanding and formulating a VBA code for multiple criteria search. I currently have a VBA Userform that searches for individual criteria and once I enter a date (currently Textbox14), click update, that search order is closed and is removed from the DashBoard worksheet. I also have Cmd Buttons of Search, Refresh, Update, Close on the Userform. Everything works perfectly. 

 

What I want to do is expand the search for two criteria = Search Textbox1 (Date Completed, column 10) and Textbox 2 (Vendor, column 5) 

 

The search will display the orders that match.   

 

Display  

 

Order Number

 

Textbox3

Textbox4

Textbox5

etc.

 

Tried searching here and YouTube but cannot find any tutorials for this

.

Thank you,

 

Roy

 

11 Replies

@RoyJr 

 

Since it is working for one criteria, then it sounds like you just need to find a way to reference a cell on the same row, but different column (I'm assuming you are searching a worksheet or structured table)?

 

If that is the case, then you might look at the Intersect function, which will return the intersection of a row and column range (the vba code is in Sheet1 code module).

 

Intersect(foundCell.entirerow, Worksheet.Range("A:A")) 

Intersect(foundCell.entirerow, cell.entirecolumn) 

 

Then you should be  able to check the value of the intersection for your second criteria. I added a workbook with an example of one way you can search a worksheet range and also match criteria in another column. If it doesn't help, then you may need to add some more details regarding what you're trying to do (perhaps upload a workbook w/ sample data if it is not sensitive information).

 

 

@JMB17 

 

Unfortunately, I'm working on two computers. I cannot see the attached file because this computer does not have a full suite of Excel and the other is restricted for downloads.

 

Below is the VBA code I'm using and working perfectly. So instead of section CommandBox1 having only one search criteria, I want to have two and the ability to list up to ten orders (TextBox 2, 3, etc) that meet that criteria. Then when I add a date in TextBox14 and click the update button, those orders are updated.

 

Private Sub CommandButton1_Click()

Dim Current_Status As String

Currrent_Status=Trim(TextBox1.Text)

lastrow=Worksheets("Overall").Cells(Rows.Count, 1).End(x1Up).Row

 

For i =2 To lastrow

If Worksheets("Overall").Cells(i, 1).Value=Current_Status Then

TextBox2.Text=Worksheets("Overall").Cells(i, 3).Value

TextBox14.Text=Worksheets("Overall").Cells(i, 11).Value

 

End If

Next

 

End Sub

-----------------------------------------------------------

Private Sub CommandButton2_Click()

Dim Current_Status As String

Currrent_Status=Trim(TextBox1.Text)

lastrow=Worksheets("Overall").Cells(Rows.Count, 1).End(x1Up).Row

 

For i =2 To lastrow

If Worksheets("Overall").Cells(i, 1).Value=Current_Status Then

Worksheets("Overall").Cells(i, 3).Value=TextBox2.Text

Worksheets("Overall").Cells(i, 11).Value=TextBox14.Text

 

End If

Next

 

End Sub

 

Sorry for the long post but I hope it provides clarity to what I want to do.

 

Thank you,

 

Roy

 

@RoyJr 

 

If I understand correctly, then I think you will need to add an AND operator to your if statement to evaluate both conditions.

 

But, if you want to load multiple results into separate sets of text boxes, then I think you will need a counter variable to keep a running total of number of matches and then, based on that running total, load your next match in the next set of text  boxes set up to hold the results (the Select Case statement in the below example is set up for three).

 

However, if you think you might expand the number of matches in the future, you could use a list box with two columns to hold the matches. And, if you wanted to, you could add functions to update only matches you select from the listbox or all matches at once. I attached a screen shot as an example.

 

 

 

Private Sub CommandButton1_Click()
  Dim Current_Status As String
  dim count as long

  Current_Status=Trim(TextBox1.Text)
  lastrow=Worksheets("Overall").Cells(Rows.Count, 1).End(x1Up).Row

  For i =2 To lastrow
    With Worksheets("Overall")
      If .Cells(i, 1).Value=Current_Status And .Cells(i, 2).Value=CRITERIA2 Then
        count = count+1

        Select Case count
          Case 1
            TextBox2.Text = .Cells(i, 3).Value
            TextBox14.Text = .Cells(i, 11).Value
          Case 2
            ' Second set of textboxes here.
          Case 3
            ' Third Set of textboxes here, etc.
        End Select
      End If
    End With
  Next
End Sub

 

 

@JMB17 

 

The List Box looks to be the solution I'm searching for.

 

I will attempt to recreate your example.

 

Thank you. 

@RoyJr 

 

If it will help, here is the workbook I was playing with.

@JMB17 

 

Sorry for the delay in responding. Things are hectic. Getting close to finishing this project using your examples and code. Thanks for your assistance. 

 

I have recreated the Userform minus the graphics on the Search and Update buttons.

 

For the coding just need some clarification: Criteria 1 = TextBox1 (from column 6 of Overall worksheet). Therefore, in TextBox1 the criteria will be Completed / Yes or Completed / No or can I just type Completed with a wildcard in the code to recognize any text after Completed?  For Criteria 2 = Textbox2 (from column 5 of Overall worksheet) the criteria will be a vendor like ABC, XYZ, etc. 

 

Click Search and the Results will populate in the List Box?

 

I only need one TextBox in Update. So TextBox3 is the date (for column 11 of Overall worksheet) that will close the orders found from the criteria. Adjusted the UserForm for this.

 

When I run DeBug, getting error - End If without block if

 

Again, thank you for your assistance. 

 

 

 

@RoyJr 

 

You could add a wildcard option for Criteria 1. One way would be to add a checkbox to enable wildcards, which controls a module variable. Then, the search function could check that module variable value and either match the text exactly or compare the text using the Like operator. 

 

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/like-operator

 

I made the change to the example workbook and included comments where I made changes.  

 

The error you noted appears to be an "If/Then" statement that is missing an "End If." You'll need to check your If/Then blocks and make sure there is a matching End If. I'd have to see the code to be more helpful than that.

@JMB17 

 

OK. Still having issues even after using the code you provided above.

 

Attached is an example of my excel file. Here is the breakdown:

 

From Userform2 on Dashboard, I search for two criteria in TextBox 1 (from Overall column C or 3) and Textbox2 (from Overall column D or 4). Once found and populated in ListBox, I enter a Date in Userform2 Textbox3. Once the Update is clicked, the data will populate in Overall column E or 5.

 

>>>Just for info: The Overall Column C will change (from a formula on this worksheet) so this data will not be found again.

 

If there is a better please option, please let me know.

 

Thank you,

 

Roy     

Is it possible to include the user form in the workbook? I'm afraid I won't be able to do much without seeing the code.

@RoyJr 

Hey Smart Guy,

I am just new to VBA learning path. Some how i could able to create one user form with one textbox search and get the data into the listbox.

Now i have one English word power excel file. Where there will be 3 textbox. One by word search , another by synonyms search and another by antonyms search. the search can be either of any 3 textbox and the result can be display in listbox below with the words, synonyms , antonyms and many more columns say 3 more column.

The user form will will search, add, delete, or exit records.

Help of to get the understanding with the example through the blog post or youtube video.

@kumachir 

 

Smart Guy???

 

Do you need my help? If so, I cannot help you. I'm still trying to get an understanding of VBA as well.

 

Or do you have a VBA example code for what I require?