VBA for Matching Multiple Criteria

[Yuriy B]

Hi,

I have a UserForm that takes data from a table on "Raw Data" tab and inserts it into a different table on "All" tab.
Now things are getting complicated and I would appreciate some help/advice:

With Vlookup I can have 1 criteria, but I need to return values based on 2 criteria.

Column 1	Column 2	Column 3	Column 4	Column 5
Data 1	0	P/N #	P/N Name
Data 2	0	P/N #	P/N Name
Data 3	0	P/N #	P/N Name
Data 4	0	P/N #	P/N Name
Data 4	1			P/N #
Data 4	2			P/N #
Data 5	0	P/N #	P/N Name

<tbody>
</tbody>

In the UserForm Column 1 will be entered into TextBox4 and Column 2 into TextBox5.
Criteria: Look up Column 1 (from TextBox4) and Column 2 (TextBox5)
If Column 2 is 0 then display Column 3 in TextBox13 and display Column 4 in TextBox14
If Column 2 is greater than 0 then display Column 5 in TextBox13


The code that I am working with:

Private Sub TextBox4_Change()


Dim MyRange As Range
Set MyRange = Worksheets("Raw Data").Range("Table_Query_from_Visual654[[base_id]:[Column11]]")
On Error Resume Next
TextBox12.Value = Application.WorksheetFunction.VLookup(TextBox4, MyRange, 15, False)


End Sub


Private Sub TextBox5_Change()






End Sub




Private Sub ComboBox1_Change()


Dim MyRange As Range
Dim VLU As String
Set MyRange = Worksheets("Failure Codes").Range("B:C")
VLU = Application.WorksheetFunction.VLookup(ComboBox1, MyRange, 2, False)
On Error Resume Next
TextBox15.Value = VLU


End Sub


Private Sub ENTER_Click()


If TextBox1.Value = "" Or TextBox4.Value = "" Or TextBox5.Value = "" Or TextBox6.Value = "" Or TextBox7.Value = "" Or TextBox8.Value = "" Or TextBox9.Value = "" Or TextBox10.Value = "" Or TextBox11.Value = "" Or TextBox12.Value = "" Or TextBox13.Value = "" Or TextBox14.Value = "" Or TextBox15.Value = "" Or ComboBox1.Value = "" Or ComboBox2.Value = "" Or ComboBox3.Value = "" Then
    If MsgBox("Form is not complete. Do you want to continue?", vbQuestion + vbYesNo) <> vbYes Then
    Exit Sub
    End If
End If


Dim the_Sheet As Worksheet
Dim the_Table As ListObject
Dim Table_Object As ListRow
Set the_Sheet = Sheets("All")
Set the_Table = the_Sheet.ListObjects(1)
Set Table_Object = the_Table.ListRows.Add


Table_Object.Range(1, 1).Value = UCase(TextBox1.Value)
Table_Object.Range(1, 2).Value = UCase(TextBox4.Value)
Table_Object.Range(1, 3).Value = UCase(TextBox5.Value)
Table_Object.Range(1, 4).Value = UCase(TextBox6.Value)
Table_Object.Range(1, 11).Value = UCase(TextBox7.Value)
Table_Object.Range(1, 16).Value = UCase(TextBox8.Value)
Table_Object.Range(1, 17).Value = UCase(TextBox9.Value)
Table_Object.Range(1, 18).Value = UCase(TextBox10.Value)
Table_Object.Range(1, 19).Value = UCase(TextBox11.Value)
Table_Object.Range(1, 5).Value = UCase(TextBox12.Value)
Table_Object.Range(1, 6).Value = UCase(TextBox13.Value)
Table_Object.Range(1, 7).Value = UCase(TextBox14.Value)
Table_Object.Range(1, 9).Value = UCase(TextBox15.Value)
Table_Object.Range(1, 8).Value = UCase(ComboBox1.Value)
Table_Object.Range(1, 10).Value = UCase(ComboBox2.Value)
Table_Object.Range(1, 15).Value = UCase(ComboBox3.Value)
Table_Object.Range(1, 13).Value = "25.00"




Call resetForm


End Sub


Sub resetForm()


TextBox1.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
TextBox9.Value = ""
TextBox10.Value = ""
TextBox11.Value = ""
TextBox12.Value = ""
TextBox13.Value = ""
TextBox14.Value = ""
TextBox15.Value = ""
ComboBox1.Value = ""
ComboBox2.Value = ""
ComboBox3.Value = ""
UserForm1.TextBox1.SetFocus


End Sub

 

[Worf]

Hi

You can use advanced filter when there are multiple criteria:

Private Sub CommandButton1_Click()
Dim rd As Worksheet
Set rd = Sheets("raw data")
rd.[g1] = rd.[a1]                       ' column 1
rd.[h1] = rd.[b1]                       ' column 2
rd.[g2] = Me.TextBox4
rd.[h2] = Me.TextBox5                   ' criteria
rd.[q:u].ClearContents
rd.[a1].CurrentRegion.AdvancedFilter 2, rd.[g1:h2], rd.[q1], 0
Select Case rd.[r2]                     ' result from column 2
    Case Is = 0
        Me.TextBox13 = rd.[s2]
        Me.TextBox14 = rd.[t2]
    Case Is > 0
        Me.TextBox13 = rd.[u2]
End Select
End Sub

 

[Yuriy B]

Worf Thank you,

I had no idea that advanced filter was a possibility in VBA.

I had come up with a very rough VBA code that seems to work, but it is not as consolidated as what you have:

Private Sub TextBox5_Change()


If TextBox5.Value >= 1 And Not IsEmpty(TextBox5.Value) Then


    Set MyRange = ActiveWorkbook.Worksheets("Raw Data").Range("Table_Query_from_Visual654[base_id]")
    
    For Each c In MyRange
        If c.Value Like UCase(TextBox4.Value) Then
           If Sheet1.Cells(c.Row, 4) = TextBox5.Value Then
                TextBox13.Value = Sheet1.Cells(c.Row, 9)
            Else
            TextBox14.Value = ""
            End If
        End If


    Next


End If
                
If TextBox5.Value = 0 And Not IsEmpty(TextBox5.Value) Then


    Set MyRange = ActiveWorkbook.Worksheets("Raw Data").Range("Table_Query_from_Visual654[base_id]")
    
    For Each c In MyRange
        If c.Value Like UCase(TextBox4.Value) Then
           If Sheet1.Cells(c.Row, 4) = TextBox5.Value Then
                TextBox13.Value = Sheet1.Cells(c.Row, 5)
                TextBox14.Value = Sheet1.Cells(c.Row, 6)
            
            End If
        End If
    Next


End If
   
End Sub