Type data mismatch in VBA

[dbrice]

How do I get around this problem?
Some of the cells are text, some formulas, and some numbers (but the numbers are formatted as text).

Sub Apply_Color_To_Text2()


' Declarations and Initialisation
Dim Row As Integer, Col As Integer
Dim CurrentCellText As String
Col = 1


' Loop Through Rows 2 to 5
For Col = 2 To 150
For Row = 24 To 55


' Get Text in Current Cell
CurrentCellText = ActiveSheet.Cells(Row, Col).Value '***The Debugger stops here ***


' Get the Position of the Text SPO
SPOStartPosition = InStr(1, CurrentCellText, "SPO ")

' Colour the Word SPO Red
If SPOStartPosition > 0 Then
ActiveSheet.Cells(Row, Col).Characters(SPOStartPosition, 4).Font.Color = RGB(0, 112, 192)
End If


Next Row
Next Col
' Change color in specific cell
'ActiveSheet.Cells(8, 58).Characters(0, 2).Font.Color = RGB(255, 0, 0)


End Sub

 

[steve the fish]

What is the value of Row and Col at the point it stops and what is in that cell?

 

[dbrice]

Thanks, your hint helped me figure it out. I didnt know that if you hover the mouse over the "Row" or "Col" it would tell you the value of those at the point of the error.

As it turns out the value was #N/A. it came from the formula:
=MATCH("Morn",'Officer List'!$B:$B,0)+MATCH("AD",INDIRECT("'Officer List'!E" & MATCH("Morn",'Officer List'!$B:$B,0) & ":E300"),0)-1, "")=IFERROR(MATCH("Morn",'Officer List'!$B:$B,0)+MATCH("AD",INDIRECT("'Officer List'!E" & MATCH("Morn",'Officer List'!$B:$B,0) & ":E300"),0)-1

I fixed it by handling the error:
=IFERROR(MATCH("Morn",'Officer List'!$B:$B,0)+MATCH("AD",INDIRECT("'Officer List'!E" & MATCH("Morn",'Officer List'!$B:$B,0) & ":E300"),0)-1, "")