Anon,
Was this from one of your posts as well?
Posted: 2002-03-04 05:35
--------------------------------------------------------------------------------
Does anyone know how to create a userform with 3 textboxs to search 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
If it is that's good because I have created a 3 criteria search userform for it. I have put this data on Sheet1 in range "B1:D4".
I have created a userform (UserForm1) with 3 text boxes and 1 commandbutton called "SearchButton". The text beoxes are called: "txtWeight", "txtColour" and "txtCode".
You type values into these textboxes then hit the button. If the data in all three textboxes is found in the same row, then those three cells are selected. If nothing else, this is an example of a way forward for you. I'm afraid I had to start from scratch because I had to get away from those awful "If...ElseIf..." statements. (That's a semi-serious joke there.
) I haven't done the code for only putting in one variable or two variables. I also haven't put in much error trapping.
Create the userform1 as described above with the three textboxes and 1 commandbutton. Then insert this code onto the form:
<pre>
Private Sub SearchButton_Click()
MainSearch
Unload Me
End Sub
</pre>
Then in a standard module, insert this code:
<pre>
Option Base 0
Public Sub Main()
UserForm1.Show
End Sub
Public Sub MainSearch()
'Declare Variables
'Search strings
Dim sWeight As String
Dim sColour As String
Dim sCode As String
'Columns to search
Dim iWeightCol As Integer
Dim iColourCol As Integer
Dim iCodeCol As Integer
'Declare these as long integers just in case there are more than 32,767 rows being used
Dim iWeightRows() As Long
Dim iColourRows() As Long
Dim iCodeRows() As Long
'Variable to hold the common row
Dim iRow As Long
'Initialise Variables
With UserForm1
sWeight = .txtWeight.Value
sColour = .txtColour.Value
sCode = .txtCode.Value
End With
'Columns to search in
iWeightCol = 2
iColourCol = 3
iCodeCol = 4
'Create Row Arrays
CreateRowArray sWeight, iWeightCol, iWeightRows()
CreateRowArray sColour, iColourCol, iColourRows()
CreateRowArray sCode, iCodeCol, iCodeRows()
'Find Common row
iRow = CommonRow(iWeightRows, iColourRows, iCodeRows)
'Highlight the Common row
Range(Cells(iRow, iWeightCol), Cells(iRow, iCodeCol)).Select
End Sub
Private Sub CreateRowArray(ByVal sTargetText As String, _
ByVal iCol As Integer, _
ByRef iReturnArray() As Long)
Dim FindRange As Range
Dim FirstAddress As String
Dim i As Long
'Find first Instance of the value
Set FindRange = Columns(iCol).Find(What:=sTargetText, LookAt:=xlWhole)
'Make sure a value was found
If Not FindRange Is Nothing Then
FirstRange = FindRange.Address
Do
'Increment count by 1
ReDim Preserve iReturnArray(i)
iReturnArray(i) = FindRange.Row
Set FindRange = Columns(iCol).FindNext(FindRange)
i = i + 1
Loop While Not FindRange Is Nothing And FindRange.Address <> FirstRange
Else
MsgBox "Not Found"
End If
End Sub
Private Function CommonRow(ByRef iArrayi() As Long, _
ByRef iArrayj() As Long, _
ByRef iArrayk() As Long) As Long
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' CommonRow - This function takes in three arrays as arguments then
' compares the values in the arrays to find a common value
' in all three
' Created - 3/5/02
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Loop variables
Dim i As Integer
Dim j As Integer
Dim k As Integer
'Compare Row Arrays
For i = 0 To UBound(iArrayi())
For j = 0 To UBound(iArrayj())
If iArrayi(i) = iArrayj(j) Then
For k = 0 To UBound(iArrayk())
If iArrayi(i) = iArrayk(k) Then
CommonRow = iArrayi(i)
Exit Function
End If
Next
End If
Next
Next
'If the code gets to here, then no common row was found
MsgBox "No common row was found for these values"
End Function
</pre>
Run the code from Sub Main and type in your values.
This code is not designed to handle the 1 variable and 2 variable combos. It has taken me 90 minutes to make this code and it's nearly beddy bo bo's time.
I'll be out of town for the next two days. Since you haven't put an email address in (and I don't do it myself either) I'm going to email a copy of this example to Barrie Davidson. If you need to see this example then get in touch with Mr. Davidson. Also, if you need help to modify any of the code, I'm sure you will find people willing to help here.
Why have I spent so much time on this one, I hear you ask?
1. I know exactly the position you are in and completely empathise with you.
2. You're boss sounds like the same sort of knobjob that I had to learn VBA for.
Cheers
