Err Handler not handling Type Mismatch

Question

for some reason a code of mine keeps breaking as 'Error 13 - Type Mismatch' on the following line (where for the record '#N/A' occurs):

For x = FirstDataRow To LastRow
      For y = Crrncy_Rng.Column + 2 To LastColumn
          Select Case .Cells(x, y)
             Case IsError(.Cells(x, y)) = True
                GoTo err_handler
             Case Is = "CHECK"
                .Cells(x, LastColumn + 1) = "CHECK"
                GoTo 20
             Case Else
          End Select
19        Next y
20        Next x    

err_handler:
GoTo 19 '19 is a line where next j is specified
end sub

I was under the impression that including the Error as potential Case would enable me to move next. Is there any other way round it?

Thanks!

I suggest you read the following question and answer: https://stackoverflow.com/questions/1038006/good-patterns-for-vba-error-handling and this page: https://www.cpearson.com/Excel/ErrorHandling.htm on how to correctly handle errors in vba. — Vincent G, Nov 05 '18 at 13:49
Could you show the code in context please - at the moment I'd say there's no `Select Case` or `End Select`. Your `err_handler` appears to be in the main body of the code and not at the end and using `Resume` or `Resume Next` or even `Resume 19` would be better than `GoTo`. — Darren Bartrup-Cook, Nov 05 '18 at 14:01
This is not an error handler. This is an in-line check using the *function* `IsError` - RTE 19 means that you're making an assignment between two types that do not match, and you can't test for that with a function call. — Comintern, Nov 05 '18 at 14:07
@DarrenBartrup-Cook just updated with the full sub section of the code. — GCoxxx, Nov 05 '18 at 15:13

score 1 · Accepted Answer · answered Nov 05 '18 at 15:52

Yes, using Select Case is one way to ignore cells with errors. In your case, if you encounter an error, you don't need to do anything because you want it to go to the next column, which it will do all on its own.

Sub CheckForCheck()

    Dim FirstDataRow As Long
    Dim LastRow As Long
    Dim Crrncy_Rng As Range
    Dim LastColumn As Long
    Dim x As Long, y As Long

    FirstDataRow = 1
    LastRow = 3
    Set Crrncy_Rng = Sheet1.Range("A1")
    LastColumn = 6

    For x = FirstDataRow To LastRow
        For y = Crrncy_Rng.Column + 2 To LastColumn
            Select Case True
                Case IsError(Sheet1.Cells(x, y).Value)
                    'Do nothing here and it will check the next column
                Case Sheet1.Cells(x, y).Value = "CHECK"
                    Sheet1.Cells(x, LastColumn).Value = "CHECK"
                    Exit For 'This moves to the next row and is better than goto
            End Select
        Next y
    Next x

End Sub

I encourage anyone who will listen to not use GOTO. Exit For accomplishes what you want but has the advantage of only moving in one direction.

Err Handler not handling Type Mismatch

1 Answers1