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.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,177
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
35,177
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
35,177
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Yes - it would be much the same structure as the error version.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,859
Messages
5,489,309
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top