|
|
![]() |
| Thread Tools | Search this Thread |
#1
| |||
| |||
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 |
#2
| |||
| |||
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
| |||
| |||
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
| |||
| |||
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 |
![]() |
|
Tags: on error goto, repetition |
Thread Tools | Search this Thread |
|
![]() | ||||
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 10:47 AM |
To use goto statement or not while coding the program? | YatinK | Software Development | 3 | 19-02-2009 07:19 PM |
VB script - GOTO Do Loop - possible to acheive? | Bosch | Software Development | 5 | 12-01-2009 08:25 PM |