VBAProject: Run-time error'13': Type mismatch

Question

I have a macro that used to work, then I decided I want to check a range for a value, and if that value is found, replace it with 0. Now it doesn't work and I keep getting the Runtime error 13 type mismatch on that line no matter what I try. Here is the code that breaks my existing code:

Dim cell As Range
For Each cell In Range("A1:GG1000")
   If cell.Value = "#N/A" Then
      cell.Value = 0
   End If
Next cell

I tried with if cell.value = 2 too just to see if it was a cell type issue with string/numbers, still same error at the same if line.

Any ideas?

Are you really checking if the cell contains the string value #N/A or would you like to check if the cell contains the error #N/A? That is a difference! — Storax
@Storax It turns out that the reason for the mismatch error was that #N/A is an error and not a number or string type format. I just used the IsNA function instead and got the desired result. The reason why it kept throwing errors even when I changed my if statement to a number is because the code looks through a range. If an error is found anywhere in that range it threw the mismatch error at me. — AMP
That's what I tried to say in my answer below and in my comment. BTW, IsNA is a worksheet function. And of course, changing yur statement to a number doe not help because you still have the data type Error for cell.value. — Storax

Storax Storax · Accepted Answer · 2020-03-11T12:39:39

You probably want to do something like this

If TypeName(cell.Value) = "Error" Then
    If cell.Value = CVErr(xlErrNA) Then
        'If cell.Value = "#N/A" Then
        cell.Value = 0
    End If
End If

Another way to check if the cell contains an error is

If IsError(cell.Value) Then
    If cell.Value = CVErr(xlErrNA) Then
        'If cell.Value = "#N/A" Then
        cell.Value = 0
    End If
End If

So, first you check if the cell contains an error and then you check for a specific error

VBAProject: Run-time error'13': Type mismatch

1 Answers