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

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

I agree with Dave, that should be the way to go.
Here a simple example how to return an array via a function while
using a global variable to hold the array.

'define a global variant for the array
Dim mnth As Variant

Sub MainRoutine()
mnth=MonthlyDateArray(2011)
' now mnth contains an array of 12 date values
End Sub

Function MonthlyDateArray(TheYear As Integer, Optional MonthDay As
Integer) As Variant
' returns variant array with 12 dates, one for each month
Dim i As Integer
Dim m As Variant
m = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11",
"12")
If MonthDay < 1 Or MonthDay 31 Then MonthDay = 1
For i = 1 To 12
m(i) = m(i) & "/" & MonthDay & "/" & TheYear
Next i
MonthlyDateArray = m
End Function

That IS the best way to do it, and thanks. I should have thought of it
before posting, I was just in "must use a function" mode.

Thanks, Dave


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