Consulting

Results 1 to 14 of 14

Thread: Error Handling (runtime error 1004)

  1. #1
    VBAX Contributor
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    132
    Location

    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:"


        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
    Last edited by Paul_Hossler; 02-01-2018 at 02:33 PM. Reason: Added CODE tags

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    NoMatch:
    On Error Goto 0
    GoTo Weiter:
    HTH. Dave

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    VBAX Contributor
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    132
    Location
    Thank you...did SamT's solution...though it didn't do the trick. still gives the match error

  5. #5
    VBAX Contributor
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    132
    Location

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    check the code around arrTeilnehmerResults

    That looks like an array. Most arrays start counting at 0, but you start r at 1.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    VBAX Contributor
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    132
    Location
    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

    Verweise.jpg
    Last edited by nikki333; 02-03-2018 at 11:28 AM.

  8. #8
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    Maybe use Application.Worksheetfunction.Match? Dave

  9. #9
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  10. #10
    VBAX Contributor
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    132
    Location
    Thanks Dave, that didn't make a difference though

  11. #11
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    https://peltiertech.com/using-refedi...excel-dialogs/
    Note the instructions that references to the refedit control must be removed. HTH. Dave

  12. #12
    VBAX Contributor
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    132
    Location
    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

  13. #13
    VBAX Contributor
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    132
    Location
    Update:
    Removed the Worksheetfuntion object and added an isError...that seems to do the trick

    code.jpg

  14. #14
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    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.
    Be as you wish to seem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •