When A Formula Errs (Error Handling?)

[Ark68]

Consider this code:

Private Sub UserForm_Initialize()
    Dim sed As Long
    Dim srng As Range
    Dim cloc As Range
    
    Me.Frame1.Caption = "Location:  " & ws9.Name & "  " & cell.Address
    Me.f1name = cval2
    hh = Application.WorksheetFunction.Index(ws_staff.Range("D4:D33"), WorksheetFunction.Match(cval2, ws_staff.Columns(4), 0))
    On Error Resume Next
    Me.f1shift = hh
    'Stop
    qrw = cell.Row
    trid = ws_working.Cells(qrw, 1)
    tpn = ws_working.Cells(qrw, 3)
    tpgm = ws_working.Cells(qrw, 5)
    tfac = ws_working.Cells(qrw, 4)
    ttms = Format(ws_working.Cells(qrw, 6), "h:mmA/P") & " - " & Format(ws_working.Cells(qrw, 7), "h:mmA/P")
    assgnt = ws_working.Cells(10, cell.Column)
    assgnt = ws_working.Cells(10, cell.Column)
    If IsNumeric(assgnt) = True Then assgnt = "TrnService" & assgnt
    Me.f1assgnt = assgnt
    Me.f1detail1 = trid & "  " & tpn & "  " & tpgm
    Me.f1detail2 = ttms & "  " & tfac
    Me.f1name.Locked = True
    Me.f1assgnt.Locked = True
    Me.f1shift.Locked = True
    Me.f1detail1.Locked = True
    Me.f1detail2.Locked = True
    
    'create combobox list
    sed = ws_staff.Cells(ws_staff.Rows.Count, "V").End(xlUp).Row
    Set srng = ws_staff.Range("V4:V" & sed)
    For Each cloc In srng
        With Me.ComboBox1
            .AddItem cloc.Value
        End With
    Next cloc
    
End Sub

The line in blue will err if there is no match. The error can be expected, so if that line errs, I want the value of hh to be "NA"

Can anyone suggest an improvement to my code to accomplish what I need to do for the value of hh?

 

[6StringJazzer]

I would do this.

Private Sub UserForm_Initialize()
    Dim sed As Long
    Dim srng As Range
    Dim cloc As Range
    
    Me.Frame1.Caption = "Location:  " & ws9.Name & "  " & cell.Address
    Me.f1name = cval2
    hh = "NA"
    On Error Resume Next
    hh = Application.WorksheetFunction.Index(ws_staff.Range("D4:D33"), WorksheetFunction.Match(cval2, ws_staff.Columns(4), 0))
    On Error GoTo 0
    On Error Resume Next ' why do you have this here? Potentially dangerous.
    Me.f1shift = hh
    'Stop
    qrw = cell.Row
    trid = ws_working.Cells(qrw, 1)
    tpn = ws_working.Cells(qrw, 3)
    tpgm = ws_working.Cells(qrw, 5)
    tfac = ws_working.Cells(qrw, 4)
    ttms = Format(ws_working.Cells(qrw, 6), "h:mmA/P") & " - " & Format(ws_working.Cells(qrw, 7), "h:mmA/P")
    assgnt = ws_working.Cells(10, cell.Column)
    assgnt = ws_working.Cells(10, cell.Column)
    If IsNumeric(assgnt) = True Then assgnt = "TrnService" & assgnt
    Me.f1assgnt = assgnt
    Me.f1detail1 = trid & "  " & tpn & "  " & tpgm
    Me.f1detail2 = ttms & "  " & tfac
    Me.f1name.Locked = True
    Me.f1assgnt.Locked = True
    Me.f1shift.Locked = True
    Me.f1detail1.Locked = True
    Me.f1detail2.Locked = True
    
    'create combobox list
    sed = ws_staff.Cells(ws_staff.Rows.Count, "V").End(xlUp).Row
    Set srng = ws_staff.Range("V4:V" & sed)
    For Each cloc In srng
        With Me.ComboBox1
            .AddItem cloc.Value
        End With
    Next cloc
    
End Sub

 

[rlv01]

Another option.

Replace this.

    hh = Application.WorksheetFunction.Index(ws_staff.Range("D4:D33"), WorksheetFunction.Match(cval2, ws_staff.Columns(4), 0))

With this:

    If Not IsError(Application.Index(ws_staff.Range("D4:D33"), Application.Match(cval2, ws_staff.Columns(4), 0))) Then
        hh = Application.Index(ws_staff.Range("D4:D33"), Application.Match(cval2, ws_staff.Columns(4), 0))
    Else
        hh = "NA"
    End If

Note that the replacement code does not contain the term "WorksheetFunction"

 

[Ark68]

That worked! Thanks again Jeff, you help is always appreciated.

 

[6StringJazzer]

Another option.

I like this. It's almost always better to detect an error rather than letting On Error do it for you.