Error Handling In a VBA Loop: only works for first error encounter

Dear Experts

I have a long loop with error handling procedures.

The premise is to summarise totals for several clients within a table of jobs
Manual additions can be made to the table, so if a new client is added, there is a separate error handling routine to add the new client code to the summary.

Error handling statements in the code below are:
lines: 65,72,77,102

'-------
M2Y2Jobs:
'   Jobs cross month end or month AND year ends.
'   One Tracker, two sheets, or Two trackers, two sheets
'Start at Row 7, first job
    r = 7
    'loop within a loop
        'while column has data
            'loop through each client and add up totals
'-------
    Do Until Cells(r, 1) = "" 'all data
            ClientID = Trim(Cells(r, 1))
            Mth = Month(Cells(r, 8)) 'Which month the job falls in.
        'Set totals to 0
            JF = 0
            VBF = 0
            Parking = 0
            WT = 0
            JTotal = 0
            VMF = 0
            Inv = 0
            JobCount = 0
        Do 'Add totals for each field for current client
            If Mth = Month(Cells(r, 8)) Then
                JF = JF + Cells(r, 23)
                VBF = VBF + Cells(r, 24)
                If Cells(r, 26) = "" Then
                    Parking = Parking + 0
                Else
                    Parking = Parking + Cells(r, 26)
                End If
                If Cells(r, 28) = "" Then
                    WT = WT + 0
                Else
                    WT = WT + (Cells(r, 28))
                End If
                JTotal = JTotal + Cells(r, 29)
                VMF = VMF + Cells(r, 30)
                Inv = JTotal - VMF
                JobCount = JobCount + 1
            Else
                GoTo TransFer2
            End If
'Next Row
            r = r + 1
         Loop Until Cells(r, 1) <> Cells(r - 1, 1) 'move to next client
'-------
'Transfer Totals to  Auto tracker
TransFer2:
'Transfer totals to Tracker Totals Sheet
    If Mth = mth1 Then
        wbTracker.Activate
        Sheets(shTr).Activate
    ElseIf Mth = mth2 And TrackerStatus = "Month2" Then
        wbTracker.Activate
        Sheets(shTr1).Activate
'-------
    ElseIf Mth = mth2 And TrackerStatus = "Year2" Then
        wbTracker1.Activate
        Sheets(shTr1).Activate
'-------
    End If
'-------
'Filter jobs for vendor file
        On Error Resume Next
        Range("A3").Select
        'Clear Any existing Filters
        ActiveSheet.Range("A3").ShowAllData
        'Add Vendor Filter
        ActiveSheet.Range("$A$3").CurrentRegion.autofilter Field:=7, Criteria1:=VendorRef
        'Reset error handling
        On Error GoTo 0
'-------
 'First Copy the row to the temporary log sheet
        'Identify Row No in Tracker
'Error Handling if new Client Added to Vendor Sheet that does not correspond with Clients processed in this file.
            On Error GoTo ClientAdded:
            rTr = Cells.SpecialCells(xlCellTypeVisible).Find(what:=ClientID, After:=Range("A3"), LookAt:=xlWhole).Row
        ' Fill in the id for current Tracker and sheet on the temporary log
        ' Column A = "original", Column B = Tacker Name, Column C = Tracker Month
            ThisWorkbook.Sheets("Log").Cells(rLg, 1) = "original"
            ThisWorkbook.Sheets("Log").Cells(rLg, 2) = ActiveWorkbook.Name
            With ThisWorkbook.Sheets("Log").Cells(rLg, 3)
                .numberformat = "@"
                .Value = ActiveSheet.Name
            End With
        'Copy and paste the client row before updating, to the temporary Tracker log
            Range(Cells(rTr, 1), Cells(rTr, 45)).Copy shLog.Cells(Rows.Count, 1).Offset.End(xlUp).Offset(0, 3)
        'Leave a one-row gap to be filled in as amended row, AFTER Client & P&L figures have been calculated.
            rLg = rLg + 2
'-------
'Find Client Name and transfer totals to that row

            Cells.Find(what:=ClientID).Offset(0, 23) = JF
            Cells.Find(what:=ClientID).Offset(0, 24) = Parking
            Cells.Find(what:=ClientID).Offset(0, 25) = WT
            Cells.Find(what:=ClientID).Offset(0, 26) = JTotal
            Cells.Find(what:=ClientID).Offset(0, 27) = VMF
            Cells.Find(what:=ClientID).Offset(0, 28) = Inv
            Cells.Find(what:=ClientID).Offset(0, 22) = JobCount
            'reset error handling
            On Error GoTo 0
'-------
'Back to template for next client
            ThisWorkbook.Activate
    Loop

Open in new window


The error handling procedure adds a new line for the new client, but it only works if there is ONE client added.
if more than one client is added, then the error handling just does not work.  The routine crashes with an error message.

Error handling statements in the code below:
line: 43

ClientAdded: 'Error Handling for a Client added into the Vendor Amended File,
            ' not on original file.
'Determine which Tracker File the added job should go.
    'I.e. If is Two sheets, then identify which sheet should be activated
        Select Case TrackerStatus
'------
            Case Is = "Month1" '(One Tracker, OneSheet)
                wbTracker.Activate
                Sheets(shTr).Activate
'-------
            Case Is = "Month2" '(One Tracker, Two Sheets)
            'Activate second sheet
                wbTracker.Activate
                If Mth = mth1 Then
                    Sheets(shTr).Activate
                ElseIf Mth = mth2 Then
                    Sheets(shTr1).Activate
                End If
'-------
            Case Is = "Year2" '(Two Trackers, Two sheets)
                If Mth = mth1 Then
                   wbTracker.Activate
                   Sheets(shTr).Activate
               ElseIf Mth = mth2 Then
                   wbTracker1.Activate
                   Sheets(shTr1).Activate
               End If
'-------
        End Select
'-------
'Activate template to lookup missing client information
        'Clear autofilter
        ActiveSheet.Range("A3").autofilter
        'Select the "Client ID" column
        Cells(3, 3).End(xlDown).Offset(1, 0).Select
        'Reinsert Autofilter
        ActiveSheet.Range("A3").autofilter Field:=7, Criteria1:=VendorRef
        'put new Client ID
        selection = ClientID
        selection.Offset(0, 4) = VendorRef
'-------
        'Clear error handling
        On Error GoTo 0
        ' Resume transferrring data
        Select Case TrackerStatus
            Case Is = "Month1"
                GoTo TransFer:
            Case Is = "Month2", Is = "Year2"
                GoTo TransFer2:
        End Select
    
'-------
End Sub

Open in new window


I am not sure how helpful putting the code in here without a sample workbook is, but is there a general principle of error handling that I am missing, or merely the application of the procedure.

Thanks if anyone out there has an idea.

Cheers
David
David PhelopsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

Instead of a Error handling

You should test if where the error appears

with
rTr = Empty
On Error Resume Next
rTr = Cells.SpecialCells(xlCellTypeVisible).Find(what:=ClientID, After:=Range("A3"), LookAt:=xlWhole).RowOn Error Goto 0
On Error goto  0
if IsEmpty(rTr) Then ' your  new Client Handling

Else ' Go on as usual

End If

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
Once an error has been raised, the only ways to clear it and reset the exception status are:
On Error Goto 0
On Error Goto -1
a Resume statement of some kind
or exiting the routine.

If you do not have one of those - e.g. you just use On Error Goto some label  and then loop - every subsequent error will be unhandled.
0
David PhelopsAuthor Commented:
RGonzo.. Thanks very much.  This worked perfectly.

Rory - thanks - I have tried  resetting the error handling status in various different places using
on error goto 0

Open in new window

The last line of the error handling procedure resumes the routine for the next client.
The code would go;

'Error handling to add new client
ClientAdded:
Code to add client
'reset error handling
on error goto 0
'resume routine
Goto Transfer:

Open in new window


I have also tried resetting the error handling where the routine is resumed, i.e
Transfer: On error goto 0

Open in new window

with the immediate next line
on error goto ClientAdded:

Open in new window


This does not seem to make a difference.  I t still crashes for the next client to be added.

I like the answer from Rgonzo. it makes good sense. I would like to understand, though, why I am unable to reset the error handling procedure.

Thanks for your time.  i really appreciate the efforts you make to help.

Cheers
David
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

David PhelopsAuthor Commented:
Extra points for Rgonzo getting in first and with an alternative solution, too.
Thanks to you both.. you helped me think it through and learn another piece of the big VBA jigsaw.

All the best

David
0
Rory ArchibaldCommented:
Apologies - I edited my comment after posting to say that they all reset the exception but they don't! Although On Error Goto 0 clears the error (so err.number will return 0), it does not reset the exception so using a subsequent On Error Goto label won't work. You would have to use On Error Goto -1 to do that.

However, as RGonzo stated, it is better to deal with the error as close to the source as possible - I merely wanted to (try to) explain why your method was not working. ;)
0
David PhelopsAuthor Commented:
Thanks Rory.  it does seem to work using "Resume" at the end of the error handling procedure, as well.  Or am I imagining that?

David
0
Rory ArchibaldCommented:
Nope, you're not imagining it - I did say a Resume statement would work - that is the usual way of exiting an error handler in fact.
0
David PhelopsAuthor Commented:
Excellent! Thanks for the reassurance...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.