Visual Basic Programming Code Examples Visual Basic > Other Code Examples Searching the contents of an Excel Range Searching the contents of an Excel Range The following code demonstrates a how to search the contents of an Excel Range: Option Explicit 'Purpose : The following code searches a range for an item 'Inputs : rngFind The range to search for an item ' vValue The value of the item to search for ' bMatchCase Determines if the search is case sensative ' eLookAt Determines if an exact match or a part match is valid. ' cMatchingCells See outputs 'Outputs : Returns the count of the matching cells ' cMatchingCells A collection containing the matching cells Function RangeFindItem(rngFind As Excel.Range, vValue As Variant, Optional bMatchCase As Boolean = False, Optional eLookAt As XlLookAt = xlWhole, Optional cMatchingCells As Collection) As Long Dim rngFound As Excel.Range On Error GoTo ErrSearchFinished 'Store the matching cells Set cMatchingCells = New Collection Set rngFound = rngFind.Find(vValue, , xlValues, eLookAt, xlByRows, , bMatchCase) Do While (rngFound Is Nothing) = False 'Store the matching cell cMatchingCells.Add rngFound, rngFound.Address RangeFindItem = RangeFindItem + 1 Set rngFound = rngFind.FindNext(rngFound) Loop ErrSearchFinished: On Error Goto 0 End Function 'Demonstration routine Sub Test() Dim oMatching As Collection, vThisCell As Variant Debug.Print "Found " & RangeFindItem(Selection, "a", False, , oMatching) & " Matching Cells" For Each vThisCell In oMatching Debug.Print "Cell address: " & vThisCell.Address vThisCell.Interior.Color = vbYellow Next End Sub