Countif in VBA

[Fredek]

Hi guys,

I have a named table ACR_tbl, I would like to count all the times #N/A appear in the table. If the number is greater than 0 I would like to pull up a MsgBox as a warning.

At the moment I am doing this (to an extent with COUNTIF(ACR_tbl,NA()) ), is there a simple way to do it via VBA?

 

[Joe4]

See here: https://www.mrexcel.com/forum/excel-questions/620519-count-number-n-column.html#post3076680

 

[Fredek]

Hi Joe, thank you for the link!

I would like to use:

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

How can I replace columns("O") with my ACR_tbl? I tried something like ActiveSheet.ListObjects("ACR_tbl").DataBodyRange but it doesn't work, any idea?

 

[Joe4]

I don't work much with tables, but why not just enter the columns that the tables uses in there?

 

[Fredek]

Hi Joe,

I will give it a try! How can I specify the sheet I want it to look? I need it to look in columns A-D.

NumberOfNAsOnly = Application.CountIf(Worksheets("ACR Ratings").Columns(D), "#N/A")

I'm getting an object defined error, any ideas?

 

[Joe4]

Sorry, I have been away for a few days.
Try this:

Set MyRange = Worksheets("ACR Ratings").Columns("D:D")
NumberOfNAsOnly = Application.CountIf(MyRange, "#N/A")