Hi Guys,

As a part of a macro that i am doing, the amount of cases before th
week that is been worked on must be counted. Is there a way of coding
pop up to ask the user to enter the date to count back from.

i.e when compling the data for the week ending 30/06/04, all the date
before and including the 23/06/04 must be counted.


Is there any way of doing this through macro?
Any help on this would be great....

Thanks in Advance,
Cathal

--
Message posted from http://www.ExcelForum.com

dim dt as Date
res = InputBox("Please enter last date of count")
dt = Cdate(res)

msgbox application.countif(Range("B:B"),format(dt,"dd/mm/yy"))


--
Regards,
Tom Ogilvy




"CPower " wrote in message
...
Hi Guys,

As a part of a macro that i am doing, the amount of cases before the
week that is been worked on must be counted. Is there a way of coding a
pop up to ask the user to enter the date to count back from.

i.e when compling the data for the week ending 30/06/04, all the dates
before and including the 23/06/04 must be counted.


Is there any way of doing this through macro?
Any help on this would be great....

Thanks in Advance,
Cathal.


---
Message posted from http://www.ExcelForum.com/

Cathal,

I would suggest getting the user tog put the start date in a cell, and then
have a worksheet formula like

=COUNTIF(A1:A100,"<="&B1-7)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CPower " wrote in message
...
Hi Guys,

As a part of a macro that i am doing, the amount of cases before the
week that is been worked on must be counted. Is there a way of coding a
pop up to ask the user to enter the date to count back from.

i.e when compling the data for the week ending 30/06/04, all the dates
before and including the 23/06/04 must be counted.


Is there any way of doing this through macro?
Any help on this would be great....

Thanks in Advance,
Cathal.


---
Message posted from http://www.ExcelForum.com/

Bob i think this would be more suitable, but how do i get a pop u
asking the user to enter the date in cell e.g. S18, in the forma
mm/dd/yyyy, then to enter the amount of rows, with dates before e.g
05/30/2004, into cell S19???

Thanks a mil for this help.
Cathal

--
Message posted from http://www.ExcelForum.com

Cathal,,

I don't know where your dates are, but let's say they are in A1:R20, then
in S19, add

=COUNTIF(A1:R20,"<="&S18-7)

I was suggesting you don't bother with a pop-up, but just tell the user to
put a date in S18, the formula will then automatically count the number.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CPower " wrote in message
...
Bob i think this would be more suitable, but how do i get a pop up
asking the user to enter the date in cell e.g. S18, in the format
mm/dd/yyyy, then to enter the amount of rows, with dates before e.g.
05/30/2004, into cell S19???

Thanks a mil for this help.
Cathal.


---
Message posted from http://www.ExcelForum.com/