Use FIND to return an array of items
Thanks much to all who replied. I found all responses helpful.
"Jim Thomlinson" wrote:
I like Tom's idea better than the range of addresses. That being said here is
the code for the range of addresses. I would be pretty easy to modify this to
use the union method.
Const STRING_TO_FIND As String = "This"
Sub MakeArray()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirstOccurence As Range
Dim aryAddresses() As String
Dim lngCounter As Long
lngCounter = 0
Set wks = ActiveSheet
Set rngToSearch = wks.Range("A1:A100")
Set rngFound = rngToSearch.Find(STRING_TO_FIND)
If Not rngFound Is Nothing Then
Set rngFirstOccurence = rngFound
Do
ReDim Preserve aryAddresses(lngCounter)
aryAddresses(lngCounter) = rngFound.Address
Set rngFound = rngToSearch.FindNext(rngFound)
lngCounter = lngCounter + 1
Loop Until rngFound.Address = rngFirstOccurence.Address
End If
End Sub
HTH
"quartz" wrote:
I am using Excel 2003 with Windows XP.
I need to be able to return an array of the cell addresses of all
occurrences of a certain value in a sheet, preferably using the FIND command.
Anyone have a clue as to how to do this? If so, could you please post an
example function or code?
Thanks much in advance.
|