VBA: CountIfs function problem

[Bezzie]

Can anyone tell me why the following call to the CountIfs function stopped working:

Please Login or Register  to view this content.

cWSName = "LIBA" (variable defined As String)
cRange1 = "Y6:Y11006" (variable defined As String)
cRange2 = "CD6:CD11006" (variable defined As String)
dDate = 13/2/2006 (13-Feb-06 - variable defined As Date)
nExpiries is defined as long

cRange1 contains dates
cRange2 contains values

The very first row (row 6) contains the date (13-Feb-06) in col Y and a value (1151300) in col CD but the CountIfs function returns 0???

If I code the CountIfs function in a cell in the sheet it returns 2 (which is correct as there are 2).

I suspect the problem is with the date format of the date in dDate. If I stop execution and hold the cursor over the variable it displays it as 13/2/2006. What format should the date be? If it is mm/dd/yyyy why doesn't it err out if it sees the date as month 13?

[ragulduy]

well you could try changing to cdate(dDate)...

[Bezzie]

Using CDate(dDate) still returns 0.

I tried Format(dDate, "mm/dd/yyyy") and this returns 2.

This different date formats is very inconvenient .