VBA COUNTIF doesn't work with decimal numbers?

[Instantaneo]

Hi,

This question started in another topic but since it "jumps out" of it a bit and I figured out one reason for my code not working I'd like to came up with another topic to discuss this specific issue.

I have the following range (small set):

Values (avg)
0,18869
0,18788
0,16756
0,1146
0,12558
0,18118
0,17898
0,06433
0,1628
0,18928

<tbody>
</tbody>

I'm trying to use this code:

    Dim pivot_total As Integer    Dim range_pivot As String
    Dim lim_max As Double
    Dim ws As Worksheet
    Dim count_1 As Integer

    Set ws = Worksheets("KPI")    ws.Select
    pivot_total = Application.WorksheetFunction.Match("Grand Total", range("K:K"), 0) 
    range_pivot = "T6:T" & (pivot_total - 1) 'this is the range of those values above
    lim_max = 0.2
    count_1 = Application.WorksheetFunction.CountIf(ws.range(range_pivot), "<" & lim_max)
    MsgBox count_1

This code isn't working (it returns always 0). But now imagine this range:

Values (avg)
18
19
17
11
13
17
6
16
18

<tbody>
</tbody>

If I use this code...

    Dim pivot_total As Integer    Dim range_pivot As String
    Dim lim_max As Double
    Dim ws As Worksheet
    Dim count_1 As Integer

    Set ws = Worksheets("KPI")    ws.Select
    pivot_total = Application.WorksheetFunction.Match("Grand Total", range("K:K"), 0) 
    range_pivot = "T6:T" & (pivot_total - 1) 'this is the range of those values above
    lim_max = 15
    count_1 = Application.WorksheetFunction.CountIf(ws.range(range_pivot), "<" & lim_max)
    MsgBox count_1

... my count_1 will return 3.

So, my question is: why isn't COUNTIF working with small values?

Thank you for your help.

 

[wideboydixon]

Perhaps it's not the small numbers but the decimal separator that's causing the confusion. What happens if you use "<0,2" in the countif?

WBD

 

[Instantaneo]

Perhaps it's not the small numbers but the decimal separator that's causing the confusion. What happens if you use "<0,2" in the countif?

WBD

Hi!

Your suggestion, along with the one from CJ in the other topic, already have made me think about the decimal separator. But with your comment I tested other options:

a) I tested "< 0,2" as you said and it returned 0 as usual.
b) I tested "< 0.2" and it returned the correct value.

So it definitely must be related with the decimal separator...

So, how can I solve it? As you can see in the picture below, if I set it manually "lim_max = 0.2" and use lim_max in the expression, it doesn't work because "lim_max" takes the value of 0,2. But if I use "<0.2" directly (which I didn't want, since I'll have this for more than one row), it works...

Thank you for your help!

 

[wideboydixon]

Then you could maybe just force the conversion:

Application.WorksheetFunction.CountIf(ws.range(range_pivot), Replace("<" & lim_max,",","."))

WBD

 

[Instantaneo]

Thank you so much WBD! I tried something like that but apparently in the wrong place. You suggested the right place!