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.