Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Repetition problem with On Error Goto xx

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 30-10-2009
Member
 
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.

Reply With Quote
  #2  
Old 30-10-2009
Member
 
Join Date: May 2008
Posts: 2,291
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
Reply With Quote
  #3  
Old 30-10-2009
Member
 
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.
Reply With Quote
  #4  
Old 30-10-2009
Member
 
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
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Repetition problem with On Error Goto xx"
Thread Thread Starter Forum Replies Last Post
Multiple Repetition of mshta.exe on Task Manager on windows vista Myshaa Operating Systems 4 18-04-2011 08:04 AM
Java - goto or continue Miles Runner Software Development 5 16-01-2010 09:47 AM
To use goto statement or not while coding the program? YatinK Software Development 3 19-02-2009 06:19 PM
VB script - GOTO Do Loop - possible to acheive? Bosch Software Development 5 12-01-2009 07:25 PM


All times are GMT +5.5. The time now is 02:11 AM.