Redim 2D Array Subscript Out Of Range Error

[lopsided]

Hi,

I have written some code to extract data from a source into an array
this code worked fine until I decided I needed 2 pieces of info fo
each piece of data. This caused a subscript out of range error in th
Redim Preserve statement line.

k = 1
ReDim varChargeArray(1 To k, 1 To 2)
While Not IsEmpty(ActiveCell)
If ActiveCell.Value = "T" Then
ReDim Preserve varChargeArray(1 To k, 1 To 2) !!ERRO
HERE!!
varChargeArray(k, 1) = ActiveCell.Offset(0, 2).Formula
varChargeArray(k, 2) = ActiveCell.Offset(0, 28).Formula
k = k + 1
End If
Wend

When the array was just one dimension i had no problems.
Can anybody help me?

Many thanks,
To

 

[Keith Willshaw]

Hi,

I have written some code to extract data from a source into an array,
this code worked fine until I decided I needed 2 pieces of info for
each piece of data. This caused a subscript out of range error in the
Redim Preserve statement line.

k = 1
ReDim varChargeArray(1 To k, 1 To 2)
While Not IsEmpty(ActiveCell)
If ActiveCell.Value = "T" Then
ReDim Preserve varChargeArray(1 To k, 1 To 2) !!ERROR
HERE!!

You cant resize the lower bound of an array using the
preserve keyword

What you need to do is make your lower bound array
sufficiently large to start with

Keith

 

[Tom Ogilvy]

Keith gives good advice. You can alter you thinking and transpose the
array, then you are redimming the last dimension

Dim varChargeArray()
Dim k as Long
k = 1
ReDim varChargeArray(1 to 2,1 To k)
While Not IsEmpty(ActiveCell)
If ActiveCell.Value = "T" Then
ReDim Preserve varChargeArray(1 to 2,1 To k) !! NO ERROR HERE!!
varChargeArray(1, k) = ActiveCell.Offset(0, 2).Formula
varChargeArray(1, k) = ActiveCell.Offset(0, 28).Formula
k = k + 1
End If
Wend

 

[Alan Beban]

???The code below (after commenting out !! NO ERROR HERE!! so that it
will compile) executes an endless loop with ActiveCell = "T"; that's to
be expected since nothing changes the active cell after any loop. Once
the OP has code that works (i.e., avoids the endless loop), if the
functions in the freely downloadable file at
http://home.pacbell.net/beban are available to the workbook, then in
that working code the Redim Preserve line can be replaced with
ResizeArray varChargeArray, k, 2

Alan Beban

 

[Tom Ogilvy]

You will have to check with the OP on that - I wasn't debugging his code,
just suggesting how to alter the array. After all, he was the one that said
it worked fine as a 1D array, so I assume he left a line of code out. And
actually it is the whole while Wend construct that loops, not just the code
below !! NO ERROR HERE!!

It would seem a real waste of resources to use some generalized array
resizing routine when it is not required at all if the dimensions of the
array are reversed as I suggested. Even doing an application.Transpose on
each side is unnecessary.

 

[Alan Beban]

You will have to check with the OP on that - I wasn't debugging his code,
just suggesting how to alter the array. After all, he was the one that said
it worked fine as a 1D array, so I assume he left a line of code out. And
actually it is the whole while Wend construct that loops, not just the code
below !! NO ERROR HERE!!

I didn't say "the code below !! NO ERROR HERE!!"; I said "the code
below". The code that was below in my post began with Dim
varChargeArray() and ended with Wend.

It would seem a real waste of resources to use some generalized array
resizing routine when it is not required at all if the dimensions of the
array are reversed as I suggested. Even doing an application.Transpose on
each side is unnecessary.

I don't necesssarily disagree (though I'm always chary of accepting
claims of things like "a real waste of resources" in the complete
absence of any context whatsoever). I suggested an alternative in case
it isn't convenient for the OP to rearrange the dimensions of his stuff.
I'm comfortable letting a user decide what "resources" are relevant and
whether a generalized resizing routine might "waste" them.

Alan Beban

 

[lopsided]

Thanks very much for all of your help,

I have as suggested just transposed the array and this work
perfectly.

Thanks again.
To