Solved

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

Posted on 2014-03-04
8
3,272 Views
Last Modified: 2014-03-04
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
0
Comment
Question by:David Phelops
  • 4
  • 3
8 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 300 total points
Comment Utility
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 200 total points
Comment Utility
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
 

Author Comment

by:David Phelops
Comment Utility
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
 

Author Closing Comment

by:David Phelops
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
 

Author Comment

by:David Phelops
Comment Utility
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
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
 

Author Comment

by:David Phelops
Comment Utility
Excellent! Thanks for the reassurance...
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now