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.

 

[Joe4]

Is there ever any entry with a length less than 3?
Or a numeric entry with a length more than three were the first number is a 0?

If the answer to both those is "No", then if you do convert them to numbers, you can just use "a number less than than or equal to 999" for that part of the criteria.

 

[Nicha]

Can you give an example with code, please?

 

[Joe4]

Can you give an example with code, please?

It would just be a COUNTIFS formula, with the following criteria to include numbers between 0 and 999:
">0"
and
"<=999"

 

[Nicha]

Thamks.
I can't, for now, format as number. But if there's no other solution I'll have to change the algorithm.

 

[Joe4]

Thamks.
I can't, for now, format as number. But if there's no other solution I'll have to change the algorithm.

Or change your plan, and use VBA to loop through the records to check each one instead of trying to use Excel worksheet functions.