Excel - Return Function into an Array 1

[tchor7]

Hi - I have a procedure that calls a Function to return an array. The Function uses the .Find method to store into an array. Problem arises after the function is called and the array is returned to the sub procedure (my 'arange' is empty). There must be some conflict with returning an array as a variant.

Your help is appreciated.
Thanks,
-Thoeum

Sub rtest()
Dim arange As Variant, bR As Range, i As Integer

arange = StoreRanges("IN", Range("STstart"), Range("Data"))

For i = 0 To UBound(arange) '***arange is empty here
Debug.Print arange(i)
Next i
End Sub

Function StoreRanges(ls As String, sR As Range, cR As Range) As Variant
Dim rMaster() As Variant
Dim lCount As Long, ix As Integer, x As Integer, y As Integer

Dim rFoundCell As Range

ix = 1
x = 0
Set rFoundCell = sR
y = WorksheetFunction.CountIf(cR, ls)
For lCount = 1 To y
Set rFoundCell = cR.Find(What:=ls, After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
On Error GoTo 0

ReDim rMaster(0 To y)
rMaster(x) = (rFoundCell.Offset(0, 1))
Debug.Print rMaster(x) '** runs ok here
x = x + 1
Next lCount

StoreRanges = rMaster() '***rMaster() is empty here

End Function

 

[PHV]

I' d replace this:
ReDim rMaster(0 To y)
with this:
ReDim Preserve rMaster(0 To y)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tchor7]

Thank you PH! Worked perfect with the ReDim Preserve

-Thoeum

 

[N1GHTEYES]

Am I missing something here?

It looks like the y value is not changing in the Icount loop - so why not simply redim the rMaster array before doing the loop?

The redim preserve will work of course, but surely it is unnecessary and will just slow things down.

Tony