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