Spaces in Constraint (CountIf) Excel 2003 - solved!

[ChrisGreaves]

I don't know that this is a vagary of Excel's COUNTIF function or an aberration of the VBA interpreter.

lngPublicHolidaysPastDeliveryDate = WorksheetFunction.CountIf(rngPublicHolidays, "> " & Format(dtDelivery, "YYYY-MM-DD"))
The VBA code above, with the space lurking after my GreaterThan sign, always returns zero.

lngPublicHolidaysPastDeliveryDate = WorksheetFunction.CountIf(rngPublicHolidays, ">" & Format(dtDelivery, "YYYY-MM-DD"))
The VBA code above, with the space removed always returns the correct non-zero value.

Who knew that a little ASCII (032) could cause such havoc?

I see nothing in the Excel 2003 help files to cover this, and furthermore, If I go back to my original in-cell calculations and introduce a space, the CountIf function delivers the correct result regardless of the space.

Code: Select all

=COUNTIF(PublicHolidays,"> "&E3)