excel error testing cells without error gives type mismatch (CVErr(xlErrNA))

user_13865097 0
Viewed 326

I'm starting to get the basics of VBA and I've wrote a macro which does the stuff I want (might not be the most elegant way but it works).

One of the things it does is write an excel formula to certain cells in column 8

"=VLOOKUP(RC[-6],TotalsPerClient!C9:C12,4,FALSE)"

This VLOOKUP is going to return some #N/A values and that's fine for me. Afterwards I like to go through the column and remove the errors. I first tried the following:

For i = 1 To LastRow
    If ws4.Cells(i, 8).Value = CVErr(xlErrNA) Then
        ws4.Cells(i, 8).ClearContents
    End If
Next i

However, I get a type mismatch. I understand now that since there is not a formula in every cell some cells do not give an error and hence a type mismatch. I've then used the following:

        If IsError(ws4.Cells(i, 8).Value) Then
            ws4.Cells(i, 8).ClearContents
        End If

Which works and does the trick.

For educational purposes; is there a way to use the Cell.Value = CVErr(xlErrNA) instead of my own solution? Using the CVErr(xlErrNA) seems a bit more specific and would narrow down the scope of what I'm deleting.

error-handling vba excel
1 Answers

Usually the error happens the other way around: there's a Variant/Error value in some Range.Value that's being compared to a string or integer literal, and boom, type mismatch - because Variant/Error cannot be compared to anything other than a Variant/Error.

The inverse is also true, so the comparison explodes with a type mismatch all the same, whenever ws4.Cells(i, 8).Value does not contain an error:

If ws4.Cells(i, 8).Value = CVErr(xlErrNA) Then

The IsError function exists specifically to evaluate whether a Variant has the Error subtype. And once you know you're looking at a Variant/Error, then a comparison to another Variant/Error value is valid, because the types match now - so you can narrow IsError further down and handle specific cell errors differently as needed:

Dim Cell As Range
Set Cell = ws4.Cells(i, 8)

If IsError(Cell.Value) Then
    'Variant/Error comparisons are safe here.
    If Cell.Value = CVErr(xlErrNA) Then
        Cell.ClearContents
    Else
        'cell contains an error, but it's not #N/A.
    End If
Else
    'cell does not contain an error.
    'Variant/Error comparisons would throw a type mismatch here.
End If
user_11885130
Related