CountIfs not working with decimals

Question

I have a worksheet with about 7000 rows of data which I want to analyze using CountIfs. The data consists of decimal numbers such as "0,05234234" (the decimal "point" is a comma due to country settings). My CountIfs code looks like this:

    For i = 1 To 6
        lastRow = w3.Worksheets(i).Cells(w3.Worksheets(i).Rows.count, 1).End(xlUp).Row
        For j = 1 To n
            tmpRow = 2
            Set rng = w3.Worksheets(i).Range(w3.Worksheets(i).Cells(2, 1 + j), w3.Worksheets(i).Cells(lastRow, 1 + j))
            Do While w1.Worksheets(i).Cells(tmpRow, 1) <> ""
                If tmpRow = 2 Then
                    above = CDbl(-100) 'first element
                Else
                    above = w1.Worksheets(i).Cells(tmpRow - 1, 1)
                End If
                curr = w1.Worksheets(i).Cells(tmpRow, 1)
                ccount = Application.WorksheetFunction.CountIfs(rng, ">" & above, rng, "<=" & curr)
                w1.Worksheets(i).Cells(tmpRow, 1 + j) = ccount
                tmpRow = tmpRow + 1
            Loop
            above = w1.Worksheets(i).Cells(tmpRow - 1, 1)
            w1.Worksheets(i).Cells(tmpRow, 1 + j) = Application.WorksheetFunction.CountIf(rng, ">" & above)
            tmpRow = tmpRow + 1
        Next j
    Next i

The values of "above" and "curr" are evenly spaced like this -2,-1,99,..,0,..,1,99,2.

However, it's not working and it's driving me crazy. The value of "ccount" is 0 for every iteration, except the very last after the loop. For that one, "above" is equal to 2, an integer. It turns out the CountIfs will only work if "above" and "curr" are integers. Why is that happening and how do I fix this?

Thanks!

Answer 1

If the decimal separator is comma due to the locale settings, then implicit conversion of Double to String and explicit conversion with CStr results in a String with comma as decimal separator. But this is not correct for WorksheetFunction.CountIfs. There a decimal point is needed.

So we have to use Str here for conversion. This will ever convert to a String with decimal point.

How you can test this behavior:

Sub test()

 Dim above As Double

 above = 1.23

 'Excel's decimal separator is comma due to the locale settings!

 s = "<" & above ' "<1,23" - implicit conversion Double to String
 Debug.Print s

 s = "<" & CStr(above) ' "<1,23" - explicit conversion with CStr
 Debug.Print s

 s = "<" & Str(above) ' "< 1.23" - explicit conversion with Str
 Debug.Print s

End Sub

But is your logic correct? ...CountIfs(rng, ">" & above, rng, "<=" & curr) - greater than above sounds strange ;-)