Searching

Does anyone know how to create a userform with 3 textboxs for searching the excel with repeated values. So user can enter 3 values to search a specific data or enter 1 or 2 values to find the first value found in the excel. The worksheet has the following format

Weight Color Code
------ ----- ----
30 40 50
------ ----- ----
30 20 50
------------------
30 40 30

[Damon Ostrander]

I don't understand the question, so I'm guessing that others may be confused as well. Do you want to find, for example, three values that are located, in order, in three adjacent cells in the same row? Some clarification would be helpful.

I have three columns B, C, D. All kinds of number(Integer, decimal) and some repeated numbers are randomly located in those three columns. The format is something like the following

Color Weight Code
-----------------
33 44 23.3
.................
.................
.................
Color Weight Code
-----------------
44 33 34
.................
.................
.................(....means blank cells)
I need to create a userform with three textboxs to have the user enter three values
and have the cell either Color, Weight or Code selected according to the values entered by the user.

Can someone please help me

If you need more information please just let me know
Thank you in advance

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.