Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Error Handler not activating

  1. #1
    Board Regular
    Join Date
    Jan 2015
    Location
    Branson, MO
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Error Handler not activating

    Code:
    Private Sub InventoryList_LostFocus()
    Dim i As Range
    If ActiveSheet.Name = "Order Form" Then
        For Each i In ActiveSheet.Range("F2:F" & ActiveSheet.Range("F" & Rows.Count).End(xlUp).Row)
            On Error GoTo Other
            Cells(i.Row, 5) = Sheets("Inventory").Range("A" & WorksheetFunction.Match(i, Sheets("Inventory").Range("C:C"), 0))
            Cells(i.Row, 7) = Sheets("Inventory").Range("D" & WorksheetFunction.Match(i, Sheets("Inventory").Range("C:C"), 0))
            Cells(i.Row, 8) = Sheets("Inventory").Range("F" & WorksheetFunction.Match(i, Sheets("Inventory").Range("C:C"), 0))
            GoTo Either:
    Other:
            On Error GoTo 0
            On Error GoTo ErrExit
            Cells(i.Row, 5) = Sheets("Other").Range("A" & WorksheetFunction.Match(i, Sheets("Other").Range("C:C"), 0))
            Cells(i.Row, 7) = Sheets("Other").Range("D" & WorksheetFunction.Match(i, Sheets("Other").Range("C:C"), 0))
            Cells(i.Row, 8) = Sheets("Other").Range("F" & WorksheetFunction.Match(i, Sheets("Other").Range("C:C"), 0))
    Either:
            Cells(i.Row, 1) = "SO-00000000"
            Cells(i.Row, 2) = "TBD"
        Next i
    End If
    ErrExit:
    End Sub
    My GoTo Other works just fine, but my code breaks (if match isn't found) on the highlighted line. What am I doing wrong?
    code break setting set to "break on unhandled error" (I have tried "break in class module", not sure what that means, but it still doesn't work)
    InventoryList is an ActiveX Comb Box
    Last edited by ndsutherland; Apr 8th, 2015 at 10:03 AM.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,117
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Error Handler not activating

    Perhaps, without On Error.
    Code:
    Private Sub InventoryList_LostFocus()
    Dim i As Range
    Dim Res As Variant
    
        If ActiveSheet.Name = "Order Form" Then
            For Each i In ActiveSheet.Range("F2:F" & ActiveSheet.Range("F" & Rows.Count).End(xlUp).Row)
    
                Res = Application.Match(i, Sheets("Inventory").Range("C:C"), 0)
                If Not IsError(Res) Then
                    Cells(i.Row, 5) = Sheets("Inventory").Range("A" & Res)
                    Cells(i.Row, 7) = Sheets("Inventory").Range("D" & Res)
                    Cells(i.Row, 8) = Sheets("Inventory").Range("F" & Res)
                Else
                    Res = Application.Match(i, Sheets("Other").Range("C:C"), 0)
                    If Not IsError(Res) Then
                        Cells(i.Row, 5) = Sheets("Other").Range("A" & Res)
                        Cells(i.Row, 7) = Sheets("Other").Range("D" & Res)
                        Cells(i.Row, 8) = Sheets("Other").Range("F" & Res)
                    Else
                        Cells(i.Row, 1) = "SO-00000000"
                        Cells(i.Row, 2) = "TBD"
                    End If
                End If
    
            Next i
        End If
    
    End Sub
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    Jan 2015
    Location
    Branson, MO
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error Handler not activating

    Good Thinking. Any idea why my original did not work?

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Error Handler not activating

    This might help explain it: On Error WTF? | Excel Matters

    Essentially your On Error Goto 0 doesn't clear the current exception, so any further error is unhandled.

  5. #5
    Board Regular
    Join Date
    Jan 2015
    Location
    Branson, MO
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error Handler not activating

    That explains it, Thanks Rory.

  6. #6
    Board Regular
    Join Date
    Jan 2015
    Location
    Branson, MO
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error Handler not activating

    Excel kept crashing when I used Norie's code, so I went back to my original but changed "On Error GoTo 0" to "On Error GoTo -1" to clear the error, and it works perfectly. Norie, I did keep your Res idea to only match 2X instead of 6X. Thanks.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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