Why won't it work when I use VBA to set autofilter criteria to ce.
 

Below is an example of what I had programmed for a custom autofilter. The
cells in the criteria contain dates. When I run the Macro it hides all the
data. However if I then go into the custom filter manually the dates are
already entered in the criteria and I just have to press OK for the filter to
work. Is there a way around having to do this manually or can excel not
handle this sort of functionality?

Dim sStartDate As String
Dim sEndDate As String
Dim sBottomRow As String

sStartDate = Sheets("Data").Cells(6, 2).Value
sEndDate = Sheets("Data").Cells(6, 4).Value
sBottomRow = CStr(Sheets("Data").Range("B8").End(xlDown).Row)


Selection.AutoFilter Field:=8, Criteria1:="=" & sStartDate,
Operator:= _
xlAnd, Criteria2:="<=" & sEndDate

Why won't it work when I use VBA to set autofilter criteria to ce.
 

Dim sStartDate As Date
Dim sEndDate As Date
Dim sBottomRow As String
sStartDate = Sheets("Data").Cells(6, 2).Value
sEndDate = Sheets("Data").Cells(6, 4).Value
sBottomRow = CStr(Sheets("Data").Range("B8").End(xlDown).Row)
Selection.AutoFilter Field:=8, _
Criteria1:="=" & sStartDate, _
Operator:=xlAnd, _
Criteria2:="<=" & sEndDate

if that doesn't work modify the above to

Selection.AutoFilter Field:=8, _
Criteria1:="=" & clng(sStartDate), _
Operator:=xlAnd, _
Criteria2:="<=" & clng(sEndDate)


--
Regards,
Tom Ogilvy


"biggy" wrote in message
...
Below is an example of what I had programmed for a custom autofilter. The
cells in the criteria contain dates. When I run the Macro it hides all the
data. However if I then go into the custom filter manually the dates are
already entered in the criteria and I just have to press OK for the filter
to
work. Is there a way around having to do this manually or can excel not
handle this sort of functionality?

Dim sStartDate As String
Dim sEndDate As String
Dim sBottomRow As String

sStartDate = Sheets("Data").Cells(6, 2).Value
sEndDate = Sheets("Data").Cells(6, 4).Value
sBottomRow = CStr(Sheets("Data").Range("B8").End(xlDown).Row)


Selection.AutoFilter Field:=8, Criteria1:="=" & sStartDate,
Operator:= _
xlAnd, Criteria2:="<=" & sEndDate

Why won't it work when I use VBA to set autofilter criteria to
 

Sorry if I didn't explain myself properly. The clng funtion prevents the
macro from working all together. Excel seems to recognise the date formats
properly but simply doesn't show the data even though it is filtering on the
correct values. It does show the data if in macro you actually have the
actual values in the data i.e. 12/03/03. But for some reason just won't show
the data when you use cell references rather than actual values even though
those cells contain the same values. So think I might give up on this one.

"Tom Ogilvy" wrote:

Dim sStartDate As Date
Dim sEndDate As Date
Dim sBottomRow As String
sStartDate = Sheets("Data").Cells(6, 2).Value
sEndDate = Sheets("Data").Cells(6, 4).Value
sBottomRow = CStr(Sheets("Data").Range("B8").End(xlDown).Row)
Selection.AutoFilter Field:=8, _
Criteria1:="=" & sStartDate, _
Operator:=xlAnd, _
Criteria2:="<=" & sEndDate

if that doesn't work modify the above to

Selection.AutoFilter Field:=8, _
Criteria1:="=" & clng(sStartDate), _
Operator:=xlAnd, _
Criteria2:="<=" & clng(sEndDate)


--
Regards,
Tom Ogilvy


"biggy" wrote in message
...
Below is an example of what I had programmed for a custom autofilter. The
cells in the criteria contain dates. When I run the Macro it hides all the
data. However if I then go into the custom filter manually the dates are
already entered in the criteria and I just have to press OK for the filter
to
work. Is there a way around having to do this manually or can excel not
handle this sort of functionality?

Dim sStartDate As String
Dim sEndDate As String
Dim sBottomRow As String

sStartDate = Sheets("Data").Cells(6, 2).Value
sEndDate = Sheets("Data").Cells(6, 4).Value
sBottomRow = CStr(Sheets("Data").Range("B8").End(xlDown).Row)


Selection.AutoFilter Field:=8, Criteria1:="=" & sStartDate,
Operator:= _
xlAnd, Criteria2:="<=" & sEndDate

Why won't it work when I use VBA to set autofilter criteria to
 

I understand you completely!! but don't have the answer!
In the example I'm trying I just want to filter a list of dates that are
equal to or less than todays date. (i.e. Fields with a date of either today
or in the past)

I have the code:
Selection.AutoFilter Field:=1, Criteria1:="<=" & Date
to assign the filter.

This shows correctly in the custom filter dialog after the code is run, but
no data is shown. To make the data show all I need to do is go into the
custom dialog and click OK (without changing anything!!). When OK is clicked
the data shows as expected.

I've been round three people at work but knowone can answer this!!
Weird eh????


"biggy" wrote:

Sorry if I didn't explain myself properly. The clng funtion prevents the
macro from working all together. Excel seems to recognise the date formats
properly but simply doesn't show the data even though it is filtering on the
correct values. It does show the data if in macro you actually have the
actual values in the data i.e. 12/03/03. But for some reason just won't show
the data when you use cell references rather than actual values even though
those cells contain the same values. So think I might give up on this one.

"Tom Ogilvy" wrote:

Dim sStartDate As Date
Dim sEndDate As Date
Dim sBottomRow As String
sStartDate = Sheets("Data").Cells(6, 2).Value
sEndDate = Sheets("Data").Cells(6, 4).Value
sBottomRow = CStr(Sheets("Data").Range("B8").End(xlDown).Row)
Selection.AutoFilter Field:=8, _
Criteria1:="=" & sStartDate, _
Operator:=xlAnd, _
Criteria2:="<=" & sEndDate

if that doesn't work modify the above to

Selection.AutoFilter Field:=8, _
Criteria1:="=" & clng(sStartDate), _
Operator:=xlAnd, _
Criteria2:="<=" & clng(sEndDate)


--
Regards,
Tom Ogilvy


"biggy" wrote in message
...
Below is an example of what I had programmed for a custom autofilter. The
cells in the criteria contain dates. When I run the Macro it hides all the
data. However if I then go into the custom filter manually the dates are
already entered in the criteria and I just have to press OK for the filter
to
work. Is there a way around having to do this manually or can excel not
handle this sort of functionality?

Dim sStartDate As String
Dim sEndDate As String
Dim sBottomRow As String

sStartDate = Sheets("Data").Cells(6, 2).Value
sEndDate = Sheets("Data").Cells(6, 4).Value
sBottomRow = CStr(Sheets("Data").Range("B8").End(xlDown).Row)


Selection.AutoFilter Field:=8, Criteria1:="=" & sStartDate,
Operator:= _
xlAnd, Criteria2:="<=" & sEndDate