To cut the long story short, I can't seem to get a very simple VBA code containing WorksheetFunction.CountIf and criteria range with decimal numbers to work properly. I encountered the problem while working on a project, and have tried the following test code in Excel 2007 and Excel 2011 for Mac. In both cases the message box reads "0". However, if I replace the criteria range with number (for example "0,5") I get the correct result (for example "3"). Same applies if I type a whole number in the criteria range (for example, type "2" instead of 0,5 in the cell A4).
I have a worksheet containing whole and decimal numbers in a column A, and I have the following VBA code:
Sub countiftest()
MsgBox WorksheetFunction.CountIf(Range("A1:A8"), "=" & Range("A4").value)
End Sub
It should be clear that the message box should print number 1 or higher. However, if the worksheet has a decimal number in the cell A4, I get 0 every time.
I use comma as a decimalseparator on my mac, and dot on my PC. If I use the similar CountIf function on the worksheet as a standard Excel formula, it returns the correct result.
Any thoughts?
I have a worksheet containing whole and decimal numbers in a column A, and I have the following VBA code:
Sub countiftest()
MsgBox WorksheetFunction.CountIf(Range("A1:A8"), "=" & Range("A4").value)
End Sub
It should be clear that the message box should print number 1 or higher. However, if the worksheet has a decimal number in the cell A4, I get 0 every time.
I use comma as a decimalseparator on my mac, and dot on my PC. If I use the similar CountIf function on the worksheet as a standard Excel formula, it returns the correct result.
Any thoughts?
