dimanche 19 avril 2015

CountIfs not working with decimals

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!


Aucun commentaire:

Enregistrer un commentaire