Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: On Error Resume Label. Stuck as a sticky thing

  1. #1
    New Member
    Join Date
    Mar 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default On Error Resume Label. Stuck as a sticky thing

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

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: On Error Resume Label. Stuck as a sticky thing

    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

  3. #3
    New Member
    Join Date
    Mar 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error Resume Label. Stuck as a sticky thing

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

  4. #4
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error Resume Label. Stuck as a sticky thing

    Quote Originally Posted by sideburnsurfer79 View Post
    ....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

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: On Error Resume Label. Stuck as a sticky thing

    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.

  6. #6
    New Member
    Join Date
    Mar 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error Resume Label. Stuck as a sticky thing

    Quote Originally Posted by RoryA View Post
    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.

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: On Error Resume Label. Stuck as a sticky thing

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •