A Visual Basic Application procedure efficiently searches through large amounts of data using the Find method. This method is used to do an exact match of the content in one or more cells searching through a worksheet, multiple worksheets or even workbooks. The VBA Find method is more efficient than using only a loop in terms of speed and lines of code. You can combine the Find method with other methods and functions in VBA for an even more effective macro.
Open the Excel file that you want to add the Find method syntax to in your procedure.
Open the VBA Editor to view your procedure. Locate the area in your procedure where you want to enter the Find method syntax.
Add this Find method syntax to your procedure:
Range_Object.Find(What, After, Lookln, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat). An example is
'Enter "Here it is!" in a cell on Sheet 1.
If Not Sheetl.Cells.Find(What:="Here it is!", After:=CelIs(1, 1), Lookln:=xlValues, LookAt:=xlPart,
SearchOrder: xlByRows, Search Direction :=x!Next, MatchCase: =True, SearchFormat: =True) Is _
Nothing Then MsgBox "Here it is!"
This procedure returns nothing if no match is found.
Save and close your Excel file when you are finished. The procedure will be available the next time you open the file.
Tips & Warnings
- Other VBA Find methods include FindNext and FindPrevious, which search after or before the active cell.
- To search and replace an item, use the Replace Method.
- To return the position of an item, use the Match method.
- Make a backup copy of your file in case the macro ruins the data.
- Photo Credit Stockbyte/Stockbyte/Getty Images
How to Remove Duplicates from Excel Macro
A duplicate entry in Microsoft Excel is an entry in a column or row that matches other entries in the same column...
How to Use the Match Function in VBA
The "Match" function in Microsoft Excel VBA (Visual Basic for Applications) procedures finds a match within a range of cells and prints...