vba - 选择案例错误 2007

Question

我在数组内循环并检查单元格值，但是，有时检查的单元格可能是“#DIV/0!”，然后我的宏就会中断。如何改变呢？我尝试了两种方法，但没有任何积极效果。

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

我也尝试过:Case CVErr(xlErrDiv0)。

Answer 1

CStr([#DIV/0!]) 返回字符串 "Error 2007"，因此您可以像这样更改代码

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