PDA

View Full Version : [SOLVED] Error Handling (runtime error 1004)



nikki333
02-01-2018, 12:28 PM
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:"



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

Dave
02-01-2018, 01:53 PM
NoMatch:
On Error Goto 0
GoTo Weiter:
HTH. Dave

SamT
02-01-2018, 04:41 PM
I prefer the looks of

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

nikki333
02-02-2018, 02:19 PM
Thank you...did SamT's solution...though it didn't do the trick. still gives the match error

nikki333
02-02-2018, 02:28 PM
21523

SamT
02-02-2018, 04:40 PM
check the code around arrTeilnehmerResults

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

nikki333
02-03-2018, 10:19 AM
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

21532

Dave
02-03-2018, 10:49 AM
Maybe use Application.Worksheetfunction.Match? Dave

SamT
02-03-2018, 10:57 AM
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.

nikki333
02-03-2018, 11:24 AM
Thanks Dave, that didn't make a difference though

Dave
02-04-2018, 06:01 AM
https://peltiertech.com/using-refedit-controls-in-excel-dialogs/
Note the instructions that references to the refedit control must be removed. HTH. Dave

nikki333
02-04-2018, 12:48 PM
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

nikki333
02-04-2018, 01:15 PM
Update:
Removed the Worksheetfuntion object and added an isError...that seems to do the trick

21540

Aflatoon
02-05-2018, 02:01 AM
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.