• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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:
                    min2 = Evaluate("aggregate(15,6," & adr & "/(" & adr & ">min(" & adr & ")),1)")

                    lig = Evaluate("match(aggregate(15,6," & adr & "/(" & adr & ">min(" & adr & ")),1)," & adr & ",0)")
                    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:
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:

                    min2 = Evaluate("aggregate(15,6," & adr & "/(" & adr & ">min(" & adr & ")),1)")

10:                'Placemarker
                    lig = Evaluate("match(aggregate(15,6," & adr & "/(" & adr & ">min(" & adr & ")),1)," & adr & ",0)")
                    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
 
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.
 
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
 
Back
Top