Index/match with multiple criteria in userform

[ollieb34]

Greetings mrexcel users,

Wondering if someone can share their knowledge of vba with the index match function. I am confident with the index match function at worksheet level but am really struggling with vba-

I am creating a userform (UserForm1) which requires users to select a vaue from combobox "Product number" (cmbProd) and enter a value into text box "Label Trace code" (txtLab)- Note- the value in text box txtlab uses Numbers and "." example (ET.475.2028)

When the user has entered this data they will click the button check (cmdCheck)

On (cmdCheck) i want the userform to check and match the data entered in (cmbProd) and (txtLab) with a table (TableProd) in a worksheet (Lookups) from (column C and D) and return either "Pass" if exact match or "Fail'' if no match in text box "Result"(textRes1)

Please forgive me if this is a simple task to achieve- perhaps not even requiring the use of the index match function. i have spent hours upon hours trying to figure this out and to find an answer on the internet is impossible

Thanks in advance to anyone who is able to provide support.

 

[Norie]

Do you need multiple criteria?

In the posted data it appears the product no is unique and if that's the case you could easily use that to return the other values.

 

[ollieb34]

Do you need multiple criteria?

In the posted data it appears the product no is unique and if that's the case you could easily use that to return the other values.

Hi Norie,
Thanks for a swift reply- The product number is unique. The label trace code must match the product number assigned to it. If it does not, the test must "fail"

 

[aRandomHelper]

If product number and label trace code is always 1:1, don't mind me asking, why is there a need to enter the code then check against your data sheet? Is it some form of test?
It seems your form is for purpose of entering data? You can populate the label trace code once user selects product number, then there's no need to "check" for pass or fail.

 

[Norie]

All you need to get the correct trace_code is to find the row the selected product number is on and get the value from column D of that row.

That could be done with MATCH but it could also be done using the ListIndex of the product number combobox.

 

[ollieb34]

If product number and label trace code is always 1:1, don't mind me asking, why is there a need to enter the code then check against your data sheet? Is it some form of test?
It seems your form is for purpose of entering data? You can populate the label trace code once user selects product number, then there's no need to "check" for pass or fail.

Thats correct- it is a test. My intention is not to populate the label trace code. If the user enters a label trace code and it does not match the product number- the test must return fail

 

[ollieb34]

All you need to get the correct trace_code is to find the row the selected product number is on and get the value from column D of that row.

That could be done with MATCH but it could also be done using the ListIndex of the product number combobox.

Norie,
Im not trying to return the trace code. I need the user to enter a trace code and check it matches against the product number they have have selected with the product number and trace code from the lookup table.

 

[aRandomHelper]

I assume you want to test Net trace code as well, try this

Private Sub cmdCheck_Click()
    Dim lab As String, net As String
    
    If cmbProd.Value <> "" Then
        If txtLab.Value <> "" Then 'label trace test
            lab = Sheets("Lookups").ListObjects("TableProd").DataBodyRange.Columns(1).Find(cmbProd.Value, lookat:=xlwhole).Offset(, 1).Value
            textRes1.Value = "Fail"
            If lab = txtLab.Value Then textRes1.Value = "Pass"
        End If
        
        If txtNet.Value <> "" Then 'net trace test
            net = Sheets("Lookups").ListObjects("TableProd").DataBodyRange.Columns(1).Find(cmbProd.Value, lookat:=xlwhole).Offset(, 2).Value
            textRes2.Value = "Fail"
            If net = txtNet.Value Then textRes2.Value = "Pass"
        End If
    End If
End Sub

 

[Norie]

How are you going to check they've entered the correct trace code if you don't get the correct trace code that corresponds to the selected product number?

See if this works.

Private Sub UserForm_Initialize()
    ' populate product number combobox
    With Sheets("Lookups")
        Me.cmbProd.List = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Value
    End With
End Sub

Private Sub cmdCheck_Click()
Dim strTraceCode As String

    If Me.cmbProd.ListIndex = -1 Then
        MsgBox "Please select a  product number!
    End If

    If Me.txtLab.Value = "" Then
        MsgBox "Please enter a lable trace code!"
    End If

    ' get correct label trace code
    strTraceCode = Sheets("Lookups").Range("D" & Me.cmbProd.ListIndex + 1).Value

    If Me.txtLabel.Value = strTraceCode Then
        Me.textRes1.Value = "Pass
    Else

    End If

End Sub

 

[ollieb34]

I assume you want to test Net trace code as well, try this

Private Sub cmdCheck_Click()
    Dim lab As String, net As String
   
    If cmbProd.Value <> "" Then
        If txtLab.Value <> "" Then 'label trace test
            lab = Sheets("Lookups").ListObjects("TableProd").DataBodyRange.Columns(1).Find(cmbProd.Value, lookat:=xlwhole).Offset(, 1).Value
            textRes1.Value = "Fail"
            If lab = txtLab.Value Then textRes1.Value = "Pass"
        End If
       
        If txtNet.Value <> "" Then 'net trace test
            net = Sheets("Lookups").ListObjects("TableProd").DataBodyRange.Columns(1).Find(cmbProd.Value, lookat:=xlwhole).Offset(, 2).Value
            textRes2.Value = "Fail"
            If net = txtNet.Value Then textRes2.Value = "Pass"
        End If
    End If
End Sub

yes i wish to test the Net trace code as well.

I copied the code and it was throwing up a runtime error 424 object required- but i realised the textbox result boxes are names txtRes1 and txtRes2.

Ive amended those and its working perfectly.

That is absolutely amazing.. i have been stuck on this for absolutely months thank you so much. i am ever so appreciative

now without being cheeky how about if I wanted the result text box to fill green colour if pass and red if fail?