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.
