XL97: Recorded AutoFilter Macro Does Not Filter Dates Correctly

ID: Q215770

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

When running a recorded macro that uses the AutoFilter feature, a date that meets the filter criteria is not properly filtered, either appearing when it shouldn't or not appearing when it should.

CAUSE

This behavior occurs when you do the following:

While recording a macro, you use the AutoFilter command.

You select a date for part of the AutoFilter criteria.

You change the date format of the cell used for the criteria.

You run the macro you recorded.

RESOLUTION

Change the value assigned to the Criteria1 argument or the Criteria2 argument of the AutoFilter method to match the date format of the date column on the AutoFilter list.

For example, if the line of code is as follows


Selection.AutoFilter Field:=1, Criteria1:="2/2/98"

and the cell containing the date in the AutoFilter list is formatted as m/d/yyyy, change the line of code to the following:


Selection.AutoFilter Field:=1, Criteria1:="2/2/1998"

MORE INFORMATION

The AutoFilter feature performs a simple string comparison. When you change the date format of the cell, AutoFilter fails because the string value is now different from the string value recorded for the AutoFilter method.

REFERENCES

For more information about the AutoFilter method, from the Visual Basic Editor, click the Office Assistant, type AutoFilter Method, click Search, and then click to view "AutoFilter Method."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If the Assistant is not able to answer your query, click the article number below to view the article in the Microsoft Knowledge Base:

Q176476 OFF: Office Assistant Not Answering Visual Basic Questions

Additional query words: XL97 vba

Keywords : kbdta kbdtacode OffVBA KbVBA
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb