On Error Goto Errhandler: not working

[CodyMonster]

I'm kind of surpised this doesn't work.
I'm getting the 91 error when WellIDRow doesn't find what it's looking for, which I expected, that is why I put in the error handing. If it can't find the number then it should skip and go to the next "a".
However, I'm still getting the "pop-up" of the Run-time error '91':
I thought it would go right to ErrHandler: when the error happens.. but it isn't.
anyone know what I'm doing wrong here?


Thanks

With id.Range("C:C")
    Set SAPWell = .Find("SAP Well Code", LookIn:=xlValues)
    SAPwellRow = SAPWell.Row
    LastRowSAP = id.Cells(Rows.Count, "C").End(xlUp).Row
End With


For a = SAPwellRow + 1 To LastRowSAP


On Error GoTo ErrHandler:


    SAPWC = id.Cells(a, 3).Value
    If IsEmpty(SAPWC) = False Then
        WellIDRow = wi.Range("C:C").Find(SAPWC, LookIn:=xlValues).Row
        wi.Cells(WellIDRow, 13).Value = Format(id.Cells(a, 8).Value, "#0.0000")
        wi.Cells(WellIDRow, 13).NumberFormat = "#0.0000"
        wi.Cells(WellIDRow, 1).Value = id.Cells(a, 1).Value
        Else
    End If


ErrHandler:
    If Err.Number = 91 Then
    Err.Clear
    End If


Next


On Error GoTo 0

 

[Eric W]

Two possibilities.

First, when you use an error handler, you are required to end it with Resume, Resume Next, or Resume Location. Otherwise, the error handler assumes you are still in the error handler. So you're probably getting a 91, jumping to ErrHandler:, going back to the top of the loop, then you're getting ANOTHER 91, and then you get the error. You could rewrite your code something like:

Sub test2()

    With ID.Range("C:C")
        Set SAPWell = .Find("SAP Well Code", LookIn:=xlValues)
        SAPwellRow = SAPWell.Row
        LastRowSAP = ID.Cells(Rows.Count, "C").End(xlUp).Row
    End With

    On Error GoTo ErrHandler:
    
    For a = SAPwellRow + 1 To LastRowSAP

        SAPWC = ID.Cells(a, 3).Value
        If IsEmpty(SAPWC) = False Then
            WellIDRow = wi.Range("C:C").Find(SAPWC, LookIn:=xlValues).Row
            wi.Cells(WellIDRow, 13).Value = Format(ID.Cells(a, 8).Value, "#0.0000")
            wi.Cells(WellIDRow, 13).NumberFormat = "#0.0000"
            wi.Cells(WellIDRow, 1).Value = ID.Cells(a, 1).Value
        Else
        End If
ResumeHere:
    Next a
    
    Exit Sub


ErrHandler:
    If Err.Number = 91 Then Err.Clear
    Resume ResumeHere:

End Sub

The other option is to change how you do the Find. Try:

    If IsEmpty(SAPWC) = False Then
        Set FindCell = wi.Range("C:C").Find(SAPWC, LookIn:=xlValues)
        If Not FindCell Is Nothing Then
             WellIDRow = FindCell.Row
             wi.Cells(WellIDRow, 13).Value = Format(ID.Cells(a, 8).Value, "#0.0000")
             wi.Cells(WellIDRow, 13).NumberFormat = "#0.0000"
             wi.Cells(WellIDRow, 1).Value = ID.Cells(a, 1).Value
         End If
     Else
     End If

This should prevent the error altogether.

 

[CodyMonster]

yay.. it works.
I used your second method. cleaner..
Thanks so much..

 

[Eric W]

Glad it works for you. And when you end up needing an error handler in the future, you know a bit more about it.