AutoFilter method throws error in VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-1403104%22%20slang%3D%22en-US%22%3EAutoFilter%20method%20throws%20error%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1403104%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20help%20a%20user%20group%20with%20a%20spreadsheet.%20The%20person%20who%20made%20it%20left.%20Can%20someone%20assist%20with%20the%20following%20code%3F%20It%20fails%20on%20the%20line%20%22PEntry.AutoFilter%22%20with%20%22AutoFilter%20method%20of%20Range%20class%20failed%22.%20A%20data%20entry%20form%20has%20a%20few%20fields%2C%20some%20buttons%2C%20with%20one%20a%20search%20button%20that%20directly%20runs%20the%20macro%20below.%20It%20was%20working%20fine%20with%20data%20entered.%20It%20quit%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-basic%22%3E%3CCODE%3ESub%20Search()%0A'%0A'%20Search%20Macro%0A'%0A%0ASheets(%22Client%20Directory%22).Visible%20%3D%20True%0A%0AColumns(%22G%3AJ%22).Select%0A%20%20%20%20Selection.ClearContents%0A%20%20%20%20%0A%20Range(%22G1%22).Value%20%3D%20%22Possible%20Clients%22%0A%20%0ADim%20First3%20As%20Range%2C%20PEntry%20%20As%20Range%0A%0ASet%20First3%20%3D%20Sheets(%22Entry%20Form%22).Range(%22E6%22)%0ASet%20PEntry%20%3D%20Sheets(%22Client%20Directory%22).Columns(%22A%3AE%22)%0A%0ASheets(%22Client%20Directory%22).Select%0A%20%20Columns(%22A%3AE%22).Select%0A%20%20%20%20PEntry.AutoFilter%20Field%3A%3D5%2C%20Criteria1%3A%3DFirst3.Value%0A%20%20Sheets(%22Client%20Directory%22).Select%0A%20%20%20%20Columns(%22A%3AD%22).Select%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20Selection.Copy%0A%20%20%20%20Sheets(%22Entry%20Form%22).Select%0A%20%20%20%20Range(%22G2%22).Select%0A%20%20%20%20Selection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%0A%20%20%20%20%20%20%20%20%3A%3DFalse%2C%20Transpose%3A%3DFalse%0A%0ASheets(%22Client%20Directory%22).Visible%20%3D%20False%0A%0ARange(%22E2%3AE3%22).Select%0A%20%20%20%20Selection.ClearContents%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1403104%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1405316%22%20slang%3D%22en-US%22%3ERe%3A%20AutoFilter%20method%20throws%20error%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1405316%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F663445%22%20target%3D%22_blank%22%3E%40subjectivist%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20thing%20first%2C%20the%20autofilter%20criteria%20is%20taken%20from%20EntryForm%20sheet%20E6%20cell.%20You%20can%20manually%20copty%20the%20whole%20content%20of%20the%20cell%20(sometimes%20there%20may%20be%20some%20invisible%20chars%20there)%20and%20try%20a%20manuel%20filter%20on%20said%20range%2C%20Client%20Directory%20columns%20A%3AE.%20the%20filter%20condition%20applies%20to%20column%20E.%20If%20it%20does%20then%20we%20need%20to%20check%20other%20options%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I need to help a user group with a spreadsheet. The person who made it left. Can someone assist with the following code? It fails on the line "PEntry.AutoFilter" with "AutoFilter method of Range class failed". A data entry form has a few fields, some buttons, with one a search button that directly runs the macro below. It was working fine with data entered. It quit working.

 

 

Sub Search()
'
' Search Macro
'

Sheets("Client Directory").Visible = True

Columns("G:J").Select
    Selection.ClearContents
    
 Range("G1").Value = "Possible Clients"
 
Dim First3 As Range, PEntry  As Range

Set First3 = Sheets("Entry Form").Range("E6")
Set PEntry = Sheets("Client Directory").Columns("A:E")

Sheets("Client Directory").Select
  Columns("A:E").Select
    PEntry.AutoFilter Field:=5, Criteria1:=First3.Value
  Sheets("Client Directory").Select
    Columns("A:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Entry Form").Select
    Range("G2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Sheets("Client Directory").Visible = False

Range("E2:E3").Select
    Selection.ClearContents

 

 

 

1 Reply
Highlighted

@subjectivist 

First thing first, the autofilter criteria is taken from EntryForm sheet E6 cell. You can manually copty the whole content of the cell (sometimes there may be some invisible chars there) and try a manuel filter on said range, Client Directory columns A:E. the filter condition applies to column E. If it does then we need to check other options