Mdrury
12-12-2017, 03:24 AM
Group,
I also have a Adv filter issue that I can't seem to resolve. Everything works except when nothing is returned. Any suggestions on how to contain this?
Private Sub cmdSearch_Click()
Dim txtSearchCrit As String
Dim FindMe As Range
Dim DataSH As Worksheet
'set object variables
Set DataSH = Sheet5
DataSH.Range("A1").Select
Application.ScreenUpdating = False
If cmbSearchBy = "" Then
MsgBox "Please enter in a Search By Category"
Exit Sub
End If
If Me.cmbSearchBy = "PartNumber" Then
DataSH.Range("H3") = "*" & Me.txtSearchCrit.Value & "*"
Set FindMe = txtSearchCrit
Else
DataSH.Range("I3") = "*" & Me.txtSearchCrit.Value & "*"
End If
Application.CutCopyMode = False
Range("Parts_List[#All]") _
.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange _
:=DataSH.Range("H2:I3"), _
CopyToRange:=DataSH.Range("K2:M2"), _
Unique:=False
'Add the search results to the list box
If Not DataSH.Range("SearchResult") Is Nothing Then
lstPartsSearch.RowSource = DataSH.Range("SearchResult").Address
Else
MsgBox "Your part was not found"
End If
'Clear the values in the search criteria cells
Sheet5.Range("H3:I3").Value = ""
Application.ScreenUpdating = True
End Sub
I also have a Adv filter issue that I can't seem to resolve. Everything works except when nothing is returned. Any suggestions on how to contain this?
Private Sub cmdSearch_Click()
Dim txtSearchCrit As String
Dim FindMe As Range
Dim DataSH As Worksheet
'set object variables
Set DataSH = Sheet5
DataSH.Range("A1").Select
Application.ScreenUpdating = False
If cmbSearchBy = "" Then
MsgBox "Please enter in a Search By Category"
Exit Sub
End If
If Me.cmbSearchBy = "PartNumber" Then
DataSH.Range("H3") = "*" & Me.txtSearchCrit.Value & "*"
Set FindMe = txtSearchCrit
Else
DataSH.Range("I3") = "*" & Me.txtSearchCrit.Value & "*"
End If
Application.CutCopyMode = False
Range("Parts_List[#All]") _
.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange _
:=DataSH.Range("H2:I3"), _
CopyToRange:=DataSH.Range("K2:M2"), _
Unique:=False
'Add the search results to the list box
If Not DataSH.Range("SearchResult") Is Nothing Then
lstPartsSearch.RowSource = DataSH.Range("SearchResult").Address
Else
MsgBox "Your part was not found"
End If
'Clear the values in the search criteria cells
Sheet5.Range("H3:I3").Value = ""
Application.ScreenUpdating = True
End Sub