On Error Resume Label. Stuck as a sticky thing

[sideburnsurfer79]

Hi there, I've been trying to solve this for 2 days now and am at the point of banging my head against the wall. My VBA skills are limited and most macros that I've written/recorded come from trial and error and looking things up on these types of forum but this time, I'm stuck.

What I want to do seems simple in my head but I just can't get it to work.

I have a code that needs to do a number of autofilters from Column A and type some text into Column C and autofill. This bit works with no problems at all. However, when it tries to filter something that is not found, I get an error but am unable to 'get round it'.

I'd like it to clear the filter and start again from the beginning of the next loop

I have tried something like this...but am failing completely. Any ideas gratefully received.

   ActiveSheet.Range("$1:$307").AutoFilter Field:=1, Criteria1:="=*bhe*", _
        Operator:=xlAnd

'If it can't find anything matching the criteria above, I want it to skip to....

    Range([c2], Cells(Rows.Count, "C")).SpecialCells(xlCellTypeVisible).Select
    ActiveCell.FormulaR1C1 = "Blip Header"
    Selection.FillDown
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = RGB(255, 153, 255)
    End With

'here.This way it should clear it and start the next loop part of filtering.

    ActiveSheet.Range("$A$1:$O$307").AutoFilter Field:=1

    ActiveSheet.Range("$1:$307").AutoFilter Field:=1, Criteria1:="=*ghe*", _
        Operator:=xlAnd
    Range([c2], Cells(Rows.Count, "C")).SpecialCells(xlCellTypeVisible).Select
    ActiveCell.FormulaR1C1 = "Gondola Header"
    Selection.FillDown
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = RGB(255, 204, 255)
    End With

    ActiveSheet.Range("$1:$307").AutoFilter Field:=1

    ActiveSheet.Range("$1:$307").AutoFilter Field:=1, Criteria1:="=*tke*", _
        Operator:=xlAnd
    Range([c2], Cells(Rows.Count, "C")).SpecialCells(xlCellTypeVisible).Select
    ActiveCell.FormulaR1C1 = "Talker"
    Selection.FillDown
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = RGB(153, 204, 255)
    End With

    ActiveSheet.Range("$1:$307").AutoFilter Field:=1

I have tried to succeed with some variation of On Error that I found but it either errors or goes into an eternal loop before crashing. I've been using it like the below which is obviously incorrect but I'm stumped.

On Error GoTo ErrHandler:
   ActiveSheet.Range("$1:$307").AutoFilter Field:=1, Criteria1:="=*bhe*", _
        Operator:=xlAnd
    Range([c2], Cells(Rows.Count, "C")).SpecialCells(xlCellTypeVisible).Select
    ActiveCell.FormulaR1C1 = "Blip Header"
    Selection.FillDown
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = RGB(255, 153, 255)
    End With
Label1:
    Exit Sub

ErrHandler:

    Resume Label1:
    ActiveSheet.Range("$A$1:$O$307").AutoFilter Field:=1

    ActiveSheet.Range("$1:$307").AutoFilter Field:=1, Criteria1:="=*ghe*", _
        Operator:=xlAnd
    Range([c2], Cells(Rows.Count, "C")).SpecialCells(xlCellTypeVisible).Select
    ActiveCell.FormulaR1C1 = "Gondola Header"
    Selection.FillDown
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = RGB(255, 204, 255)
    End With

    ActiveSheet.Range("$1:$307").AutoFilter Field:=1

Please help.

 

[RoryA]

What you need is something like this:

Dim rgVis as Range
   ActiveSheet.Range("$1:$307").AutoFilter Field:=1, Criteria1:="=*bhe*", _
        Operator:=xlAnd
On Error Resume Next
   Set rgVis = Range([c2], Cells(Rows.Count, "C")).SpecialCells(xlCellTypeVisible)
On Error Goto 0
If not rgVis Is Nothing then
     rgVis.Value = "Blip Header"

    With rgVis.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = RGB(255, 153, 255)
    End With
    Set rgVis = Nothing
End If
' repeat with next filter
    ActiveSheet.Range("$1:$307").AutoFilter Field:=1, Criteria1:="=*ghe*", _
        Operator:=xlAnd
  On Error Resume Next
  Set rgVis = Range([c2], Cells(Rows.Count, "C")).SpecialCells(xlCellTypeVisible)
  On Error Goto 0
   If Not rgVis Is Nothing then
' and so on

 

[sideburnsurfer79]

Rory, I have no idea what half of that means but you've solved the problem. Thanks

 

[DocAElstein]

....I have no idea what half of that means but you've solved the problem. ...

.... You would if you looked here:
On Error WTF? | Excel Matters

Alan

 

[RoryA]

I should mention that you can also do it without the error handler by including the header row in the SpecialCells command and then checking if the cell count is > 1 before doing anything with the data.

 

[sideburnsurfer79]

I should mention that you can also do it without the error handler by including the header row in the SpecialCells command and then checking if the cell count is > 1 before doing anything with the data.

Would the macro know where to continue running from if I used that command though or would it just stop? I remember looking at that yesterday.

 

[RoryA]

Yes - it would be much the same structure as the error version.