Select Case error 2007

Question

I loop inside an array and check cells values, however, sometimes it can happen that checked cell is "#DIV/0!", then my macro breakes. How to change it? I have tried two things but will no positive effect.

vRange = Range(Cells(ShiftRow, 4), Cells(ShiftRow - 3, TheLastColumn)).Value

Dim i As Integer

For i = 4 To TheLastColumn
    Select Case vRange(4, i)
        Case Is = "#DIV/0!"
            vRange(1, i) = ""
        Case Is = "1"
            vRange(1, i) = vRange(3, i)
        Case Is = "2"
            vRange(1, i) = vRange(3, i - 1)
        Case Is = "3"
            vRange(1, i) = vRange(3, i - 2)
        Case Is = "I"
            vRange(1, i) = vRange(3, i)
        Case Is = "II"
            vRange(1, i) = vRange(3, i - 1)
        Case Is = "III"
            vRange(1, i) = vRange(3, i - 2)
        Case Else
            vRange(1, i) = ""

    End Select
Next i

I also tried: Case CVErr(xlErrDiv0) .

Answer 1

CStr([#DIV/0!]) returns the string "Error 2007" so you can change the code like this

vRange = Range(Cells(ShiftRow - 3, 4), Cells(ShiftRow, TheLastColumn)).Value ' the second cell is usualy the bottom right, but Excel takes care of that
Dim i As Integer

For i = 4 To UBound(vRange, 2)
    Select Case CStr(vRange(4, i))
        Case "1", "I":     vRange(1, i) = vRange(3, i)
        Case "2", "II":    vRange(1, i) = vRange(3, i - 1)
        Case "3", "III":   vRange(1, i) = vRange(3, i - 2)
        Case "Error 2007": vRange(1, i) = "" ' optional because Case Else can handle it
        Case Else:         vRange(1, i) = ""
    End Select
Next i

Answer 2

Try to trap the Error with the On Error Resume Next, and add the following code instead of your Select Case:

Dim i As Integer

On Error Resume Next
For i = 4 To TheLastColumn

    Select Case vRange(4, i)
        Case Is = "#DIV/0!"
            If err.Number = 13 Then ' this is Error number 13 when deviding by zero
                vRange(1, i) = ""
                err.Clear
            End If

        Case Is = "1"
            vRange(1, i) = vRange(3, i)
        Case Is = "2"
            vRange(1, i) = vRange(3, i - 1)
        Case Is = "3"
            vRange(1, i) = vRange(3, i - 2)
        Case Is = "I"
            vRange(1, i) = vRange(3, i)
        Case Is = "II"
            vRange(1, i) = vRange(3, i - 1)
        Case Is = "III"
            vRange(1, i) = vRange(3, i - 2)
        Case Else
            vRange(1, i) = ""

    End Select

Next i