Redim (out of subscript error) (Excel 2K (VBA))

Topic

New Reply

This code essentially is supposed to get a recordset of data associated with a certain occurrence on column “D” pretty much as a database would do it. If data on clumn “D” equals a certain value, the data on cells G, H and so on the same row are to be input into a listbox control. I keep getting an “Subscript out of range” error when I am ReDIm’ing the array (NOT while assigning data to an arrray member). Can someone spot wht is wrong with this code?. Thanks in advance.

I have declared the variable vector (which is an array) vector as follows:

dim vector() as variant
dim lastRow as integer

‘get the # of the last column
lastRow = Worksheets(“Results”).UsedRange.Rows.Count

‘bundles the cells on column D with legit data
For Each criteria In Worksheets(“Results”).Range(“d2:d” & lastRow).Cells

If criteria.Value = frmMain.cbPart.Value Then

ReDim Preserve vector(index_y, 9) ‘<~~~~~~~~~~~ ERROR RIGHT HERE

'row of the current cell whose value equals the part number being searched
rrrIndex = criteria.Row

vector(index_y, 0) = aw.Range("g" & rrrIndex).Value
vector(index_y, 1) = aw.Range("h" & rrrIndex).Value
vector(index_y, 2) = aw.Range("i" & rrrIndex).Value
vector(index_y, 3) = aw.Range("j" & rrrIndex).Value
vector(index_y, 4) = aw.Range("k" & rrrIndex).Value
vector(index_y, 5) = aw.Range("l" & rrrIndex).Value
vector(index_y, 6) = aw.Range("m" & rrrIndex).Value
vector(index_y, 7) = aw.Range("n" & rrrIndex).Value
vector(index_y, 8) = aw.Range("o" & rrrIndex).Value
vector(index_y, 9) = aw.Range("p" & rrrIndex).Value
index_y = index_y + 1
End If

Next criteria

Reply | Quote

Replies

With ReDim Preserve you can only change the last dimension. So you’ll have to switch the dimensions and use

ReDim Preserve vector(9, index_y)

Of course, you’ll have to modify the rest of the code to suit this change.

Reply | Quote

Wow…..thanks for the prompt tip

Reply | Quote

The following is from XL Help:

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.

You are trying to change the first dimension.

Reply | Quote