Excel vba Custom Filter

[arvarr]

Hi
I am trying to write a macro for custom filter but not sure what is wrong. Any help would be greatly appreciated.
Thanks.

Below is data in worksheet
Column A Column B
Month 1/08/2010 (Have format it to mm/dd/yyyy)

Date Hrs Person
1/02/2010 10 Dave
2/02/2010 5 Mike
3/08/2010 2 Greg
31/08/2010 6 Anthony
2/09/2010 3 Jack


Sub test()

Dim Month As String
Dim Aug09a As String
Dim Aug09b As String

Month = Format(Range("B1"), "mm/dd/yyyy")
Aug09a = Format("08-01-2010", "mm/dd/yyyy")
Aug09b = Format("08-31-2010", "mm/dd/yyyy")

If Month = Aug09a Then

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

Else
End If
End Sub

 

[SkipVought]

Please explain, in plain language, how you want the filter to work.

Did you try macro recording? Please post yuor recorded code ALSO.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
OK, to simplify it, I am trying to get the macro to run the auto filter by referencing to the date limits but it does not seem to filter correctly.
When i check the custom filter, the dates are correct but it does not seem to filter the data within the date limit.
Thanks.

Sub test()
Selection.AutoFilter Field:=1, Criteria1:=">=" & Range("A1"), Operator:=xlAnd _
, Criteria2:="<=" & Range("B1")
End Sub



Column A1 Column B1
1/01/2010 31/01/2010


Date cost hr
1/01/2010 1 1
5/01/2010 1 1
31/01/2010 1 1
2/02/2010 1 1

 

[SkipVought]

Select the date range in your table.

Format > Cells - NUMBER TAB: GENERAL

Every date display should change to a NUMBER...
[tt]
Date cost hr
40179 1 1
40183 1 1
40209 1 1
40211 1 1
[/tt]
Yes? No?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

If that is the issue then a quick way to convert a column of numbers to dates is Data,Text to Columns and at the last step highlight the column and choose Date. May be worth doing this as a precaution whenever your data gets refreshed.

Gavin

 

[SkipVought]

And I sould add, do the same for your DATES in A1 & B1.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
It works when I changed the format to General.

1. How come it does not work when it is in date?
2. So, does this mean I have to change it to number and then after the filter, change it back to dates?

Thanks.

 

[SkipVought]

When you FORMATTED ALL the date ranges did ALL the displays change to NUMBERS?

BTW, my code works regardless of how it's formatted.

Sub test()
    With ActiveSheet
        .Cells.Find("Date", LookAt:=xlWhole).AutoFilter _
            Field:=1, _
            Criteria1:=">=" & .Range("A1"), _
            Operator:=xlAnd, _
            Criteria2:="<=" & .Range("B1")
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
Yes, when i formatted the cells to General, it all changes to number.

But, the code does not work for me.

I just copied your code and try to run it but it does not work.

Not sure what's wrong?

The other thing is cell A1's date say is 01/08/10, it will change to 08/01/10 when i look at the custom filter date range after running the macro.

Thanks.

 

[SkipVought]

thats because Bill Gates & Co are in Seattle Washington USA. Whenever you use a DMY date format, unexpected stuff seems to happen at times.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Oh well, thanks.