CODEWITHSUNDEEP
excelvba
SparklePony
SparklePony

Reputation: 9

Error Handling; Count Times Loop Repeated

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.

Upvotes: 0

Views: 270

Answers (1)

Czeskleba
Czeskleba

Reputation: 464

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

Upvotes: 1

Related Questions