Filtering with a macro using input box + dates

[olly_w]

Hi guys
I've got a spreadsheet which in column 3, contains date and time e.g. 12/12/200 08:36 . What i want to do is have a macro embedded into a button which prompts the user for 2 dates, start and finish. The macro will then autofilter the records in the spreadsheet to only those in between the pre-entered dates. But my code isn't working, any ideas?


Sub PrintOutByMonth()
Dim FirstDate As Date
Dim LastDate As Date

Application.ScreenUpdating = False

FirstDate = Application.InputBox("Please Enter the First Date in DD MM YYYY hh:ss format: ", "Enter First Date")

LastDate = Application.InputBox("Please Enter the Last Date in DD MM YYYY format: ", "Enter Last Date")

Selection.AutoFilter Field:=1, Criteria1:=">=" & FistDate, Operator:=xlAnd _
, Criteria2:="<=" & LastDate

End Sub


Any help greatly appreciated, thanks in advance
olly

 

[santeria]

This actually Might be covering the same question....

http://www.mrexcel.com/board2/viewtopic.php?t=71438&highlight=filtering+macro

 

[Andrew Poulsom]

Instead of:

Selection.AutoFilter Field:=1, Criteria1:=">=" & FirstDate, Operator:=xlAnd, Criteria2:="<=" & LastDate

try:

Selection.AutoFilter Field:=1, Criteria1:=">=" & CLng(FirstDate), Operator:=xlAnd, Criteria2:="<=" & CLng(LastDate)

Note that I corrected a typo in your code - FistDate to FirstDate. I hope you have Option Explicit at the top of your module and that the typo is only on the Board (not in your actual code).

 

[olly_w]

Sub PrintOutByMonth()
Dim FirstDate As Date
Dim LastDate As Date

Application.ScreenUpdating = False

FirstDate = Application.InputBox("Please Enter the First Date in DD MM YYYY hh:ss format: ", "Enter First Date")
LastDate = Application.InputBox("Please Enter the Last Date in DD MM YYYY format: ", "Enter Last Date")
Selection.AutoFilter Field:=3, Criteria1:=">=" & CLng(FirstDate), Operator:=xlAnd, Criteria2:="<=" & CLng(LastDate)
End Sub


This is my corrected code, option explicit at the top of the module. But when i run it, and i enter the dates, it doesn't return the correct records.
Any help greatly appreciated
cheers
olly

 

[Andrew Poulsom]

What (if any) records does it return?

 

[olly_w]

ok i select the cells i wish to filter (ranging from dates 16/12/2003 to 24/12/2003), and then run the macro, and enter the dates 18/12/2003 to 25/12/2003. But it only returns the first record every time i.e. 16/12/2003.

Is it something to do with the fact that the contents of column C, which is the column i wish to filter on, look like 12/12/2003 08:28 i.e. include a time, but my input box does not cater for the time ?

Bit lost...
thanks in advance, your help is greatly appreciated
cheers
olly

 

[Andrew Poulsom]

With this sample data:

BOOK9
	A	B	C	D
1	Val1	Val2	Date
2	1	10	16/12/030:00
3	2	11	17/12/030:00
4	3	12	18/12/030:00
5	4	13	19/12/030:00
6	5	14	20/12/030:00
7	6	15	21/12/030:00
8	7	16	22/12/030:00
9	8	17	23/12/030:00
10	9	18	24/12/030:00
Sheet1

running your code and entering 18/12/03 and 25/12/03 resulted in rows 3 and 4 being hidden.

You should include a header row in your selection. The dates in column C are true serial dates aren't they? What happens if you filter the list for those dates manually?

 

[olly_w]

i've got my date time field formatted as custom dd/mm/yyyy hh:mm

but it just isn't finding the records ! -it only ever finds the first record, regardless of the dates input..

 

[Andrew Poulsom]

i've got my date time field formatted as custom dd/mm/yyyy hh:mm

So have I in my example.

but it just isn't finding the records ! -it only ever finds the first record, regardless of the dates input..

It worked for me if I include the header in my selection.

In your case the first item is is being treated as a header. As I asked before:

1. The dates in column C are true serial dates aren't they?

2. What happens if you filter the list for those dates manually?

 

[olly_w]

Ok, the dates in column c are taken from excel from other cells using the ()now function, and formatted in the cell as a described earlier.