Type Mismatch Error 13 due to "#n/a" written as number in cells

[radonwilson]

I have "#n/a" written in some of my cells(cells formatted as numbers) of column F, I am getting a type mismatch error 13. How to fix?

Sub CheckErrors()

Dim r As Integer

For r = 1 To 10

If RS.Range("F" & r + 1).Value = "#n/a" Then

Debug.Print "Yes"

Else

Debug.Print "No"

End If

Next

End Sub

 

[Trebor76]

Hi radonwilson,

Try this (note I have added the cell address for easier referencing):

Option Explicit
Sub CheckErrors()

    Dim r As Long
    Dim RS As Worksheet
    
    Set RS = ThisWorkbook.Sheets("Sheet1") '<-Sheet name to check. Change to suit if necessary.
    
    On Error Resume Next
        For r = 1 To 10
            If RS.Range("F" & r + 1).Value = CDbl(CVErr(xlErrNA)) Then
                Debug.Print RS.Range("F" & r + 1).Address & " - Yes"
            Else
                Debug.Print RS.Range("F" & r + 1).Address & " - No"
            End If
        Next r
    On Error GoTo 0

End Sub

Regards,

Robert

 

[Peter_SSs]

For r = 1 To 10
If RS.Range("F" & r + 1).Value = "#n/a" Then
Debug.Print "Yes"
Else
Debug.Print "No"
End If
Next

Try this instead of the above

For r = 1 To 10
  If LCase(RS.Range("F" & r + 1).Text) = "#n/a" Then
    Debug.Print "Yes"
  Else
    Debug.Print "No"
  End If
Next

 

[radonwilson]

Try this instead of the above

For r = 1 To 10
  If LCase(RS.Range("F" & r + 1).Text) = "#n/a" Then
    Debug.Print "Yes"
  Else
    Debug.Print "No"
  End If
Next

Thanks Peter

May I have a little explanation on :-

1. the issue in my code.
2. how your code is working?

 

[Peter_SSs]

1. the issue in my code.

The #n/a equates to an error in your code so the cell does not actually have a "value" it has an "error", hence the mismatch

2. how your code is working?

It uses the literal Text in the cell, not the "Value"

If LCase(RS.Range("F" & r + 1).Text) = "#n/a" Then