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.

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

--
Regards,
Tom Ogilvy


"quartz" wrote in message
...
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.

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.

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.

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.