Hi,
I posted a response to this question this morning, but it seems to have gotten lost, so here is the solution again. This solution assumes you have a custom userform with three textboxes, named ColorTB, WeightTB, and CodeTB, and also have a commandbutton named FindBtn, and that the sheet you want to search on is active at the time the form is loaded. It also assumes that the 3 data cells are directly under (in the next row from) the Color/Weight/Code headings.
Private Sub FindBtn_Click()
Dim Color As String
Dim Weight As String
Dim Code As String
Color = ColorTB.Text
Weight = WeightTB.Text
Code = CodeTB.Text
'Search active worksheet for line with these values
'First find lines with Color/Weight/Code header
Dim Cell As Range
Dim FirstFind As Range
Set Cell = Nothing
Do
If Cell Is Nothing Then
Set Cell = Columns(2).Find("Color", LookIn:=xlValues)
Set FirstFind = Cell
End If
If Not Cell Is Nothing Then
'color header found--check for weight/code header
If Cell.Offset(0, 1) = "Weight" And Cell.Offset(0, 2) = "Code" Then
'valid complete header found--now check data values
If Cell.Offset(1, 0) = Color And _
Cell.Offset(1, 1) = Weight And _
Cell.Offset(1, 2) = Code Then
'value found--select these cells
Range(Cell.Offset(1), Cell.Offset(1, 2)).Select
Exit Sub
End If
End If
End If
Set Cell = Columns(2).FindNext(Cell)
Loop Until Cell.Address = FirstFind.Address
MsgBox "Values not found", vbexplanation, "Search results"
End Sub
This requires and EXACT match in all three cells to all three textboxes. Of course this code goes into the event code module associated with the userform.
