count the number of #N/A in a column

[nicnad]

Hi,

I am trying to count the number of #N/A in a the column O.

I tried the following code but it is not working :

sub test()
v = Application.WorksheetFunction.SumProduct(--Application.WorksheetFunction.IsNA("O:O"))
msgbox v
end sub

Any idea how to fix this?

 

[T. Valko]

Hi,

I am trying to count the number of #N/A in a the column O.

I tried the following code but it is not working :

sub test()
v = Application.WorksheetFunction.SumProduct(--Application.WorksheetFunction.IsNA("O:O"))
msgbox v
end sub

Any idea how to fix this?

Like this...

=COUNTIF(O:O,#N/A)

I don't know how to get it in code, though!

 

[Trevor G]

Here is the userside formula perhaps you could use that or adapt it in the code side but recording a macro to use to create the formula.

Excel Workbook
	A	B	C	D
1				4
2	#DIV/0!
3		#N/A
4		#N/A
5		#N/A
6		#N/A
Sheet2

 

[nicnad]

In your example, the userside formula could also be

=sumproduct(--isna(B:B))

But I am looking for the vba code of it.

 

[Jonmo1]

Are you wanting to count specifically #N/A errros, or is it sufficient to count ALL errors, #N/A / #VALUE! / #DIV/0! etc.. ??

Try

x = Range("O:O").SpecialCells(xlCellTypeFormulas, 16).Count

 

[nicnad]

Your code did the trick.

Thank you very much!

 

[Jonmo1]

Glad to help, thanks for the feedback

 

[Rick Rothstein]

Glad to help, thanks for the feedback

If there is any chance other errors might exist and you really only wanted to count the #N/A errors, this code line can be used..

NumberOfNAsOnly = Application.Countif(Columns("O"),"#N/A")

Note: This is Biff's formula translated into VBA code.