Help with passing date value into Auto filter criteria

[John Clarke]

Please can someone help me with a spreadsheet that has been working OK
until this year.

The sheet has a list of dates, there also is a date entered in a cell
which is used as a variable. The format of both the variable and the
list are identical.
The code is below.

Option Explicit

Dim FilterPeriod As String 'Used by subs UpdateChart and
CreatePareto for filtering on graphs
Dim TotalComplaintsFilterPeriod As String 'holds the entended filter
preiod required by the Totals Line Chart (i.e. 3 years)

Sub newmonth()

FilterPeriod = Sheets(1).Range("a2").Value
FilterPeriod = DateAdd("yyyy", -1, FilterPeriod)
FilterPeriod = Format(FilterPeriod, "dd/mmm/yy")
TotalComplaintsFilterPeriod = DateAdd("yyyy", -2, FilterPeriod)
TotalComplaintsFilterPeriod = Format(TotalComplaintsFilterPeriod,
"mmm-yy")

Selection.AutoFilter Field:=1, _
Criteria1:=">=" & TotalComplaintsFilterPeriod, Operator:=xlAnd


End Sub

If I run the macro using Dec-03 or earlier dates the macro works OK.
If I use Jan-04 or any future dates then the macro falls over.
Interestingly if I query the custom value in the autofilter, on my
machine at work the value that is being passed into the criteria field
from the TotalComplaintsFilterPeriod variable is the serial format,
whereas on my PC at home it returns it in the dd/mm/yy format.
Why should this macro stop working all of a sudden and why should the
two machines be different.

Thanks in advance for any solutions.

Should anyone require a simplified version of the spreadsheet just
with the macro and a sample data set please drop me an email.

John C.

 

[BrianB]

I don't really know the reason, but one thing that strikes me a
something that might give a problem is the line :-
Dim FilterPeriod As String

In this respect Excel is most helful in converting any value assigne
to it into a string ("It's a feature, not a bug" <<grin>> )

This usually helps solve numerous problems, but in this case the firs
time you assign it you convert the date value to a string, s
subsequent assignments are having to use a string as one of it
arguments when it might be expecting a date number

 

[John Clarke]

We finally resolved the feature (bug) today.

The macro takes the cuurent month and backdates it 3 years and that
value is passed into the autofilter. We had written the format to be
mmm-yy. What we had not debuged all those years ago was that Jan-04
minus 3 years equals Jan-01. The problem was that the macro was
interpreting it as 01-01 and because we were not explicit in the date
format of yyyy this resulted in 1st Jan 04. Obviously last month and
every month beforehand always had an invalid month value i.e Dec-03
minus 3 equals Dec-00 and as 00 does not equal a valid month therefore
Excel had no alternative but to accept the 00 as being the year 2000.

Another example of thorough testing not quite capturing every
eventuallity.

Let this be a lesson to us all and thanks for anyone who attempted to
help.

John Clarke

 

[Dave Peterson]

Glad you found a work-around.

Sometimes, just converting the date to long will help:

FilterPeriod = Sheets(1).Range("a2").Value
FilterPeriod = DateAdd("yyyy", -1, FilterPeriod)
FilterPeriod = clng(filterperiod)

And use that in the .autofilter line.