Error running this code - If ActiveCell.Value = "#N/A"

[gmcgough600]

Hi, hope someone can help. I'm trying to run this code but when the cell value is "#N/A# I get an error 'type mismatch', note that the cell value is text not the formula =NA().

If ActiveCell.Value = "#N/A" Then
    ActiveCell.ClearContents
ElseIf ActiveCell.Value = "Error" Then
    ActiveCell.ClearContents
Else
End If

I get this error:

"Run-time error '13':


Type mismatch"

Thanks!

 

[FormR]

Hi, you could try this instead.

If ActiveCell.Text = "#N/A" Then

 

[ncrcnbl]

maybe also application.WorksheetFunction.IsNA(activecell.Value)

 

[Fluff]

Another option

If IsError(ActiveCell.Value) Then
    ActiveCell.ClearContents
ElseIf ActiveCell.Value = "Error" Then
    ActiveCell.ClearContents
End If

This will catch any error values not just #N/A

 

[gmcgough600]

Hi, you could try this instead.

If ActiveCell.Text = "#N/A" Then

This works, thanks!