Autofilter: Filter between to dates

[sophieschrit]

Hi all,

I'm new on VBA and I'm trying to find a solution for my problem. So basically want I want to do is to filter all the data between two exact dates which I get from an inputbox. So I can only keep the payout dates from one week. At the beginning it worked fine but then I saw that I'll have problems if the dates are from two months. I also tried to do it with an Array but didn't worked. I really hope some one can help me as I'm now getting really frustrated :(
Here is my beloved part of the macro and I also attach the worksheet.
Thank you so much for helping me


Sub filtertry()
Dim EndDate0 As Date
Dim EndDate As String
Dim StartDate0 As Date
Dim StartDate As String


ActiveSheet.Name = "Settlement Overview"
EndDate = InputBox("Please insert Enddate", Hi, Format(Now, "dd.mm.yyyy"))


EndDate0 = CDate(EndDate)
StartDate0 = EndDate0 - 4
StartDate = Format(StartDate0, "dd.mm.yyyy")


ActiveSheet.Range("A8").End(xlDown).End(xlToRight).Select


Selection.AutoFilter Field:=4, Criteria1:=">=" & "StartDate", Operator:=xlAnd, Criteria2:="<=" & "EndDate", Operator:=xlFilterValues


End Sub

[Aflatoon]

Try using this:


Selection.AutoFilter Field:=4, Criteria1:=">=" & CDbl(StartDate0), Operator:=xlAnd, Criteria2:="<=" & CDbl(EndDate0), Operator:=xlAnd

[sophieschrit]

Hi thanks for the quick answer. I tried it unfortunately it's also not working :( Could it be that I first have to convert the column in date format? I tried this but maybe had the wrong formula I just don't understand why it works if the the dates in the same month but as soon as you have a week within two months it's not working

[Aflatoon]

Those aren't real dates - they're text. You need to convert them to real dates before the filter will work.

[mancubus]

working with non-US dates in vba is always a pain. :D

if column D contains real dates (not strings) below should work. it worked for the test file i used.




Sub vbax_53986_filter_between_two_dates()
Dim EndDate As Long

EndDate = Application.InputBox("Please insert End Date", "END DATE", Format(Date, "dd.mm.yyyy"), , , , , 1)

With ActiveSheet
.Name = "Settlement Overview"
.Range("A8").AutoFilter Field:=4, Criteria1:=">=" & EndDate - 4, Operator:=xlAnd, Criteria2:="<=" & EndDate
End With
End Sub



edit:
oppps. there was an attachment.
as Aflatoon said, first convert strings in Col D to dates.

[sophieschrit]

Thanks guys, so I will try to convert them then :bug::bug::bug:

[p45cal]

Adapting Mancubus's code (after converting to dates (see your other thread)):
Sub blah1()
Dim EndDate
EndDate = Application.InputBox("Please insert End Date", "END DATE", Format(Date, "dd/mm/yyyy"), , , , , 2)
EndDate = CLng(CDate(EndDate))
With ActiveSheet
.Name = "Settlement Overview"
.Range("A8").AutoFilter Field:=4, Criteria1:=">=" & EndDate - 4, Operator:=xlAnd, Criteria2:="<=" & EndDate
End With
End SubFrom the -4 in the code I guess you're entering a Friday as the end date.

[sophieschrit]

Thank you so much for helping me