How do I skip errors using on error statements?

[yxz152830]

Gurus,
in my case here I want to use on error goto:

For ss_line = 1 To 10
                             
                                Select Case ss_line
                            
                                Case 1 To 9
                               
                                cell1.Offset(0, ss_line + 3) = cell1.Offset(0, ss_line + 3) + _
                                WorksheetFunction.Index(tbl.DataBodyRange, WorksheetFunction.Match(cell.Value, tbl.ListColumns(3).DataBodyRange, 0), WorksheetFunction.Match("SS0" & ss_line, tbl.Range.Rows(1), 0))
                                                          on error goto notfound   
                                Case 10
                                cell1.Offset(0, ss_line + 3) = cell1.Offset(0, ss_line + 3) + _
                                WorksheetFunction.Index(tbl.DataBodyRange, WorksheetFunction.Match(cell.Value, tbl.ListColumns(3).DataBodyRange, 0), WorksheetFunction.Match("SS" & ss_line, tbl.Range.Rows(1), 0))
                                End Select
notfound:
                                Next ss_line

code does not seem to be working. Where do I place the on error statement or how do I find the right position to place it? Thank you!

 

[mikerickson]

I wouldn't use On Error to trap when the terms aren't found.

Dim rowIndexNum as Variant, colIndexNum as Variant
'...
For ss_line = 1 to 10
    rowIndexNum = Application.Match(cell.Value, tbl.ListColumns(3).DataBodyRange, 0)
    colIIndexNum = Application.Match("SS" & Format(ss_line, "00"), tbl.Range.Rows(1), 0)
    If IsError(rowIndexNum) or IsError(colIndexNum) Then
        MsgBox "not there"
    Else
        cell1.Offset(0, ss_line + 3) = cell1.Offset(0, ss_line + 3) + tbl.DataBodyRange.Cells(rowIndexNum, colIndexNum)
    End If
Next ss_line

Note that Application.Match and WorksheetFunction.Match act differently when the search term is not found.

 

[yxz152830]

I wouldn't use On Error to trap when the terms aren't found.

Dim rowIndexNum as Variant, colIndexNum as Variant
'...
For ss_line = 1 to 10
    rowIndexNum = Application.Match(cell.Value, tbl.ListColumns(3).DataBodyRange, 0)
    colIIndexNum = Application.Match("SS" & Format(ss_line, "00"), tbl.Range.Rows(1), 0)
    If IsError(rowIndexNum) or IsError(colIndexNum) Then
        MsgBox "not there"
    Else
        cell1.Offset(0, ss_line + 3) = cell1.Offset(0, ss_line + 3) + tbl.DataBodyRange.Cells(rowIndexNum, colIndexNum)
    End If
Next ss_line

Note that Application.Match and WorksheetFunction.Match act differently when the search term is not found.

hi I tried application.match the thing is my calculation contains other variables so it's gonna pop type mismatch error regardless. Anyway to skip this alert?

 

[Zyndstoff]

On error Resume Next
For ss_line = 1 To 10
                             
                                Select Case ss_line
                            
                                Case 1 To 9
                               
                                cell1.Offset(0, ss_line + 3) = cell1.Offset(0, ss_line + 3) + _
                                WorksheetFunction.Index(tbl.DataBodyRange, WorksheetFunction.Match(cell.Value, tbl.ListColumns(3).DataBodyRange, 0), WorksheetFunction.Match("SS0" & ss_line, tbl.Range.Rows(1), 0))
                                Case 10
                                cell1.Offset(0, ss_line + 3) = cell1.Offset(0, ss_line + 3) + _
                                WorksheetFunction.Index(tbl.DataBodyRange, WorksheetFunction.Match(cell.Value, tbl.ListColumns(3).DataBodyRange, 0), WorksheetFunction.Match("SS" & ss_line, tbl.Range.Rows(1), 0))
                                End Select
Next ss_line
On Error Goto 0

 

[mikerickson]

Anyway to skip this alert?

Remove the MsgBox line.