On Error Resume Label. Stuck as a sticky thing

sideburnsurfer79

New Member
Joined
Mar 8, 2010
Messages
7
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.



HTML:
   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.

HTML:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,178
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
What you need is something like this:
Code:
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,178
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

New Member
Joined
Mar 8, 2010
Messages
7
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,178
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Yes - it would be much the same structure as the error version.
 

Forum statistics

Threads
1,078,252
Messages
5,339,100
Members
399,278
Latest member
randomNumberGenerator2211

Some videos you may like

This Week's Hot Topics

Top