Select Case error 2007

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) .

4 Answers

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 

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 

Further to Slai's excellent answer, this information might be helpful to future readers.

I have a .csv file - plain text - across which I am running a script to colorize any cell that is not a number. So, for each cell, I am running this function:

Function fnIsNumber(Value) As Boolean fnIsNumber = Evaluate("ISNUMBER(0+""" & Value & """)") End Function 

Some of the cells have the (plain text - remember this is a csv) value #DIV/0!. The above function abends on these cells, with the message Run-time error '13': Type mismatch

In Excel macros, even the string #DIV/0! evaluates to "Error 2007", so I had to modify the function as Slai recommended above:

Function fnIsNumber(Value) As Boolean If CStr(Value) = "Error 2007" Then fnIsNumber = False Else fnIsNumber = Evaluate("ISNUMBER(0+""" & Value & """)") End If End Function 

Cells that contain an error return a different data type (variant of subtype error) than non-error cells. This complicates comparison.

One approach is to nest one case statement within another. The outer case checks for the cell type (error/non-error). The inner case choses what to do, based on non-error value. I've used the TypeName function to determin what data type I am working with.

This example works on the active cell. Update the values to test.

Sub test() ' Update contents of active cell to test. ' Test cases: ' ' Value Output ' 1/0 Error: Error 2007 ' 1 One ' Hi Hi ' <other> Other ' Outer case checks for errors. Select Case TypeName(ActiveCell.Value) Case "Error" ' We have an error. MsgBox "Error: " & CStr(CVErr(ActiveCell.Value)) Case Else ' Non-error. ' Inner case checks cell content. Select Case ActiveCell.Value Case 1 ' User entered 1. MsgBox "One" Case "Hi" ' User entered "Hi". MsgBox "Hi" Case Else ' Unknown user entry. MsgBox "Other" End Select End Select End Sub 

You could improve this sub by adding a second inner case, that checks for different error codes.