Microsoft KB Archive/248670: Difference between revisions

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - """ to """)
 
Line 75: Line 75:
<br />
<br />
For example, in Microsoft Windows 95, Microsoft Windows 98, or Microsoft Windows NT 4.0, the line of code is the following
For example, in Microsoft Windows 95, Microsoft Windows 98, or Microsoft Windows NT 4.0, the line of code is the following
<pre class="codesample">Selection.AutoFilter Field:=1, Criteria1:=&quot;2/2/98&quot;
<pre class="codesample">Selection.AutoFilter Field:=1, Criteria1:="2/2/98"
                 </pre>
                 </pre>
and the cell containing the date in the AutoFilter list is formatted as &quot;m/d/yyyy&quot;, change the line of code to the following:
and the cell containing the date in the AutoFilter list is formatted as "m/d/yyyy", change the line of code to the following:
<pre class="codesample">Selection.AutoFilter Field:=1, Criteria1:=&quot;2/2/1998&quot;
<pre class="codesample">Selection.AutoFilter Field:=1, Criteria1:="2/2/1998"
                 </pre>
                 </pre>
=== Method 2: Changing the Date Format of the Code and Worksheet Cells ===
=== Method 2: Changing the Date Format of the Code and Worksheet Cells ===
Line 91: Line 91:
<br />
<br />
For example, if the cells are formatted as MM/dd/yyyy, the line of code is the following:</p>
For example, if the cells are formatted as MM/dd/yyyy, the line of code is the following:</p>
<pre class="codesample">Selection.AutoFilter Field:=1, Criteria1:=&quot;02/02/1998&quot;
<pre class="codesample">Selection.AutoFilter Field:=1, Criteria1:="02/02/1998"
                     </pre></li></ol>
                     </pre></li></ol>


Line 100: Line 100:
== MORE INFORMATION ==
== MORE INFORMATION ==


In Microsoft Windows 95, Microsoft Windows 98 and Microsoft Windows NT 4.0, the default short date format is &quot;M/d/yy&quot;.<br />
In Microsoft Windows 95, Microsoft Windows 98 and Microsoft Windows NT 4.0, the default short date format is "M/d/yy".<br />
<br />
<br />
In Microsoft Windows 2000, the default short date format is &quot;M/d/yyyy&quot;.
In Microsoft Windows 2000, the default short date format is "M/d/yyyy".


</div>
</div>

Latest revision as of 12:51, 21 July 2020

Article ID: 248670

Article Last Modified on 10/11/2006



APPLIES TO

  • Microsoft Excel 97 Standard Edition



This article was previously published under Q248670


SYMPTOMS

When running a recorded Microsoft Visual Basic for Applications macro (Sub procedure) that performs an AutoFilter, dates that meet the criteria are not filtered.

CAUSE

This behavior occurs when you perform the following steps:

  1. You configure the regional settings in the operating system to display the short date format in any format other than the default setting.
  2. You type dates in one or more cells in a workbook, including the day, month, and year with hyphen (-) or slash mark (/) separators, and do not specify a specific date format.
  3. You record a macro using the AutoFilter command.
  4. You select a date for part of the AutoFilter criteria.
  5. You run the recorded macro.


WORKAROUND

Either change the date format of the recorded macro to the default short date format of the operating system, or format the dates in the worksheet and recorded code with a custom date format.

Method 1: Changing the Date Format of the Recorded Code

Change the value assigned to the Criteria1 argument or the Criteria2 argument of the AutoFilter method to match the default short date format of the operating system.

For example, in Microsoft Windows 95, Microsoft Windows 98, or Microsoft Windows NT 4.0, the line of code is the following

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"
                

Method 2: Changing the Date Format of the Code and Worksheet Cells

Change the format of the dates to a different custom date number format, and change the value assigned to the Criteria1 argument or the Criteria2 argument of the AutoFilter method to match the same custom date number format. Follow these steps to perform the task:

  1. In the worksheet, select the dates in the list.
  2. On the Format menu, click Cells.
  3. On the Number tab, click Custom in the Category list.
  4. Type a new date format in the Type box (for example, MM/dd/yyyy) and click OK.
  5. Change the value assigned to the Criteria1 argument or the Criteria2 argument of the AutoFilter method to match the date format of the dates in the worksheet list.

    For example, if the cells are formatted as MM/dd/yyyy, the line of code is the following:

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


MORE INFORMATION

In Microsoft Windows 95, Microsoft Windows 98 and Microsoft Windows NT 4.0, the default short date format is "M/d/yy".

In Microsoft Windows 2000, the default short date format is "M/d/yyyy".

REFERENCES

For additional information about other issues with dates in AutoFilters and recorded macros, click the article number below to view the article in the Microsoft Knowledge Base:

215770 XL97: Recorded AutoFilter Macro Does Not Filter Dates Correctly



Additional query words: Y2K XL97 vba

Keywords: kbbug kbpending KB248670