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:
Moderate

Other People Are Reading

Things you need

  • Excel 2007 or Excel 2010

Show MoreHide

Instructions

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

    MatchCase:=False)

    If Not Rng Is Nothing Then

    Application.Goto Rng, True

    Else

    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

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

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