PDA

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!!