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.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[TD]Column 4[/TD]
[TD]Column 5[/TD]
[/TR]
[TR]
[TD]Data 1
[/TD]
[TD]0[/TD]
[TD]P/N #[/TD]
[TD]P/N Name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data 2[/TD]
[TD]0[/TD]
[TD]P/N #[/TD]
[TD]P/N Name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data 3[/TD]
[TD]0[/TD]
[TD]P/N #[/TD]
[TD]P/N Name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data 4[/TD]
[TD]0[/TD]
[TD]P/N #[/TD]
[TD]P/N Name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data 4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]P/N #[/TD]
[/TR]
[TR]
[TD]Data 4[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]P/N #[/TD]
[/TR]
[TR]
[TD]Data 5[/TD]
[TD]0[/TD]
[TD]P/N #[/TD]
[TD]P/N Name[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




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