# How to manage an error

#### Pierre

##### Member
Hello,

I have a line in my code that may return an error in some specific case. When it happens I'd like to force a value, then continue my code. I read a bit about the "On error GoTo", but seems I don't use it correctly

Here is what I did
Code:
``````Do Until surplus = 0
adr = "AF" & lmin & ":AF" & lmax & ""
If lmax - lmin = 0 Then
min = Application.WorksheetFunction.Subtotal(5, Range(Cells(lmin, "AF"), Cells(lmax, "AF")))
lig = Evaluate("=MATCH(SUBTOTAL(5,(AF" & lmin & ":AF" & lmax & ")),AF" & lmin & ":AF" & lmax & ",0)")
Cells(lig + lmin - 1, 30) = Cells(lig + lmin - 1, 30) + 1
surplus = surplus - 1
Else

On Error GoTo ErrHandler:

Cells(lig + lmin - 1, 30) = Cells(lig + lmin - 1, 30) + 1
surplus = surplus - 1
Exit Do
ErrHandler:
Cells(lig + lmin - 1, 30) = Cells(lig + lmin - 1, 30) + surplus
surplus = 0
Resume Next
End If``````
The line
Code:
``min2 = Evaluate("aggregate(15,6," & adr & "/(" & adr & ">min(" & adr & ")),1)")"``
is the line which can return an error. If there is an error I want to do
Code:
``````              Cells(lig + lmin - 1, 30) = Cells(lig + lmin - 1, 30) + surplus
surplus = 0``````
Then exit my Do and continue the rest of my code.

What am I missing?

Last edited:

#### Hui

##### Excel Ninja
Staff member
The below is what you want
I'm not sure where you want to jump back to, so adjust accordingly

Code:
``````Do Until surplus = 0
adr = "AF" & lmin & ":AF" & lmax & ""
If lmax - lmin = 0 Then
min = Application.WorksheetFunction.Subtotal(5, Range(Cells(lmin, "AF"), Cells(lmax, "AF")))
lig = Evaluate("=MATCH(SUBTOTAL(5,(AF" & lmin & ":AF" & lmax & ")),AF" & lmin & ":AF" & lmax & ",0)")
Cells(lig + lmin - 1, 30) = Cells(lig + lmin - 1, 30) + 1
surplus = surplus - 1
Else

On Error GoTo ErrHandler:

10:                'Placemarker
Cells(lig + lmin - 1, 30) = Cells(lig + lmin - 1, 30) + 1
surplus = surplus - 1
Exit Do

ErrHandler:
Cells(lig + lmin - 1, 30) = Cells(lig + lmin - 1, 30) + surplus
surplus = 0
Goto 10``````

#### Debaser

##### Active Member
You need a Resume statement (you can use On Error Goto -1 but it tends to indicate sloppy code if you need it) to clear an active exception. It would, I think, be simpler here to use a Variant variable and test it with IsError and then proceed accordingly.

#### Pierre

##### Member
Thanks Hui, I managed to make it work based on your proposal

#### Hui

##### Excel Ninja
Staff member
AS debaser described, the On error is still active once triggered
If another error occurs it will go back to 10
so you need to a reset statement as described into the end of the error handling to stop that behaviour