Results 1 to 4 of 4

Thread: Repetition problem with On Error Goto xx

  1. #1
    Join Date
    Sep 2009
    Posts
    152

    Repetition problem with On Error Goto xx

    I ask my macro to select a data range visible after a filter in multiple tabs. If there is no beach visible (which can happen), I put an On Error Goto to pass it to the next tab. This works if the error appears only once, but not both ...

    Here are simplified in my line of code:
    Code:
        Sheets("A").select 
        Range("A1").Select 
        On Error GoTo Suite1 
        With Range("_FilterDatabase") 
        Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible) 
        With plagefiltervisible.Areas(1).Rows(1).Select 
        Range(Selection, Selection.End(xlDown)).Select 
        End With 
        End With 
    Suite1: 
        Sheets("B").select 
        Range("A1").Select 
        On Error GoTo Suite2 
        With Range("_FilterDatabase") 
        Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible) 
        With plagefiltervisible.Areas(1).Rows(1).Select 
        Range(Selection, Selection.End(xlDown)).Select 
        End With 
        End With 
    Suite2: 
        Sheets("C").select 
        Range("A1").Select 
        On Error GoTo Suite3 
        With Range("_FilterDatabase") 
        Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible) 
        With plagefiltervisible.Areas(1).Rows(1).Select 
        Range(Selection, Selection.End(xlDown)).Select 
        End With 
        End With 
    Suite3: 
        Sheets("D").select 
        Range("A1").Select 
        On Error GoTo Suite4 
        With Range("_FilterDatabase") 
        Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible) 
        With plagefiltervisible.Areas(1).Rows(1).Select 
        Range(Selection, Selection.End(xlDown)).Select 
        End With 
        End With 
    Suite4: 
    End sub
    If the leaf B and C in the sheet is not visible in data, the code blocks on the line marked in BLUE.

  2. #2
    Join Date
    May 2008
    Posts
    2,302

    Re: Repetition problem with On Error Goto xx

    Have you tried to Err.Clear after each label suite1: suite2: etc. ...
    ?

    suite1:
    Err.Clear

    Also I have always seen On Error to be associated with Resume

  3. #3
    Join Date
    Sep 2009
    Posts
    152

    Re: Repetition problem with On Error Goto xx

    I just tried with Err.Clear and it makes me exactly the same thing. I do not understand why this does not work twice. If the error happens once, it leaves much to good result.

  4. #4
    Join Date
    Nov 2008
    Posts
    1,054

    Re: Repetition problem with On Error Goto xx

    Here is a solution that probably works, although there probably is way to simple:

    Code:
       Sheets("A").select  
        Range("A1").Select  
        On Error GoTo Err1 
        With Range("_FilterDatabase" )  
        Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)  
        With plagefiltervisible.Areas(1).Rows(1).Select  
        Range(Selection, Selection.End(xlDown)).Select  
        End With  
        End With  
    Main_suite: 
        Go To Suite4 
    Err1:  
        Sheets("B" ).select  
        Range("A1" ).Select  
        On Error GoTo Err2 
        With Range("_FilterDatabase" )  
        Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)  
        With plagefiltervisible.Areas(1).Rows(1).Select  
        Range(Selection, Selection.End(xlDown)).Select  
        End With  
        End With  
    Err1_fin: 
        Resume Main_suite 
    Err2:  
        Sheets("C" ).select  
        Range("A1" ).Select  
        On Error GoTo Err3  
        With Range("_FilterDatabase" )  
        Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)  
        With plagefiltervisible.Areas(1).Rows(1).Select  
        Range(Selection, Selection.End(xlDown)).Select  
        End With  
        End With  
    Err2_fin: 
        Resume Err1_fin 
    Err3:  
        Sheets("D" ).select  
        Range("A1" ).Select  
        On Error GoTo Err3_fin 
        With Range("_FilterDatabase" )  
        Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)  
        With plagefiltervisible.Areas(1).Rows(1).Select  
        Range(Selection, Selection.End(xlDown)).Select  
        End With  
        End With  
    Err3_fin: 
        Resume Err2_fin 
    Suite4:  
    End sub

Similar Threads

  1. Replies: 4
    Last Post: 18-04-2011, 08:04 AM
  2. Java - goto or continue
    By Miles Runner in forum Software Development
    Replies: 5
    Last Post: 16-01-2010, 09:47 AM
  3. To use goto statement or not while coding the program?
    By YatinK in forum Software Development
    Replies: 3
    Last Post: 19-02-2009, 06:19 PM
  4. VB script - GOTO Do Loop - possible to acheive?
    By Bosch in forum Software Development
    Replies: 5
    Last Post: 12-01-2009, 07:25 PM

Posting Permissions

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