dss28
Board Regular
- Joined
- Sep 3, 2020
- Messages
- 165
- Office Version
- 2007
- Platform
- Windows
In my program I have a userform2 with four textboxes named CodeA, CodeB, CodeC and CodeD and a listbox named "Results", and a command button to execute the code.
I am calling this userform2 through another main userform1.
The four text boxes are used to search four columns - column1, 2, 4 and 7 of sheet2 respectively.
Table1 is the name of the range Sheet2!$A$2:$O$999999
These columns in sheet2 have the heading as ColumnCodeA, ColumnCodeB, ColumnCodeC, ColumnCodeD.
The problem I am facing is:
If the command button to open the UserForm2 is placed on the Sheet2, the code to search all four columns works, but when I placed the command button to call the userform2 from my main userform1, it gives error.
I have replaced terms like CodeA.Value by UserForm2.CodeA.Value etc. and tried the code in a module as well as in UserForm2 but it gives error.
I am not able to properly reference the columns to be searched in sheet2 . eg in the lines SearchColumn = "ColumnCodeA"
I also has issues in the line With Range("Table1[" & SearchColumn & "]")
request help to resolve.
thank you.
I am calling this userform2 through another main userform1.
The four text boxes are used to search four columns - column1, 2, 4 and 7 of sheet2 respectively.
Table1 is the name of the range Sheet2!$A$2:$O$999999
These columns in sheet2 have the heading as ColumnCodeA, ColumnCodeB, ColumnCodeC, ColumnCodeD.
The problem I am facing is:
If the command button to open the UserForm2 is placed on the Sheet2, the code to search all four columns works, but when I placed the command button to call the userform2 from my main userform1, it gives error.
I have replaced terms like CodeA.Value by UserForm2.CodeA.Value etc. and tried the code in a module as well as in UserForm2 but it gives error.
I am not able to properly reference the columns to be searched in sheet2 . eg in the lines SearchColumn = "ColumnCodeA"
I also has issues in the line With Range("Table1[" & SearchColumn & "]")
request help to resolve.
thank you.
VBA Code:
Private Sub SearchBtn_Click()
Dim SearchTerm As String
Dim SearchColumn As String
Dim RecordRange As Range
Dim FirstAddress As String
Dim FirstCell As Range
Dim RowCount As Integer
' Display an error if no search term is entered
' codeA... CodeB.....CodeC.... CodeD are textbox names
'listbox name is Results
If CodeA.Value = "" And CodeB.Value = "" And CodeC.Value = "" And CodeD.Value = "" Then
MsgBox "No search term specified", vbCritical + vbOKOnly
Exit Sub
End If
' Work out what is being searched for
If CodeA.Value <> "" Then
SearchTerm = CodeA.Value
SearchColumn = "ColumnCodeA"
End If
If CodeB.Value <> "" Then
SearchTerm = CodeB.Value
SearchColumn = "ColumnCodeB"
End If
If CodeC.Value <> "" Then
SearchTerm = CodeC.Value
SearchColumn = "ColumnCodeC"
End If
If CodeD.Value <> "" Then
SearchTerm = CodeD.Value
SearchColumn = "ColumnCodeD"
End If
Results.Clear
' Only search in the relevant table column i.e. if somone is searching in text box CodeA
' only search in the ColumnCodeA
With Range("Table1[" & SearchColumn & "]")
' Find the first match
Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)
' If a match has been found
If Not RecordRange Is Nothing Then
FirstAddress = RecordRange.Address
RowCount = 0
Do
' Set the first cell in the row of the matching value
Set FirstCell = Range("A" & RecordRange.Row)
' Add matching record to List Box
Results.AddItem
Results.List(RowCount, 0) = FirstCell(1, 1)
Results.List(RowCount, 1) = FirstCell(1, 2)
Results.List(RowCount, 2) = FirstCell(1, 4)
Results.List(RowCount, 3) = FirstCell(1, 7)
RowCount = RowCount + 1
' Look for next match
Set RecordRange = .FindNext(RecordRange)
' When no further matches are found, exit the sub
If RecordRange Is Nothing Then
Exit Sub
End If
' Keep looking while unique matches are found
Loop While RecordRange.Address <> FirstAddress
Else
' If you get here, no matches were found
Results.AddItem
Results.List(RowCount, 0) = "Nothing Found"
End If
End With
End Sub
