SumIfs function in VBA with Date as one criteria

[Bezzie]

I'm trying to use the SumIfs function from VBA but I'm having trouble with the date criteria.

Background:
Computer Regional Settings date format: dd/MM/yyyy
Excel displays date as: dd/MM/yyyy in the formula bar
dVariable is Dimmed as Date

Sum Range is BD6:BD11006
Criteria 1 Range is D6:D11006 (the above mentioned dates in this column)
dVariable contains date 28/12/2005
Criteria 2 Range is E6:E11006 (this is a text column)
cVariable contains string ABC-DE

I want to use the SumIfs function to sum all the values in range BD6:BD11006 where the date in range D6:D11006 <= dVariable (28/12/2005) and the string in range E6:E11006 = cVariable (ABC-DE) but the date part is not working. If I leave the date part out the function returns the correct answer but when I add the date criteria the function returns 0.

I suspect the problem is the date format (might be wrong!) but can't figure out what to do.

'the ranges used below is ranges in the code; just entered them here as variables for the example sake

nTotal = Application.WorksheetFunction.SumIfs(cSumRange, cCrit1Range, "<=" & dVariable, cCrit2Range, "=" & cVariable)

Any ideas how to do this or how to get the function to convert the date into whatever is needed?

[Norie]

How have you declared dVariable and cVariable?

[Bezzie]

Dim cVariable As String
Dim dVariable As Date

[Norie]

Does this work?

nTotal = Application.WorksheetFunction.SumIfs(cSumRange, cCrit1Range, "<=" & CLng(dVariable), cCrit2Range, "=" & cVariable)

[Bezzie]

Thanks. Looks like it works. Will need to test some more but so far it's looking good.