Problem with Redim statement

[Eugene Gerber]

I am trying to use the Redim statement to create a growing 2-dimensional
list. I get the following error the second time I try to Redim:

Run-time error '9':

Subscript out of range.

The code is as follows:

Dim List() As String
Dim ListCount As Integer
ListCount = 0

For Each item In Workbooks("Master
Pricelist.xls").Worksheets("Pricelist").Range("DataCategory")

If Target.Value = item.Value Then
If ListCount = 0 Then
ListCount = ListCount + 1
ReDim Preserve List(ListCount, 1)
List(ListCount, 1) = item.Range("B1").Value
ElseIf NotInList(item.Range("B1").Value, List,
ListCount, 1) Then
ListCount = ListCount + 1
ReDim Preserve List(ListCount, 1)
*****This line give the error
List(ListCount, 1) = item.Range("B1").Value
End If
End If

Why does the Redim statement work the first time but not the second time?

 

[Andy Pope]

Hi,

You can only redim the array on the outer dimension.

The syntax would appear more like,

ReDim Preserve List(1,ListCount)

This is directly from the help file.

"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)

Cheers
Andy