Advanced Filter Help

[Mdrury]

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]

Moderator Bump

[Aflatoon]

Could you be more specific as to what the actual problem is?