I have a dataset which is an extract from a SQL DB. One of the goals of my macro is to check if a specific data field contained "#N/A". I have found that the data as extracted is not recognized by excel as an Excel #N/A error but it seems to think it's a string. If I try to check if a cell contains #N/A on the extracted data I get the following:

• actual worksheet function =ISNA(cell which contains #N/A) results in FALSE
• VBA: If .range(cell which contains #N/A) = CVErr(xlErrNA) Then I get Run-time error '13': Type mismatch

However, if I physically go to the cell with "#N/A", select the cell, hit F2, hit Enter and then rerun the VBA I no longer get the Run-time error. The worksheet formula also returns TRUE after physically selecting the cell and hitting Enter.

I can use VBA to try to look for the String "#N/A" but if I edited the data before running the VBA, I also get a error because now the cell value is the true #N/A error (Error 2042).

Is there a way to automatically get Excel to recognize the string "#N/A" as the Excel error without physically selecting each cell, or manually copy/pasting?

CodePudding user response：

You can convert the string in a way to make IsNA working. Please, test the next way:

  Dim actC As Range: Set actC = ActiveCell
  
  With actC
        .NumberFormat = "General"
        .Value = .Value  'it simulates the editing...
  End With
  Debug.Print WorksheetFunction.IsNA(actC.Value)

actC can be the whole range you want (firstly) converting...

CodePudding user response：

You have a string-that-looks-like-an-error-value. Or you have an actual error value, if Excel has helpfully converted it when you hit F2 and Enter.

The normal approaches apply:

' Check if error
If WorksheetFunction.IsNA(cell.Value) Then

' Check if text-that-looks-like-an-error
ElseIf cell.Value = "#N/A" Then

End If