Help with passing date value into Auto filter criteria
 

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.

Help with passing date value into Auto filter criteria
 

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

--
Message posted from http://www.ExcelForum.com