autofilter using string input

[KT]

The following macro doesn't return any records.

Sub filtering()
Dim StartDate As String
Dim cStartDate As String
Dim cEndDate As String

StartDate = ActiveSheet.Cells(1, 4).Value
cStartDate = ">=" & StartDate
cEndDate = "<=" & ActiveSheet.Cells(1, 4).Value + 6

ActiveSheet.Cells(5, 1).Select
Selection.AutoFilter Field:=8, Criteria1:=cStartDate, _
Operator:=xlAnd, Criteria2:=cEndDate
End Sub

However, when I go manually go to check the autofilter
(custom...) window and then click OK, I get the expected
result of the filter.

It seems the criteria for the filter aren't working very
well programatically. How can I overcome this?

TIA for assistance.
KT

 

[Myrna Larson]

Have you tried turning on the macro recorder, setting up the filter, then
stopping the recorder and looking at the code. Maybe it will give you the
needed clue.

 

[KT]

BTW, the code results in the correct fields being
populated in the Custom AutoFilter dialog box - but for
some reason Excel doesn't filter correctly.

 

[Dave Peterson]

Sometimes, if you convert the dates to longs, it helps:

cStartDate = ">=" & StartDate
cEndDate = "<=" & ActiveSheet.Cells(1, 4).Value + 6

cStartDate = ">=" & clng(StartDate)
cEndDate = "<=" & clng(ActiveSheet.Cells(1, 4).Value) + 6

But dates can be problems in autofilters via code.