ReDim, Preserve and Multidimensional arrays

[Guest]

Running Windows XP and Office XP.
I am trying to store data returned from a search into a dynamic
multidimensional array with the following code,

If currentCell.Value Like searchText Then
i = i + 1
ReDim Preserve searchResults(i, 4)
searchResults(i, 1) = ActiveSheet.Name
searchResults(i, 2) = currentCell.Value
searchResults(i, 3) = descriptionCell.Value
searchResults(i, 4) = priceCell.Value
End If

having declared the array at the top of the procedure with

Dim searchResults() As Variant

Problem is that the code always halts with a "subscript out of range" error
message when i = 2. If I remove the Preserve then the code runs fine except
I only have the last line of search data.

Any ideas anybody?

 

[RWN]

From Help on xl2k;
If you use the Preserve keyword, you can resize only the last array
dimension and you can't change the number of dimensions at all. For
example, if your array has only one dimension, you can resize that
dimension because it is the last and only dimension. However, if your
array has two or more dimensions, you can change the size of only the
last dimension and still preserve the contents of the array. The
following example shows how you can increase the size of the last
dimension of a dynamic array without erasing any existing data contained
in the array.

ReDim X(10, 10, 10)
.. . .
ReDim Preserve X(10, 10, 15)

 

[Guest]

When you use the Preserve keyword with a dynamic array, you can change only
the UPPER bound of the last dimension.

John

 

[Alan Beban]

From Help on xl2k;
If you use the Preserve keyword, you can resize only the last array
dimension and you can't change the number of dimensions at all. . . .

To bypass these limitations you might want to do a Google search for
Harlan Grove's aresize function.

Alan Beban