How to Do an Exact Match in Excel Using VBA


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

    Sub Quick()

    '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!"

    End Sub

    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.

Related Searches


  • Photo Credit Stockbyte/Stockbyte/Getty Images
Promoted By Zergnet


You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!