How to replace a criteria in AutoFilter by a variable?

[Pana]

Hi!

I have this code:


Sub Macro4()
'
' Macro4 Macro
'

'
Range("A1").Sel ect
Selection.AutoF ilter
ActiveSheet.Ran ge("$A$1:$E$25" ).AutoFilter Field:=1, Criteria1:="16/08/2008", _
Operator:=xlAnd
End Sub


Instead of having to insert "16/08/2008" as Criteria1, I would like to have a variable in there corresponding to the maximum value of a column containing dates.

Could anyone help? That would be extremely helpful and very much appreciated...

[Pana]

In this case the maximum would correspond to the most recent date in the DATE column....

DATE SELLER ITEM QUANTITY PRICE
01.01.2008 John SHOES 10 100
02.01.2008 John TROWSERS 20 110
03.01.2008 John SHIRTS 10 80
04.01.2008 John JEANS 25 90
05.01.2008 John SOCKS 20 95
06.01.2008 John SHOES 32 75
07.01.2008 John TROWSERS 45 65
08.01.2008 John SHIRTS 11 95
09.01.2008 John JEANS 12 12
10.01.2008 John SOCKS 14 13
11.01.2008 John SOCKS 10 100
12.01.2008 John SHOES 20 110
12.01.2008 John TROWSERS 10 80
12.01.2008 John SHIRTS 25 90
13.01.2008 John JEANS 20 95
14.01.2008 John SOCKS 32 75
15.01.2008 John SOCKS 45 65
16.01.2008 John SHOES 11 95
16.01.2008 John TROWSERS 12 12
16.01.2008 John SHIRTS 14 13

[Guido Geurs]

I hope this will help (see attachment):

Sub Macro4()
Dim DateSelect As Date
   DateSelect = Selection
   Range("A1").Select
   Selection.AutoFilter
   ActiveSheet.Range("$A$1:$E$25").AutoFilter Field:=1, Criteria1:=Format(DateSelect, "dd.mm.yyyy"), _
   Operator:=xlAnd
End Sub

[Pana]

Thank you so much for your reply.

It says type mismatch regarding the DateSelect...

[Guido Geurs]

It's working on my PC.
I'm working with Excel 2003 ! You to ?
You must select a cell with a date like "G1" or "H1" !!!!
If You select a text cell than You will have an error.
Can You exclude the selection of a non date cell ?
I will try to find an solution.
If You can't solve it, is it possible to attach Your sheet ?

[Guido Geurs]

I think this will do it=

Sub Macro4()
Dim DateSelect As Date
   On Error GoTo Bad_Selection
   DateSelect = Selection
   Range("A1").Select
   Selection.AutoFilter
   ActiveSheet.Range("$A$1:$E$25").AutoFilter Field:=1, Criteria1:=Format(DateSelect, "dd.mm.yyyy"), _
   Operator:=xlAnd
Exit Sub
Bad_Selection:
   MsgBox "Select a date !"
End Sub

[Pana]

Thank you very much for your reply

I think we are almost there... but I do have a problem with the date selection. In the file you attached ("How to....xls"), the dates are formatted as Text, therefore the filter works (Except for cell G1 which is formatted as Date, but the macro keeps functioning anyways).

My dates are formatted as dates and for some reason the macro doesn't recognise them as dates. So I tried to format them as Text as you did, but then values like 40380.... appear.

You will see a proof in the attachments: I copied identically your macro but because of my formatting of the date it won't work as it should.

Do you know what I can do about this?

Thank you so much.

[Guido Geurs]

After searching the web I have found out that autofiltering on dates isn't so easy.
What I have found is that autofilter uses the US format to filter on dates.
So I have changed the sheet dates (are = date AND time) to "mm/dd/jjjj" format and formated the var "DateSelect " to "mm/dd/yyyy".
And surprisingly it's working ! (see attachment)

Sub Macro5()
Dim DateSelect As String
   On Error GoTo Bad_Selection
   If IsDate(Selection) Then
      DateSelect = Format(Selection, "mm/dd/yyyy")
      Range("A2").AutoFilter Field:=1, Criteria1:=DateSelect, Operator:=xlAnd
   Else
      GoTo Bad_Selection
   End If
Exit Sub
Bad_Selection:
   MsgBox "Select a date !"
End Sub

[Guido Geurs]

If the user want to see the date in an other format (not "mm/dd/yyyy") than You can let the 1e col in the users format and hide a col with the reference to col A but with the US format and filter on that col (col "F" in the attachment).

[Pana]

Thank you sooo much for your help, it is immesnily appreciated