Solved

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

Posted on 2014-03-04
8
3,522 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 49

Accepted Solution

by:
Rgonzo1971 earned 300 total points
ID: 39902920
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
ID: 39902954
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
ID: 39903120
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Closing Comment

by:David Phelops
ID: 39903163
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39903209
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
ID: 39903213
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
ID: 39903292
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
ID: 39903702
Excellent! Thanks for the reassurance...
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

822 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