Error Handling (runtime error 1004)

Hey Folks

Having trouble getting my error handling to work.

In this procedure I'm using the match function to write data from an array to a worksheet.

In a few cases there is no match (so it errors with: the match function cannot be assigned), and i'd like the error handler to skip it and go on finding the next match,
but it ignores the "on Error goto NoMatch:"

Code:

---

    With wsMatrix
        For q = 6 To lrMatrix
            For r = 1 To numHelper
                If .Range("B" & q) = arrTeilnehmerResults(r, 1) Then
                    On Error GoTo NoMatch:
                    s = WorksheetFunction.Match(arrTeilnehmerResults(r, 2), rngTrainingNrMatrix, 0)
                    With .Cells(q, s)
                        .Value = arrTeilnehmerResults(r, 4)
                        If arrTeilnehmerResults(r, 4) < .Offset(0, 2) Then
                            .Interior.Color = RGB(255, 174, 172)
                        Else:
                            .Interior.Color = RGB(226, 239, 218)
                        End If
                    End With
                    .Cells(q, s + 1) = arrTeilnehmerResults(r, 3)
                End If
Weiter:
            Next r
        Next q
    End With


NoMatch:
    GoTo Weiter:

End with

---

Code:

---

NoMatch:
On Error Goto 0
GoTo Weiter:

---

HTH. Dave

I prefer the looks of

Code:

---

With wsMatrix
    For q = 6 To lrMatrix
        For r = 1 To numHelper
On Error GoTo Weiter
            If .Range("B" & q) = arrTeilnehmerResults(r, 1) Then
                s = WorksheetFunction.Match(arrTeilnehmerResults(r, 2), rngTrainingNrMatrix, 0)
                With .Cells(q, s)
                    .Value = arrTeilnehmerResults(r, 4)
                    If arrTeilnehmerResults(r, 4) < .Offset(0, 2) Then
                        .Interior.Color = RGB(255, 174, 172)
Else
                        .Interior.Color = RGB(226, 239, 218)
                    End If
                End With
                .Cells(q, s + 1) = arrTeilnehmerResults(r, 3)
            End If
Weiter:
On Error Goto 0

        Next r
    Next q
End With

---

Thank you...did SamT's solution...though it didn't do the trick. still gives the match error

Attachment 21523

check the code around arrTeilnehmerResults

That looks like an array. Most arrays start counting at 0, but you start r at 1.

I'm using Option Base 1 at the beginning of the module, so the array can start with 1 instead of 0.

Regardless of that I dimensioned the array as follows:
ReDim arrTeilnehmerResults(1 To numHelper, 1 To 4) As Variant

What is strange is that the error handling worked with office 2013, not in office
No idea, but therefore I was thinking, could it be related to the libraries used? If so, below are the current libraries

Attachment 21532

Maybe use Application.Worksheetfunction.Match? Dave

Then your array indexing should be good.

you have only showed a small portion of your code. The cause might be in a different portion.

Thanks Dave, that didn't make a difference though

https://peltiertech.com/using-refedi...excel-dialogs/
Note the instructions that references to the refedit control must be removed. HTH. Dave

I have removed the RefEdit Control library, but doesn't make a difference

Really struggling on this one. Even if I put On Error resume next instead of On Error Goto ***, it gets stuck with the match function

Update:
Removed the Worksheetfuntion object and added an isError...that seems to do the trick

Attachment 21540

FYI, using On Error Goto 0 does not clear an active exception, it merely resets the error handler. While an exception is active, any further error will be unhandled no matter what error handler you have set. You needed to use a Resume statement, not Goto.