Error Handling; Count Times Loop Repeated

Question

I'm still very new to VBA, so I appreciate any patience and guidance you're willing to give. I've been trying to log how many times a loop has to restart for each row and have tinkered with it long enough that I'm out of ideas that might resolve the issue.

JumpToHere:
        On Error GoTo MyErrorHandler

Do Until IsEmpty(ActiveCell)

[SOME ".VALUE =" AND A BUNCH OF QUERIES HAPPEN HERE]

        Application.ScreenUpdating = True
        ActiveCell.Offset(1, 0).Select
        DoEvents
        With Application
            .Calculation = xlManual
            .ScreenUpdating = False
        End With

MyErrorHandler:
    Err.Clear
    ActiveCell.Offset(0, 20).Value = ActiveCell.Offset(0, 20).Value + 1
    Resume JumpToHere

Loop

My concern (I believe) is with the ActiveCell.Offset(0, 20).Value + 1 line. When there is an error, I want the macro to log a 1 in ActiveCell.Offset(0, 20) when the first error occurs, restart the loop from that same row where the error occurred, then add +1 to ActiveCell.Offset(0, 20) if there are additional errors/loops performed on the row.

Currently, the macro seems to never add an error count in the first row, but will place a value of 2 in every subsequent row, including in the first blank row at the bottom of the data to be processed, where there is no processing needed. With all of the error handling commented out, the macro runs through all rows just fine, leaving me to believe the above code is not truly logging error counts.

The active cell is not always the one you think it is. For this reason (and others) it's a good idea to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Feb 11 '20 at 22:13
Maybe post the complete code and from there we can see how to handle it. — Ricardo Diaz, Feb 11 '20 at 22:28
Store the count in a variable instead of a cell, and then when it's done, put the value of the variable into the cell. It will also work much faster that way if you have a lot of data. — braX, Feb 11 '20 at 22:56
@braX, I briefly looked around and found a couple other posts referencing variables, but despite trying a couple different ways I wasn't able to make it work. I meant to ask a day or two ago, but is this easy enough for a beginner to accomplish? — SparklePony, Feb 14 '20 at 14:00
@cybernetic.nomad, that's a good suggestion. Being a beginner, the idea of having to change it is daunting. On a positive note, I've updated my code so that it uses offset much more than select and value more than copy/paste :) I'm learning! — SparklePony, Feb 14 '20 at 14:01

score 1 · Accepted Answer · answered Feb 12 '20 at 10:43

without the whole code its difficult to see, but it seems you have your error handler inside the loop? This way it gets executed every time the code in the loop executes. So your trouble is understandable because the currently "ActiveCell" wanders down the rows.

So just move the error handler outside the loop so it doesn't get executed every loop, but only if an error actually occurs. Secondly, try to avoid working with ActiveCell if you have time to optimize your code.

I'm thinking something like this:

Sub GreatSub()

Dim Things As String


'Your code happens here


JumpToHere:
On Error GoTo MyErrorHandler

Do Until IsEmpty(ActiveCell)

    'More awesome code here

Loop

'Possibly more greatness here

Exit Sub

MyErrorHandler:
    Err.Clear
    ActiveCell.Offset(0, 20).Value = ActiveCell.Offset(0, 20).Value + 1
    Resume JumpToHere

End Sub

Like a charm! I had tried moving 'MyErrorHandler', but only moved it further up in the loop. After making your suggested change, it works as intended and in hindsight (of course) it makes total sense why it was "doing it wrong". Thank you! — SparklePony, Feb 12 '20 at 15:44

Error Handling; Count Times Loop Repeated

1 Answers1