Counting if Values in range are numeric with vba

[Nicha]

I need to count combinations (pairs of), caractère 'A' + numerical value with 3 digits. The caractère 'A' is in column (B) and the numerical value is in column (C). The important thing is that you can only count if the value in column (C) is numeric.

Group	B	C	Result
A1	A	123	1
A1	#	000	0
A1	A	546	1
A1	#	901	0
A2	A	678	2
A2	A	678	2
A2	#	500	0
A3	A	532	1
A3	#	000	0
A3	#	000	0
A4	#	1000	0
A4	A	1000	1

I tried to use the expression below, but it doesn't work because column (C) is formatted as (text); but it would work if it were formatted as (number).

    'Rng_Value.cells(i).Value2 -> is the numéric value in column (C)
    vPairs = WorksheetFunction.CountIfs(Rng_Act, "A", Rng_Value, Rng_Value.cells(i).Value2, Rng_Value, ">0")

the code below works for value validation, but doesn't seem very robust since it only checks if we have a 'text' value.

vPairs = WorksheetFunction.CountIfs(Rng_Act, "A", Rng_Value, Rng_Value.cells(i).Value2, Rng_Value, "*")

I am looking for a more precise way to validate that the value is numeric, with 3 characters. Something like:

vPairs = WorksheetFunction.CountIfs(Rng_Act, "A", Rng_Value, Rng_Value.cells(i).Value2, Rng_Value, "isnumeric() and len() = 3")

Conclusion:
1) The most important thing is to count if 'A' + 'Value'. But if it is also possible to validate the length, even better.
2) In the table, for group 'A4' we have the pair 'A +1000' with the count result = 1 (green). Of course, if it was possible to validate the length too, since it is a value with 4 characters, the result should be = 0.

Can anyone help please?
My best regards.

 

[Micron]

IsNumeric will return true if the value is or can be coerced to a number, thus a cell will return True even when it's empty. So I'd use the worksheet function IsNumber, perhaps as IsNumber(Rng_Value.cells(i)). That's a guess because of the limited amount of code provided. You probably don't need to worry about the length of the string if the cell is formatted as text but if you want to, perhaps add >0 along with IsNumber. That should return False if the cell value is text and is 000, and True if text and the value is like 111.

 

[Nicha]

Hi Micron, Manny thanks for your answer, the code is rather complex to paste it here. I just need to know how to apply your suggestions in that particular line where the count happens, inside the countif().
"And the IsNumber(Rng_Value.cells(i)). doesn't work inside the countifs().

 

[Micron]

I don't really know formulas that well, so maybe that's why yours makes no sense to me.
Should it not be "=A", not just "A" ?

WRT next part, first is a range reference (?) but the next part isn't valid criteria?
Rng_Value, Rng_Value.cells(i).Value2

"And" would not be valid inside of the third part? (don't know for sure). It would be
Rng_Value, "isnumeric(Rng_Value), Rng_Value, len(Rng_Value) = 3"
You can't put nothing inside functions like Len and IsNumeric and expect it to ever evaluate to anything.

 

[Nicha]

Thank you once more Micron.
This expression is just pseudo code of what I need to do. I know that these not work. Because I'm count on a range, inside each group, I need something that work through that range. If I was reading line by line, I could test for numeric and length before do the count(). But that's not the case.
I need to do that count at the very beginning of each group.

My best regards.

 

[Nicha]

Hello fellow members.
Can anyone help me to solve this problem, please?

• Count in a range the numeric values. The process looks at the range as a whole, not line by line, therefore I'm using Countif().

Is there any solution for this?
My best regards.

 

[Joe4]

Hello fellow members.
Can anyone help me to solve this problem, please?

• Count in a range the numeric values. The process looks at the range as a whole, not line by line, therefore I'm using Countif().

Is there any solution for this?
My best regards.

If your entries are TRULY numeric, and not numbers entered as text, and they are all greater than 0, you could do that like this (for column P in this example):

=COUNTIF(P:P,">0")

 

[Nicha]

Hi Joe4. Thank you for your help.

Like I've said above:
"I tried to use the expression below, but it doesn't work because column (C) is formatted as (text); but it would work if it were formatted as (number)."

    'Rng_Value.cells(i).Value2 -> is the numéric value in column (C)
    vPairs = WorksheetFunction.CountIfs(Rng_Act, "A", Rng_Value, Rng_Value.cells(i).Value2, Rng_Value, ">0")

As you already notice, that is my main problem.

Thank's.

 

[Joe4]

Sorry, I was just focusing on your post in post 6 were I thought you were stating your current issue. People often ask multiple questions in a thread, or move on to different variations.
I mistakingly took post 6 at face value, and did not realize that you left out critical details in your re-stated question.

I don't know of any way to do that without VBA. The issue is that formulas see the entries as text because, well that is precisely what they are!
Why not convert the entries as numbers, and just change the format of the columns to show leading zeroes (custom format of "000")?
Theh they will actually be numbers, so you can use numeric formulas on them, but they will display as three digits.

 

[Nicha]

No problem Joe4. We are here to discuss solutions with each other.
Beyond the "numeric" validation I need to check the lenght() = 3. As mentioned i the "Conclusion - Point 2", the result in the Group "A4" should be = 0.

Group	B	C	Result
A1	A	123	2
A1	#	000	0
A1	A	546	2
A1	#	901	0
A2	A	678	2
A2	A	678	2
A2	#	500	0
A3	A	532	2
A3	#	000	0
A3	#	000	0
A4	#	1000	0
A4	A	1000	1