If you've worked with the VLOOKUP function in Excel, you've probably come across cells that have "#N/A" displayed in them. This indicates that the value you're looking for doesn't exist. Here's an example:
Notice that cell D6, the sales rep for Connecticut, shows "#N/A". That cell is using the VLOOKUP function to find the appropriate sales rep for the state (which is in column B). In my sales rep table (G1:H4), I have reps set up for Massachusetts, New Hampshire and Rhode Island, but not for Connecticut. So on row 6, when I try to look up "CT" in the Sales Rep table, it's not found, and I get "#N/A" instead.
Detecting #N/A
It's easy to suppress that #N/A using the IsNA function or the IfNA function. But what about your VBA code? Is is possible to detect the #N/A with code?
Good news. Yes, you can detect #N/A with your VBA code. Knowledge is power, as they say, and in this case, knowing that a cell contains #N/A means you can react appropriately. Let's say, for example, that you're doing some math with the contents of a cell. If the cell has #N/A in it, your code is going to have problems. So you'll want to know ahead of time if the cell contains #N/A.
In my case, I'm going to highlight any cells that contain #N/A in yellow, to draw the users attention. I'll use a simple bit of VBA code to do this. Here's what it looks like:
The CVErr function
Let's break that code down to understand what its doing.
First, you'll see that I've set up a loop, ranging from 2 to 7. I'm using this to iterate through the rows that have data on them - in my case, rows 2 through 7.
Next, I use the "ISERROR" function to check to see if there is an error in column D. I need to do this because the next statement I'm going to use (CVErr), will cause an error in my VBA code on any rows that DON'T have an error - which, in this case, is every row except row 6.
Finally, I use the "CVERR" function to check for a specific error. It allows me to check to see if its the #N/A error, or any one of several other # errors you may have seen, like #DIV/0 or #REF!
The CVERR function has just one argument - the specific error you're looking for. Since I'm looking for the #N/A error, I'll use the "xlErrNA" constant. If the cell contains #N/A, this statement will evaluate as true, thus triggering the next line, which sets the interior color of the cell to yellow.
Here's the result after I run this code:
Nice, right? Now the user can't miss the fact that there is an invalid entry on this list, and will (hopefully!) take the steps to correct it.
You can also use the CVErr function to find the following errors:
Check out Microsoft's page on these errors here: https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/cell-error-values. You can also find additional info about the CVErr function here: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/cverr-function.
New To Me
You may be surprised to learn that CVERR is a new function for me. I've been working extensively with Excel VBA for many years, and never come across this before. That happens more often than you might think. When I teach Excel VBA classes, students often indicate that they expect to leave class with enough knowledge in their heads to bang out any VBA code they'll need. That's not the case with VBA. There are no many commands available in VBA that you'll never learn them all. Even after writing thousands of lines of code, I still find myself googling how-tos. That's the nature of the beast. If you're going to work with VBA, expect to get good at finding answers. You'll need that skill.
Or, call me.
Of course, I'm always happy to discuss your project with you to see how I can help. Most of the people I work with could figure out the VBA code themselves if they only had the time... sound familiar? I'll help you out with the VBA, you focus on what you're best at!
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2023. All rights reserved.