Autofilter - What's going wrong?

[mwperkins]

Hello,
I am trying to use autofilter to only show records with a date set for during the next 3 weeks.

Two problems:
1. If Date+21 is during the following month, VBA/autofilter appears to try and ignore information saved in local UK date format dd/mm/yyyy and tries to use USA date format mm/dd/yyyy.

2. It does not display data that meets the filter criteria.

Can anyone help?

Example Data

due within 21 days.xls
	A	B	C	D
1	Task	DueDate
2	A	17/03/2003
3	B	18/03/2003
4	C	19/03/2003
5	D	20/03/2003
6	E	21/03/2003
7	F	22/03/2003
8	G	23/03/2003
9	H	24/03/2003
10	I	06/04/2003
11	J	07/04/2003
12	K	08/04/2003
13	L	09/04/2003
14	M	10/04/2003
15	N	11/04/2003
Sheet1

Code I am Using

Sub Due_within_21_days()
    Dim Date_To_Use
    Date_To_Use = Format((Date + 21), "dd/mm/yyyy")
    Columns("A:B").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="<" & Date_To_Use, Operator:=xlAnd
    'Stop
    Columns("A:B").Select
    Selection.Copy
    Range("E1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFilter
End Sub

Many thanks,
Mark

 

[gareth]

Hi There

Maybe this code will work for you dude

_/_/_/_/_/_/_/_/_/_/_/_/_/

Sub Due_within_21_days()
Dim Date_To_Use
Date_To_Use = Format((Date + 21), "dd/mm/yyyy")
MsgBox Date_To_Use
With Columns("A:B")
.Resize(, 1).Offset(, 1).NumberFormatLocal = "dd/mm/yyyy"
.AutoFilter Field:=2, Criteria1:="<" & Date_To_Use, Operator:=xlAnd
.Copy [E1]
.AutoFilter
End With
End Sub

_/_/_/_/_/_/_/_/_/_/_/_/_/

Let us know what happens please

 

[mwperkins]

Gareth,
Thanks, Slight improvement.
The msgbox displays "07/04/2003" but the filter is still looking for < 04/07/2003.

Any further ideas?

Thanks,
Mark

 

[Andrew Poulsom]

Coercing the date to a Long Integer worked for me:

Sub Test()
    Dim Rng As Range
    Dim Date_To_Use
    Date_To_Use = CLng(Date + 21)
    Set Rng = Range("A1:A" & Range("A1").End(xlDown).Row).Resize(, 2)
    With Rng
        .AutoFilter Field:=2, Criteria1:="<" & Date_To_Use, Operator:=xlAnd
        .Copy Range("E1")
        .AutoFilter
    End With
    Application.CutCopyMode = False
End Sub

 

[gareth]

Hi there Again bro

Both my code and Andrews code works perfectly. . . Are you sure the date format you are using is all set to the same value's ?

 

[mwperkins]

Andrew's code works perfectly.

Many thanks,
Mark