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.