Macro that uses FindNext to search for specific format finds wrong cell in Excel 2003 or in Excel 2002 (282151)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
This article was previously published under Q282151

SYMPTOMS

When you use a Microsoft Visual Basic for Applications macro to search for cells with specific formatting, the macro may incorrectly find cells that do not match the formatting that you specified.

CAUSE

This behavior can occur because the macro uses the FindNext or FindPrevious method, each of which ignores format settings used in the preceding Find method.

NOTE: If you click Find Next while recording a macro, the FindNext method is used.

WORKAROUND

To work around this behavior, replace all instances of FindNext and FindPrevious with Find. The second and subsequent Find commands need to have the optional After and SearchFormat parameters.

For example, if the recorded code appears as follows 
Application.FindFormat.Interior.ColorIndex = 6
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=True).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
replace the second two instances of Cells.FindNext(After:=ActiveCell).Activate with the Find method as follows: 
Application.FindFormat.Interior.ColorIndex = 6
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=True).Activate
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=True).Activate
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=True).Activate

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
Modification Type:MajorLast Reviewed:5/2/2005
Keywords:kbbug kbpending KB282151
©2004 Microsoft Corporation. All rights reserved.