Counting integers in VBA

[Alan Beban]

How do I count the number of integers in a range that includes blank strings and truly blank cells?
Thanks?

[leelnich]

Hi Alan -
1) Just integers, or any number?
2) How do you want to handle numbers entered as TEXT?

Perhaps post some sample data in a workbook...

[EssoExplJoe]

You question is not clear. Do you mean that the cells in the range can have integers, text, and decimals? Do you want to just count the number of cells that have numbers that equate to an integer.

[leelnich]

If done in Excel, this would count all numbers NOT entered as text:

C = Application.Count(Range("A2:G101"))

[AlKey]

If you want to use formula try this
Enter array formula in B2

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

=COUNT(IF((MOD(A$2:A$15,1)=0)*(A$2:A$15<>""),A$2:A$15))

v	A	B
1		Count Integers
2	43	7
3	88
4	143
5	25.0842463
6	126.3725726
7	text
8	87
9	71.39662062
10	TEXT
11	94
12	137
13
14	123.3944593
15	62
16

[Alan Beban]

to leelnich Reply #4--I know; I just want integers. And the specific problem I'm dealing with is that

   ElseIf Feature = "Integers" Then
              For i = 1 To NR: For j = 1 To NC
                If IsNumeric(InRngArray(i, j)) Then
                  If Application.RoundDown(InRngArray(i, j), 0) = InRngArray(i, j) Then
                    tempCount = tempCount + 1
                  End If
                End If
              Next: Next
            End If

treats a truly blank cell as 0, an integer.

[Alan Beban]

I have a function that counts the Size (i.e., rows X columns), Numbers, Blanks or Non-Blanks in an array
or range depending on which feature is input, and I want to expand it to count integers, currency, and
probably more features. This the first hurdle.

[Alan Beban]

to leelnich--as text, not a number.

[leelnich]

The Excel ISNUMBER function ignores blanks. Try this:

'...
ElseIf Feature = "Integers" Then
    For i = 1 To NR: For j = 1 To NC
        If Application.IsNumber(InRngArray(i, j)) Then
            If InRngArray(i, j) = Int(InRngArray(i, j)) Then
              tempCount = tempCount + 1
            End If
        End If
    Next: Next
End If
'...

[Alan Beban]

to leelnich--Thanks, perfect! Why the Excel function IsNumber ignores both Blank strings and true Blanks while the VBA Function IsNumeric
ignores only Blank strings baffles me, but I'll take it.

Thanks again.

[leelnich]

...Why the Excel function IsNumber ignores both Blank strings and true Blanks while the VBA Function IsNumeric ignores only Blank strings baffles me...

Amen, brother. Thank you for the rep. - Lee

[Alan Beban]

Although, after having made that comment it occurred to me that once you know about the features of the two functions, and since they are both available
in VBA, it's really more versatile to have two tools instead of one.

[leelnich]

If you want to use formula try this...

@AlKey, you could simplify that to:

=COUNT(IF((MOD(A$2:A$15,1)=0)*(A$2:A$15<>""),1))

...and here's another array formula that uses COUNT's ability to ignore #DIV/0! errors:

=COUNT(1/(MOD(A$2:A$15,A$2:A$15<>"")=0))