autofilter using string input
 

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

autofilter using string input
 

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.

On Wed, 27 Oct 2004 19:50:31 -0700, "KT"
wrote:

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

autofilter using string input
 

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.

-----Original Message-----
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.

On Wed, 27 Oct 2004 19:50:31 -0700, "KT"

wrote:

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

.

autofilter using string input
 

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.


KT wrote:

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

--

Dave Peterson