VB and VBA Users Source Code: Searching the contents of an Excel Range
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Searching the contents of an Excel Range
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Friday, November 09, 2001
Hits:
1119
Category:
Office
Article:
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 'Author : Andrew Baker 'Date : 5/Nov/2001 03:17 'Notes : 'Revisions : 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
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
