Vba: Application.WorksheetFunction.CountIfs with variables

[Nelson78]

Hello everybody.

I'm tinkering with the following situation:

cell.value = Application.WorksheetFunction.CountIfs(Sheets(1).Range("B2:B" & lr), ">=" & date1, Sheets(1).Range("B2:B" & lr), "<=" & date2)

The range Sheets(1).Range("B2:B" & lr) is populated only by dates.
In cell I need to count all the dates included in the range delimited by the variablese date1 and date2.

At the moment the result is 0, but it is not correct.

How can I figure it out?

Thank's in advance.

 

[Alex Blakenburg]

Try converting the dates using CLng

cell.Value = Application.WorksheetFunction.CountIfs(Sheets(1).Range("B2:B" & lr), ">=" & CLng(date1), Sheets(1).Range("B2:B" & lr), "<=" & CLng(date2))

If that doesn't work show us how you have Dim'd date1 and date2 and how you are populating them and check that column B is being recognised as a date (/number) and not as text.

 

[Nelson78]

Absolutely correct.
Thank you.

 

[Alex Blakenburg]

You're welcome. Glad I could help.