How to handle Error 1004 in Autofilter Multiple Criteria

[petes]

Hi Guys -

I have this code. The 2nd part fails (Doesn't move to EFCFilter on error) whenever there is no matching filter criteria in 1st part. But, runs perfectly if there is matching filter criteria found in 1st part. Any thought?

[B]'--------------------------1st Part----------------------------------------------[/B] 
On Error GoTo ACT2


   AutoFilterMode = False
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=18, Criteria1:="False"
       
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Areas(2).Select
With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True

[B]'--------------------------2nd Part----------------------------------------------[/B]
ACT2:
On Error GoTo EFCFilter
    
    AutoFilterMode = False
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=18, Criteria1:="=#N/A"
       
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Areas(2).Select
With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True

Note: I don't want to combine multiple criteria. I need it separately as is.

 

[Peter_SSs]

Hard to give specific advice as we don't know ..
- the object of the code
- the full code
- what the data is like

However, the following is not right.

AutoFilterMode = False

The syntax for AutoFilterMode is (from Help)

Syntax
expression. AutoFilterMode

expression A variable that represents a Worksheet object.

You do not have the qualifying Worksheet object. It would need to be something like

ActiveSheet.AutoFilterMode = False

 

[petes]

Sorry about that! Here is the full code. Each part fails if there is no matching criteria in previous part and also in the current part. Hope its not confusing.

Sub FilterTEST()
   
'--------------------------1st Part-----------------------------------------------
  
On Error GoTo ACT2


   AutoFilterMode = False
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=18, Criteria1:="False"
       
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Areas(2).Select
With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True


ACT2:
On Error GoTo EFCFilter
    
    AutoFilterMode = False
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=18, Criteria1:="=#N/A"
       
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Areas(2).Select
With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True


'--------------------------2nd Part------------------------------------------------


EFCFilter:
On Error GoTo EFC2


    AutoFilterMode = False
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=22, Criteria1:="False"
    
rSource.Resize(rSource.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Select
With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    
EFC2:
On Error GoTo BUDGETFilter


AutoFilterMode = False
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=22, Criteria1:="=#N/A"
       
rSource.Resize(rSource.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Select
With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True


'--------------------------3rd part-----------------------------------------------------


BUDGETFilter:
On Error GoTo BGT2


    AutoFilterMode = False
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=26, Criteria1:="False"
      
rSource.Resize(rSource.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Select
With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True


BGT2:
On Error GoTo ENDFilter
AutoFilterMode = False
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=26, Criteria1:="=#N/A"
      
rSource.Resize(rSource.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Select
With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True


'--------------------------End-----------------------------------------------------	


ENDFilter:
    Range("A1").Select
    Selection.AutoFilter
  
End Sub

 

[RoryA]

Your error handling is wrong - you need a proper error handler and a Resume statement to clear the current exception, otherwise anything but the first error will not be handled: http://excelmatters.com/2015/03/17/on-error-wtf/

 

[petes]

[QUOTEhttp://excelmatters.com/2015/03/17/on-error-wtf/[/QUOTE]

Awesome doc on error handling..very helpful for a novice like me. I was able to crack it, Thanks!

 

[RoryA]

Glad you found it useful!