I don't quite understand how you're going to use this, but could you declare the
myArray as a public/global variable -- outside any procedure and then populate
it once and use it where ever you want.

On 10/15/2010 01:33, CompleteNewb wrote:
I have this part of a procedure that gives me all the certain values I'm looking
for, and I use it to determine column headings in some sheets, row headings in
others, and various counts and checks, etc. I have to run it in every Sub for
which I use this array. I experimented with CPearson's page on using functions
to return arrays, but I think I'm at too low a level to grasp it. Is there a
relatively easy way to set up the following so I can just use the completed
MyArray in other subs without having to run this every time? Like in the sub I'd
be able to set a range.value to FunctionName(MyArray)?

With MySheet.Columns("C")
Set foundit = .Find("MyString", LookIn:=xlValues, LookAt:=xlPart)
If Not foundit Is Nothing Then
FirstAddress = foundit.Address
Do
Startrange = MySheet.Range(foundit.Address).Offset(3, -2).Address
EndRange = MySheet.Range(Startrange).End(xlDown).Address
For Each cell In MySheet.Range(Startrange, EndRange)
ReDim Preserve MyArray(0 To i)
MyArray(i) = cell.Value
i = i + 1
Next cell
Set foundit = .FindNext(foundit)
Loop While Not foundit Is Nothing And foundit.Address < FirstAddress
i = 0
End If
End With

Thanks for any thoughts and help

--
Dave Peterson