CODEWITHSUNDEEP
vbaerror-handling
GCoxxx
GCoxxx

Reputation: 75

Err Handler not handling Type Mismatch

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!

Upvotes: 0

Views: 69

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33175

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.

Upvotes: 1

Related Questions