On Error Loop

L

Legacy 287389

Guest
Hi good people!,

This is my code:
Code:
On Error GoTo a:
    Windows("Patron_Actives_Extract.csv").Activate
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:K").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:AF").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1:I1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Patron_Actives_Extract").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Patron_Actives_Extract").Sort.SortFields.Add Key:= _
        Range("G2:G2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Patron_Actives_Extract").Sort
        .SetRange Range("A1:I2000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   Range("I2").Select
    ActiveCell.FormulaR1C1 = "=RC[3]+RC[5]"
 Exit Sub
a:
On Error GoTo b:
Windows("Patron_Actives_Extract (1).csv").Activate
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:K").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:AF").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1:I1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Patron_Actives_Extract (1)").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Patron_Actives_Extract (1)").Sort.SortFields.Add Key:= _
        Range("G2:G2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Patron_Actives_Extract (1)").Sort
        .SetRange Range("A1:I2000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   Range("I2").Select
    ActiveCell.FormulaR1C1 = "=RC[3]+RC[5]"
  Exit Sub
b:
   Windows("Patron_Actives_Extract (2).csv").Activate
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:K").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:AF").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1:I1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Patron_Actives_Extract (1)").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Patron_Actives_Extract (1)").Sort.SortFields.Add Key:= _
        Range("G2:G2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Patron_Actives_Extract (1)").Sort
        .SetRange Range("A1:I2000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   Range("I2").Select
    ActiveCell.FormulaR1C1 = "=RC[3]+RC[5]"
End Sub
The second error condition does not execute. I have read about this on the web and discovered that the error handler becomes ACTIVE after the first error, and needs to be reset, before the error handler can execute a 2nd error, if it should exist. I have read that 'on error goto 0" is used to reset the current active error handler. So, I inserted that line and got this:
Code:
On Error GoTo a:
    Windows("Patron_Actives_Extract.csv").Activate
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:K").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:AF").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1:I1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Patron_Actives_Extract").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Patron_Actives_Extract").Sort.SortFields.Add Key:= _
        Range("G2:G2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Patron_Actives_Extract").Sort
        .SetRange Range("A1:I2000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   Range("I2").Select
    ActiveCell.FormulaR1C1 = "=RC[3]+RC[5]"
 Exit Sub
a:
[B][U]On Error GoTo 0[/U][/B]
On Error GoTo b:
Windows("Patron_Actives_Extract (1).csv").Activate
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:K").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:AF").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1:I1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Patron_Actives_Extract (1)").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Patron_Actives_Extract (1)").Sort.SortFields.Add Key:= _
        Range("G2:G2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Patron_Actives_Extract (1)").Sort
        .SetRange Range("A1:I2000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   Range("I2").Select
    ActiveCell.FormulaR1C1 = "=RC[3]+RC[5]"
  Exit Sub
b:
   Windows("Patron_Actives_Extract (2).csv").Activate
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:K").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:AF").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1:I1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Patron_Actives_Extract (1)").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Patron_Actives_Extract (1)").Sort.SortFields.Add Key:= _
        Range("G2:G2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Patron_Actives_Extract (1)").Sort
        .SetRange Range("A1:I2000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   Range("I2").Select
    ActiveCell.FormulaR1C1 = "=RC[3]+RC[5]"
End Sub
but that obviously does not work...please help me sort this issue out, all and any help will be greatly appreciated!!
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,814
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
On Error Goto 0 deactivates the current error handler but it does not reset any existing errors. You appear to be basically doing the same thing to several different csv files, so you should really put this code into a separate routine, pass the relevant workbook as an argument and then use one error handler in that routine to exit gracefully.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,814
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
If you didn't follow the answer you could have just said so rather than simply posting the same question elsewhere.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,975
Messages
5,447,659
Members
405,460
Latest member
stuartbennett

This Week's Hot Topics

Top