View Full Version : Solved: Filter using dates
marreco
03-29-2012, 09:47 AM
hi.
I need to adapt a filter.
in this case I need to add one more criterion.
I cell to cell B4 and D4, in them shall be entered the start date and end date.
need to adapt the code that already have
CatDaddy
03-29-2012, 10:31 AM
http://www.ozgrid.com/VBA/autofilter-vba-dates.htm
marreco
03-29-2012, 10:43 AM
Before posting my question I went to OZGRID.
but I can not adapt to my code that is inside the attachment.
Bob Phillips
03-29-2012, 10:44 AM
Sub TestCase()
Dim LR As Long
Rows(6).Resize(100).Delete
Select Case True
Case Range("A2").Value = "" And Range("B2").Value <> "" And Range("C2").Value <> ""
With Sheets("A2002").UsedRange
.AutoFilter Field:=3, Criteria1:=Range("B2").Value
.AutoFilter Field:=11, Criteria1:=Range("C2").Value
.Copy Range("A6")
.AutoFilter
End With
Case Range("A2").Value <> "" And Range("B2") = "" And Range("C2").Value <> "" And _
Range("B4").Value <> "" And Range("D4").Value
With Sheets("A2002").UsedRange
.AutoFilter Field:=24, _
Criteria1:=">=" & Format(Range("B4").Value, Sheets("A2002").Range("X2").NumberFormat), _
Operator:=xlAnd, _
Criteria2:="<=" & Format(Range("D4").Value, Sheets("A2002").Range("X2").NumberFormat)
.AutoFilter Field:=1, Criteria1:=Range("A2").Value
.AutoFilter Field:=11, Criteria1:=Range("C2").Value
.Copy Range("A6")
.AutoFilter
End With
Case Range("A2").Value <> "" And Range("B2").Value <> "" And Range("C2").Value = ""
With Sheets("A2002").UsedRange
.AutoFilter Field:=1, Criteria1:=Range("A2").Value
.AutoFilter Field:=3, Criteria1:=Range("B2").Value
.Copy Range("A6")
.AutoFilter
End With
Case Range("A2").Value = "" And Range("B2").Value = "" And Range("C2").Value <> ""
With Sheets("A2002").UsedRange
.AutoFilter Field:=11, Criteria1:=Range("C2").Value
.Copy Range("A6")
.AutoFilter
End With
Case Range("A2").Value <> "" And Range("B2").Value = "" And Range("C2").Value = ""
With Sheets("A2002").UsedRange
.AutoFilter Field:=1, Criteria1:=Range("A2").Value
.Copy Range("A6")
.AutoFilter
End With
Case Range("A2").Value = "" And Range("B2").Value <> "" And Range("C2").Value = ""
With Sheets("A2002").UsedRange
.AutoFilter Field:=3, Criteria1:=Range("B2").Value
.Copy Range("A6")
.AutoFilter
End With
Case Range("A2").Value <> "" And Range("B2").Value <> "" And Range("C2").Value <> ""
With Sheets("A2002").UsedRange
.AutoFilter Field:=1, Criteria1:=Range("A2").Value
.AutoFilter Field:=3, Criteria1:=Range("B2").Value
.AutoFilter Field:=11, Criteria1:=Range("C2").Value
.Copy Range("A6")
.AutoFilter
End With
End Select
End Sub
marreco
03-29-2012, 10:56 AM
Thank you excel genius, I'll test after I return, but you can mark the thread as SOLVED.
I can not mark as solved!
Thank you!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.