This Excel tutorial explains how to check Excel #N/A cells using worksheet formula ISNA and VBA Function.
You may also want to read:
Cause of Excel #N/A
#N/A is usually caused by lookup related functions where a value cannot be found in lookup table. Lookup Functions include Vlookup, Match, HLookup, Lookup.
Another common cause is that when you use an Add-In formula, and then send the file to someone who doesn’t have that Add-In. The formula was fine at the time of open the workbook but the formula will turn #N/A once formula is triggered to recalculate.
Check Excel #N/A using worksheet formula
If you want to prevent #N/A in a template with Vlookup formula, you can use IFERROR Function, which captures #N/A as well as other errors. For example
=IFERROR(VLOOKUP(D3,$A$3:$B$7,2,0),”Not found”)
If you try to check if the Cell contains exactly #N/A, use ISNA Function. For example, if A2 contains #N/A then
=ISNA(A2) returns TRUE
Check Excel #N/A using VBA
Excel #N/A is extremely annoying in VBA because it probably causes all the subsequent VBA code to fail.
Method 1
To capture Excel #N/A in VBA, the best way is to use the worksheet function ISNA.
For example
Application.Worksheetfunction.ISNA(A2)
Method 2
Alternatively, you can also use Range.Text
Range("A2").Text = "#N/A"
Range.Text is what the screen displays, do not use Range.Value because it gets the underlying value.
Method 3
Finally, you can also use On Error Resume Next to skip the error.
Outbound References
