Inserting countifs formula using vba.....having problem with date

I am trying to put the following formula into an excel sheet via vba.........

Please Login or Register  to view this content.

but its giving me "0"

if i change the formula to this.........it works

Please Login or Register  to view this content.

problem i am having is in my vba code i have a variable

current_month = "9/1/11"

But no matter what i try i can seem to get the date into the right format for this formula to be inserted correctly........any ideas?

Have you looked into the DATE function and the DATEVALUE functions? Also, can you describe how the current_month="9/1/2011" variable is interfering? Can you post your code and sample workbook?

Please Login or Register  to view this content.

The problem is countifs is expecting criteria to be quoted, and your variable was dimensioned as a date data type. Removing the type declaration allowed me to surround the date with quotes as a string.

Had a devil of a time getting it to accept the quotes, so I used Chr(34) which is the ASCII for the quotation.

Please Login or Register  to view this content.

Glad to help! Please mark the thread closed, and if you're so inclined, you can add to my reputation.

For future reference, the best way to get a formula in VBA is to start in Excel

Put the formula you want in the cell where you want it. Check it returns what you expect.
Start the macro recorder.
Press F2 to go into Edit mode
Press Enter to (re-commit) the formula
Stop the macro recorder.

You'll end up with something like:

Please Login or Register  to view this content.

Regards