PDA

View Full Version : Advanced Filter Help



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

SamT
12-13-2017, 09:23 AM
Moderator Bump

Aflatoon
12-14-2017, 03:42 AM
Could you be more specific as to what the actual problem is?