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

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.

Step

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

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

Step

Copy and paste the following code into the blank window:

Step

Sub Find_First() Dim FindString As String Dim Rng As Range FindString = InputBox("Enter a Search value") If Trim(FindString) <> "" Then With 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 Then Application.Goto Rng, True Else MsgBox "Nothing found" End If End With End If End Sub

Step

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

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.