Microsoft KB Archive/282151

= Macro that uses FindNext to search for specific format finds wrong cell in Excel =

Article ID: 282151

Article Last Modified on 1/29/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-



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:=&quot;&quot;, 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:=&quot;&quot;, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _   xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _   , SearchFormat:=True).Activate Cells.Find(What:=&quot;&quot;, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _   xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _   , SearchFormat:=True).Activate Cells.Find(What:=&quot;&quot;, 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 in the &quot;Applies to&quot; section.

Additional query words: XL 2007 XL2002 replace XL2003

Keywords: kbbug kbpending KB282151

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.