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 Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

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 *