Check Excel #N/A using ISNA Function

This Excel tutorial explains how to check Excel #N/A cells using worksheet formula ISNA and VBA Function.

You may also want to read:

Excel IFERROR Function

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

https://support.office.com/en-us/article/Start-the-Power-Pivot-in-Microsoft-Excel-add-in-a891a66d-36e3-43fc-81e8-fc4798f39ea8?ui=en-US&rs=en-US&ad=US

 

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *