Use FIND to return an array of items

[quartz]

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.

[Tom Ogilvy]

use the Union method combined with the sample code in Excel VBA help for the
FindNext method.

--
Regards,
Tom Ogilvy


"quartz" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[Toppers]

A starter:

Sub FindAll()

Dim AddrList(100) As String
Dim n As Long, i As Long, c As Range, firstaddress As String
Dim Searchvalue As String

Searchvalue = "a"

n = 0
With Worksheets(1).Range("a1:c500")
Set c = .Find(Searchvalue, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
n = n + 1
AddrList(n) = c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With

For i = 1 To n
MsgBox AddrList(i)
Next i

End Sub

I don't know how to use UNION as Tom suggested so mine is a more basic
solution but 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.
>

[Jim Thomlinson]

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.
>

[quartz]

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.
> >