Count occurences

[Guest]

I've written a quick and easy macro that clears (not delete) a row when it
finds a user prompted date. I now need the macro to run for every occurence
that it can find.
Is there a way to program Excel to count how many times a date appears? I
can then use this count in a loop.
Thanks.

 

[Fredrik Wahlgren]

I've written a quick and easy macro that clears (not delete) a row when it
finds a user prompted date. I now need the macro to run for every occurence
that it can find.
Is there a way to program Excel to count how many times a date appears? I
can then use this count in a loop.
Thanks.

You can use the COUNTIF function.

http://www.mrexcel.com/archive/Dates/30343.html

/Fredrik

 

[Guest]

Thanks. I'm familiar with countif, but how do I incorporate countif in a
macro? I know I could have the macro write the formula on the spreadsheet
and gather the information that I need. Then I can delete the formula. But
is there a cleaner way to do this without writing out a formula to the
spreadsheet and then deleting it?

 

[Tom Ogilvy]

Dim cnt as Long
cnt = application.Countif(Range("A1:A100"),"3/10/2004")


to demo from the immediate window:
? application.Countif(Range("A1:A100"),"3/10/2004")
3

 

[Fredrik Wahlgren]

Thanks. I'm familiar with countif, but how do I incorporate countif in a
macro? I know I could have the macro write the formula on the spreadsheet
and gather the information that I need. Then I can delete the formula. But
is there a cleaner way to do this without writing out a formula to the
spreadsheet and then deleting it?

In that case, use the DATEDIF function
http://www.cpearson.com/excel/datedif.htm

/Fredrik