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

Written by stephanie ellen
  • Share
  • Tweet
  • Share
  • Email

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.

Skill level:

Other People Are Reading

Things you need

  • Excel 2007 or Excel 2010

Show MoreHide


  1. 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).

  2. 2

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

  3. 3

    Copy and paste the following code into the blank window:

    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, _


    If Not Rng Is Nothing Then

    Application.Goto Rng, True


    MsgBox "Nothing found"

    End If

    End With

    End If

    End Sub

  4. 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."

  5. 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 and warnings

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

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.