Searching

G

Guest

Guest
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top