Macro halts on probable data error

[Dan E]

I have a macro (see below) that runs OK in 2 workbooks, and has run OK on
previous editions of this particular workbook (the workbook is a fortnightly
schedule, and is updated every 2 weeks with fresh data). This time, it
stalls with a Run-time error '13' - Type mismatch. The line it stops on is
the "ElseIf Len(cell.Value) = 2 Or Len(cell.Value) = 3 Then" in Sub
Color_Text. When I step through the macro, it loops smoothly many times in
the sub (Color_Text) where it eventually stops on an error, and because the
loop happens so many times, I haven't yet got to the iteration on which it
stops. I suspect that the error is caused by a cell containing something
unexpected, and I know there's a way to get a pop-up showing the location in
the sheet at which the error occurs, but for the life of me I can't find how
to detect the cell where it stalls. Would some kind soul please enlighten
me? All help gratefully received and acknowledged!

TIA

Macro follows:-
___________________________
Sub Main_REHAB()
ActiveSheet.Unprotect
Color_Text
myRows
CC_OT
ALL_OT
ActiveSheet.Protect
End Sub

Sub Color_Text()
Dim cell As Range
Dim col As Integer
On Error GoTo ws_next
For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex = 1 Or _
cell.Interior.ColorIndex = 15 Then
ElseIf Len(cell.Value) = 2 Or Len(cell.Value) = 3 Then
Select Case LCase(cell.Value)
Case "umr": col = 40
Case "ra": col = 38
Case "rb": col = 35
Case "rc": col = 36
Case "cs": col = 37
Case "rf": col = 38
Case "rg": col = 35
Case "rh": col = 36
Case "r1": col = 38
Case "r2": col = 35
Case "r3": col = 36
Case "r4": col = 24
Case "r5": col = 43
Case "r6": col = 22
Case "r8": col = 38
Case "r9": col = 35
Case "r10": col = 36
Case "eto": col = 0
Case Else: col = cell.Interior.ColorIndex
End Select
cell.Interior.ColorIndex = col
End If
ws_next:
Next
ws_exit:
End Sub

Sub myRows()

Dim oRow As Range
Dim cell As Range
On Error GoTo ws_next2
For Each oRow In ActiveSheet.UsedRange.Rows
If Cells(oRow.Row, "AT").Value = "X" Then
For Each cell In Cells(oRow.Row, "AT").EntireRow.Cells
If IsEmpty(cell.Value) Then
cell.Interior.ColorIndex = xlColorIndexNone
End If
Next cell
End If
ws_next2:
Next oRow

End Sub

Sub CC_OT()

Dim oRow As Range
Dim cell As Range

On Error GoTo ws_next3
For Each oRow In ActiveSheet.UsedRange.Rows
If Cells(oRow.Row, "AT").Value = "X" Then
Cells(oRow.Row, "BA").Value = ""
If Cells(oRow.Row, "W").Value = "1" Then
Call week1(oRow)
ElseIf Cells(oRow.Row, "W").Value = "2" Then
Call week2(oRow)
ElseIf Cells(oRow.Row, "W").Value = "3" Then
Call bothweeks(oRow)
End If
End If
ws_next3:
Next oRow

End Sub

Sub week1(oRow As Range)
If Cells(oRow.Row, "AW").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
End If
End Sub

Sub week2(oRow As Range)

If Cells(oRow.Row, "AX").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AX").Value - 40
End If
End Sub

Sub bothweeks(oRow As Range)

Cells(oRow.Row, "BA").Value = ""
If Cells(oRow.Row, "AW").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
End If
If Cells(oRow.Row, "AX").Value > 40 Then
Cells(oRow.Row, "BA").Value = (Cells(oRow.Row, "BA").Value + _
(Cells(oRow.Row, "AX").Value - 40))
End If
End Sub

Sub ALL_OT()

Dim oRow As Range
Dim cell As Range

On Error GoTo ws_next4
For Each oRow In ActiveSheet.UsedRange.Rows
If Cells(oRow.Row, "AT").Value = "X" Then
Cells(oRow.Row, "BB").Value = ""
If Cells(oRow.Row, "AW").Value > 40 Then
Cells(oRow.Row, "BB").Value = Cells(oRow.Row, "AW").Value -
40
End If
If Cells(oRow.Row, "AX").Value > 40 Then
Cells(oRow.Row, "BB").Value = (Cells(oRow.Row, "BB").Value +
_
(Cells(oRow.Row, "AX").Value - 40))
End If
End If
ws_next4:
Next oRow

End Sub
___________________
Macro ends

 

[Dan E]

After posting this, I found a formula cell with #N/A, unhid the cells it was
getting data from, found one cell with a #N/A, clicked on the cell and
looked at it in the cell contents bar at the top - seemed to be a space, so
I deleted the space, the #N/As disappeared, and the macro then ran OK.
Now - looking at the macro, I can't see why a space would give an error 13
in the Color_Text sub. Any help much appreciated and acknowledged!

TIA,

Dan

 

[Earl Kiosterud]

Dan,

I haven't looked too closely at your code, but one possible debugging tool
is to add Cell.Select just before that line, then when it has failed, go see
what the cell contains. It might have an error in it.

 

[Dan E]

Many thanks, Earl. I'll try it. See my second message, also - any
suggestions for why a space could stop it?

TIA

Dan

Dan,

I haven't looked too closely at your code, but one possible debugging tool
is to add Cell.Select just before that line, then when it has failed, go
see what the cell contains. It might have an error in it.

 

[Earl Kiosterud]

Dan,

The #N/A coming from the formula would be causing the error in the macro, I
should think, not the space in the referred cell. What is the formula in
the cell? What did the referred cell contain, just a space?
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

After posting this, I found a formula cell with #N/A, unhid the cells it
was getting data from, found one cell with a #N/A, clicked on the cell and
looked at it in the cell contents bar at the top - seemed to be a space,
so I deleted the space, the #N/As disappeared, and the macro then ran OK.
Now - looking at the macro, I can't see why a space would give an error 13
in the Color_Text sub. Any help much appreciated and acknowledged!

TIA,

Dan

 

[Dan E]

Earl - yes, there was only a space. The formula is of this form:-

=IF(OR(B8="",B8="-"),"-",IF(RIGHT(B8,2)="OR","-",LOOKUP(B8,Crib!$A$1:$A$247,Crib!$B$1:$B$247)))

and if B8 contains a space, the formula generates a #N/A. Any suggestions
for how to add to this formula to get rid of spurious spaces very welcome!

TIA

Dan

Dan,

The #N/A coming from the formula would be causing the error in the macro,
I should think, not the space in the referred cell. What is the formula
in the cell? What did the referred cell contain, just a space?

 

[Dave Peterson]

When there's an error in the cell, then cell.value will cause an error.

You have at least a couple of choices.

#1. Look at cell.text (len(cell.text) will be ok)

..text is what appears in the cell. If you have a numeric entry of 3 in the
cell, but format it to show 2 decimal places, then the cell.value = 3, but
cell.text = "3.00"

#2. You could test the cell to see if it is an error:

if iserror(cell.value) then
'do nothing
else
'do your real code here
end if

 

[Dan E]

That's neat! (No. 2) (So call me old-fashioned! )

Many thanks Dave,

Dan