Autofilter date range from two cell values

[Ax8472]

Hi everyone.

I'm hoping someone can be kind enough to look into this for me, I already asked on Ozgrid but I think I have people stumped.

I'm looking to VBA some code for an autofilter to filter some dates. The dates change every time. I can set these dates into some cells, they're currently set as mm/dd/yyyy format dates.

Sub Macro15()
'
' Macro15 Macro
'


'
    ActiveSheet.Range("$K$20:$Q$58").AutoFilter Field:=3, Criteria1:= _
    ">=" & Worksheets(1).Range("AA16"), Operator:=xlAnd, Criteria2:=Worksheets(1).Range("AB14")
End Sub

See the first date? Works like a charm. ">=" & Worksheets(1).Range("AA16").

But the second one doesn't... Criteria2:=Worksheets(1).Range("AB14") if I change this one to a static date then the code works fine.

I then thought maybe it was the source data of the cell used for the second criteria, but I used those cells for the first criteria and it worked fine, so it's nothing to do with the cells.

if anyone can help me with this, it'd be such a huge help! <3


EDIT : Forgot to say. When I say the code doesn't work, what I mean is, it filters with no results. No error as such but it just gives me nothing. If I change it to a date, it shows the correct data. I'm using Excel 2007.

Capture.jpg

[mdmackillop]

Use CDbl for dates. Also, you're missing < in the second criteria

   ActiveSheet.Range("$K$20:$Q$58").AutoFilter Field:=3, Criteria1:= _
    ">=" & CDbl(Worksheets(1).Range("AA16")), Operator:=xlAnd, Criteria2:="<" & CDbl(Worksheets(1).Range("AB14"))

[Ax8472]

Use CDbl for dates. Also, you're missing < in the second criteria

   ActiveSheet.Range("$K$20:$Q$58").AutoFilter Field:=3, Criteria1:= _
    ">=" & CDbl(Worksheets(1).Range("AA16")), Operator:=xlAnd, Criteria2:="<" & CDbl(Worksheets(1).Range("AB14"))

Thanks for your help

I'm a little in over my head, espectially with CDbl, but I copied what you wrote and got this error..

Capture.jpg

I wouldn't even know where to begin debugging this from this point...

again, any time and help is much appreciated...

[mdmackillop]

Can you post your workbook. Manage Attachments/Go Advanced

[Ax8472]

There you go.

Bare in mind I only started this this week. I'm obviously terrible, but I'm trying to figure something out for my boss. I've made a practice sheet at home so I can recreate it at work, but with dummy data.

So yo can understand this mess, page one will be both teams whole stats, side by side. With a drop down box for wtd, last week, etc.

Page two will be on an individual basis, (staff). Same filters apply.

Page three is some raw data but I've managed to implement a date system which works out the week to date, and the subsequent weeks before that (likely a convoluted way of doing so but it seems to work).

"Sheet 6" is where I test macro 15 which is to apply a filter using data from cells.

Any help appreciated. I'm so in over my head...

[mdmackillop]

What appear dates on sheet 1 are in fact text. Dates will normally align to the right. Try changing the format to mmmm dddd yyyyy. Nothing will change.

It's good practice to create worksheet variables, especially if your code involves more than one. Also, Sheets(1) is prone to error if the sheet order changes. Use Sheet names or Code names (which never change) eg. Sheet1.Range("AB17")

Sub Macro15()


Dim ws1 As Worksheet, ws2 As Worksheet


Set ws1 = Sheets("Sheet6")
Set ws2 = Sheets("Sheet1")


ws1.Range("$K$20:$Q$58").AutoFilter Field:=3, Criteria1:= _
">=" & CDbl(ws2.Range("AB17")), Operator:=xlAnd, Criteria2:="<" & CDbl(ws2.Range("AB16"))
End Sub