0

I have been trying to remove 0 and #N/A" from the cell but when i run the code it gives me an error Type Mismatch.

Can someone please look into the issue that why an error is occur. Your help will be appreciated.

 If sht3.Range("B4") = 0 Or "#N/A" Then
 sht3.Range("B4") = ""
 End If
Improve this question
1
  • 1
    stackoverflow.com/questions/5143402/…. You can use Application.IsNA but you need to move the check for 0 into an ElseIf (assuming that the cell can't be any other type of error, e.g. #DIV/0). Commented Mar 10, 2021 at 18:32

1 Answer 1

Reset to default
1

The error #N/A is not text. Attempting to compare an error value to a number or text is an automatic type mismatch.

You can use Application.IsNA, but note that you need to move the check for 0 into an ElseIf (this assumes the cell cannot be any other type of error, e.g. #DIV/0).

With sht3.Range("B4")
    If Application.IsNA(.Value) Then
        .Value = ""
    ElseIf .Value = 0
        .Value = ""
    End If
End With
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@Mento note that Application.IsNA is getting Excel's calculation engine involved to evaluate the ISNA Excel function. The all-VBA equivalent would be to check whether IsError(.Value) (true if cell value is a Variant/Error, which #N/A is), and then to compare that error value with CVErr(xlErrNA) to branch execution when the error is specifically a #N/A error. That said the biggest problem with your original code is that the expression "If X = A Or B" should read "If X = A Or X = B" to work as intended, otherwise you're bitwise-Or'ing the B expression with the Boolean result of =A.
^^^^ @Mento, yeah that's what the linked question in my first comment demonstrates. So that's the more thorough approach, agree with Mathieu.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.