How to Search a Cell Range for a Matching Value in Excel VBA

By Stephanie Ellen

Microsoft Excel is spreadsheet software that can contain a vast amount of data: up to one billion cells of information per spreadsheet. While this can be invaluable if you need to input large data sets, it can make it quite a challenge if you are looking for a particular piece of information in a worksheet. Excel Visual Basic for Applications (VBA) can find that cell match in a split second.

Things You'll Need

  • Excel 2007 or Excel 2010

Step 1

Open the worksheet in which you want to find a match. Press the "Alt" and "F11" keys together. This opens the Visual Basic Editor (VBE).

Step 2

Click "Insert" and then click "Module." This inserts a blank module window.

Step 3

Copy and paste the following code into the blank window:Sub Find_First()Dim FindString As StringDim Rng As RangeFindString = InputBox("Enter a Search value")If Trim(FindString) <> "" ThenWith Sheets("Sheet1").Range("A1:Z256")Set Rng = .Find(What:=FindString, _After:=.Cells(.Cells.Count), _LookIn:=xlValues, _LookAt:=xlWhole, _SearchOrder:=xlByRows, _SearchDirection:=xlNext, _MatchCase:=False)If Not Rng Is Nothing ThenApplication.Goto Rng, TrueElseMsgBox "Nothing found"End IfEnd WithEnd IfEnd Sub

Step 4

Change the "Range" in the code to the cell range in which you want to look for a match. For example, if you want to look in cells C1:C1000, type "C1:C1000" in place of "A1:Z256."

Step 5

Press "F5," then type the string of text or data you want to find into the text box. Excel will find the match and highlight the cell.

Tips & Warnings

  • Press the "Alt" and "F11" keys at any time to toggle between the VBE and the worksheet.