using 3 combo box outputs to find a matching row and display data

[dbkimber]

i have a work sheet that im trying to build a user for in, that will take the information selected in 3 different combo boxes and use it to find a row that matches that information and then display the information it the columns to the right of it. i can make it return the information im looking for but its only matching the row by the last combo box

this is what i have that will get me more or less what i need but only looks at one of the selected criteria and not all 3


Private Sub combobox3_LostFocus()

Dim LstRw As Long, Rng As Range
If Len(ComboBox3) = 0 Then Exit Sub
Range("C3").Select
LstRw = Cells(Rows.Count, "C").End(xlUp).Row

For Each Rng In Range("c101:C" & LstRw)
If Rng.Value = ComboBox3.Text Then


With UserForm1
.OutPutLabel3.Caption = Rng.Value
.TextBox1.Value = Rng.Offset(0, 1).Text
.TextBox2.Value = Rng.Offset(, 2).Text
.TextBox3.Value = Rng.Offset(, 3).Text
.TextBox4.Value = Rng.Offset(, 4).Text
.TextBox5.Value = Rng.Offset(, 5).Text
.TextBox6.Value = Rng.Offset(, 6).Text


End With
End If

Next Rng
UserForm1.Show

End Sub

 

[Rosen]

Assuming that ComboBox1 corresponds to Column A and ComboBox2 corresponds to Column B and all three need to match for the information to display. The following code should do what your looking for. Please note that you may want to add this trigger to the other two combo boxes incase the user selects them out of order.

Please note that this code is untested, please remember to backup your work before using any new code.

Function GetColumnCRangeForCriteria() As Range
    Dim LstRw As Long, Rng As Range, Result As Range
    If Len(ComboBox1) = 0 Or Len(ComboBox2) = 0 Or Len(ComboBox3) = 0 Then
        Err.Raise 101
    End If
    LstRw = Cells(Rows.Count, "A").End(xlUp).Row
    For Each Rng In Range("a101:a" & LstRw)
        If Rng.Value = ComboBox1.Text And Range("B" & Rng.Row).Value = ComboBox2.Text And Range("C" & Rng.Row).Value = ComboBox3.Value Then
            Set GetColumnCRangeForCriteria = Range("C" & Rng.Row)
            Exit Function
        End If
    Next Rng
    Err.Raise 102
End Function

Private Sub combobox3_LostFocus()

    Dim Rng As Range
    
    On Error Resume Next
    Set Rng = GetColumnCRangeForCriteria
    If Err.Number = 101 Then
        ' Not all inputs have been completed
        Exit Sub
    End If
    If Err.Number = 102 Then
        ' Criteria not found
        MsgBox "Unable find selected criteria"
        Exit Sub
    End If
        
    With UserForm1
        .OutPutLabel3.Caption = Rng.Value
        .TextBox1.Value = Rng.Offset(0, 1).Text
        .TextBox2.Value = Rng.Offset(, 2).Text
        .TextBox3.Value = Rng.Offset(, 3).Text
        .TextBox4.Value = Rng.Offset(, 4).Text
        .TextBox5.Value = Rng.Offset(, 5).Text
        .TextBox6.Value = Rng.Offset(, 6).Text
    End With
    UserForm1.Show

End Sub

Hope this helps!

 

[dbkimber]

thanks this seems to be working. now all i have left it to add a button to the form that will find and display the next match from the list

 

[dbkimber]

@Rosen Could i use this same function and sub with a command button click to find the next matching row and display its information?

 

[dbkimber]

ok here is what I've tried to use what I've gotten above along with what I've read and tried to put together with it to find the next matching range with in the given range and display its information. this is the first thing that I've tried that hasn't come back with some kind of error but it doesn't return anything at all. you click the button and nothing


Sub FindValue()

Dim c As Range
Dim firstAddress As String

With Worksheets(1).Range("A101:I")
Set c = .Find(ComboBox1.Text And Range("B" & Rng.Row).Value = ComboBox2.Text And Range("C" & Rng.Row).Value = ComboBox3.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = Range.Select
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With

End Sub

Function GetColumnCRangeForCriteria() As Range
Dim LstRw As Long, Rng As Range, Result As Range
If Len(ComboBox1) = 0 Or Len(ComboBox2) = 0 Or Len(ComboBox3) = 0 Then
Err.Raise 101
End If
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
For Each Rng In Range("a101:a" & LstRw)
If Rng.Value = ComboBox1.Text And Range("B" & Rng.Row).Value = ComboBox2.Text And Range("C" & Rng.Row).Value = ComboBox3.Value Then
Set GetColumnCRangeForCriteria = Range("C" & Rng.Row)
Exit Function
End If
Next Rng
Err.Raise 102
End Function

Private Sub CommandButton2_Click()

Dim Rng As Range
On Error Resume Next
Set Rng = GetColumnCRangeForCriteria
If Err.Number = 101 Then
' Not all inputs have been completed

Exit Sub
End If
If Err.Number = 102 Then
' Criteria not found
MsgBox "Unable find selected criteria"
Exit Sub
End If

With UserForm1
.OutPutLabel3.Caption = FindNext.Value
.TextBox1.Value = FindNext.Offset(0, 1).Text
.TextBox2.Value = FindNext.Offset(, 2).Text
.TextBox3.Value = FindNext.Offset(, 3).Text
.TextBox4.Value = FindNext.Offset(, 4).Text
.TextBox5.Value = FindNext.Offset(, 5).Text
.TextBox6.Value = FindNext.Offset(, 6).Text
End With
UserForm1.Show

End Sub