Excel VBA - interpret "no data" values
I am viewing a table that contains a price column as types double
. I am trying to find a missing value that appears in the spreadsheet as "n/a"
, but that prevents me from interpreting it as a type string
.
The cell containing "n/a"
appears to be of type integer
; how can i read this?
source to share
If all you want to do is check the error value then:
Application.WorksheetFunction.IsNA(rngToCheck.Value)
where rngToCheck
is the cell you want to check for the error value#N/A
(There's a list of worksheet functions that can be called from Excel VBA here )
You can also examine rngToCheck.Text
as it will contain the string "# N / A"
If instead you want to read the formula in the cell that generated #N/A
then rngToCheck.Formula
will do this
source to share
You can prepare the table you want to check as described below and evaluate the special cells containing IS functions, they can be easily checked in True
or False
in VBA. Alternatively, you can write your own VBA function as shown below.
There are Excel functions that check cells for special values, for example:
=ISNA(C1)
(assumes C1 is the tested cell). This will return True
if the cell #N/A
is otherwise False
.
If you want to show whether a range of cells (say " C1:C17
") has any cell containing #N/A
or not, it might seem wise to use:
=if(ISNA(C1:C17); "There are #N/A in one of the cells"; "")
Unfortunately, this is not the case , it will not work as expected. You can only evaluate one cell.
However, you can do it indirectly using:
=if(COUNTIF(E1:E17;TRUE)>0; "There are #N/A in one of the cells"; "")
assuming each of the cells E1
through E17
contains formulas ISNA
for each cell to check:
=ISNA(C1) =ISNA(C2) ... =ISNA(C17)
You can hide a column E
by right-clicking on the column and choosing the "Hide in Excel" context menu to prevent the user of your spreadsheet from seeing the column. They can still be obtained and appreciated even if hidden.
In VBA, you can pass a range object as a parameter RANGE
and evaluate the values individually using a FOR loop:
Public Function checkCells(Rg As Range) As Boolean
Dim result As Boolean
result = False
For Each r In Rg
If Application.WorksheetFunction.IsNA(r) Then
result = True
Exit For
End If
Next
checkCells = result
End Function
This function uses the IsNA () function internally. It should be placed inside a module and then can be used inside a spreadsheet, for example:
=checkCells(A1:E5)
It returns True
if any cell #N/A
, otherwise False
. You must save the workbook as a macro-enabled workbook (extension XLSM
) and make sure the macros are not disabled.
Excel provides more features such as:
ISERROR(), ISERR(), ISBLANK(), ISEVEN(), ISODD(), ISLOGICAL(), ISNONTEXT(), ISNUMBER(), ISREF(), ISTEXT(), ISPMT()
For example, ISERR()
checks for all cell errors except #N/A
, and is useful for detecting calculation errors.
All of these functions are described in the built-in Excel help (press F1, then enter "IS Functions" as the search text for an explanation). Some of them can be used inside VBA, some of them can only be used as cell macro function.
source to share