XL: VB Procedure to Emulate Find Command

This article was previously published under Q124924
This article has been archived. It is offered "as is" and will no longer be updated.
You can create a Microsoft Visual Basic for Applications procedure thatemulates the behavior of the Microsoft Excel Find command. To do this, usethe Find and FindNext methods in a Visual Basic module.

The code in the "More Information" section of this article allows you tofind all occurrences of a search string in the active worksheet withoutchanging the selection or the active cell. When the search text islocated, the code displays a message box with the address of the cellthat contains the search string. If the search text is not found, a "Search Value Not Found" message box is displayed.

NOTE: You can easily modify this code by replacing the MsgBox statementswith the code that you want to run whenever the search text is or is notfound.
More information
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Sample Visual Basic Procedure

   Sub FindStrings()       Dim firstCell, nextCell, stringToFind As String       ' Show an input box and return the entry to a variable.       stringToFind = _           Application.InputBox("String to find?", "Search String")       ' Set an object variable to evaluate the Find command.       Set firstCell = Cells.Find(what:=stringToFind, lookat:=xlWhole, _           searchdirection:=xlPrevious)       ' If the string is not found, show this message box.       If firstCell Is Nothing Then           MsgBox "Search Value Not Found.", vbExclamation       Else           ' Otherwise, find the next occurrence of the search text.           nextCell = _               Cells.FindNext(after:=Range(firstCell.Address)).Address           ' Show its address in a message box.           MsgBox nextCell           ' Continue finding the next occurrence as long as the address of           ' the found cell is not the same as the first cell.           Do While firstCell.Address <> nextCell               nextCell = Cells.FindNext(after:=Range(nextCell)).Address               MsgBox nextCell           Loop       End If   End Sub				
If the range in which the search text is to be found is in a small portionof the worksheet, use the For...Each control structure instead of the Findmethod (For...Each requires less code). The advantage to the Find methodis

that it is faster than For...Each on large ranges such as an entireworksheet.

If the string that you are searching for is only a portion of the contentsof a cell, use the "xlPart" constant for the lookat named argument of theFind method.

For additional information, please see the following article in theMicrosoft Knowledge Base:
108892 XL: Cells.Find Returns Error When No Match Found
"Visual Basic User's Guide," pages 148-149
8.00 XL97 XL98 XL7 XL5 FindNext Find Searching

Article ID: 124924 - Last Review: 10/26/2013 15:10:00 - Revision: 3.0

Microsoft Excel 97 Standard Edition, Microsoft Excel 95 Standard Edition, Microsoft Excel 5.0 Standard Edition, Microsoft Excel 98 for Macintosh

  • kbnosurvey kbarchive kbdtacode kbhowto kbprogramming KB124924