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:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
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.
 
Upvote 0
If you didn't follow the answer you could have just said so rather than simply posting the same question elsewhere.
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
VBA: Deleting Visible rows using Auto Filter; Excluding Header
Replies
4
Views
821
mumps
mumps
J
  • Question Question
Issue with Macros and renaming files
Replies
6
Views
1K
kevin9999
K
M
  • Question Question
Help with my code
Replies
9
Views
560
kevin9999
K
C
  • Question Question
How to get my macro to stop at the last recognized line
Replies
6
Views
546
Coco1
C
T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
344
RoryA
RoryA

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top