Subscript out of range - ReDim Preserve an array

Hi!

Please help me solve the following problem...

I'm trying to copy a range from the worksheet into a VBA array. This range is basically a column of zeroes and ones. Then I want to perform operations on this array: to increment the counter if an array element is 1 and to decrement if it is 0. Then I need to pass all the intermediary positions of the counter into another array. Which I would copy into another range on the worksheet.

If I only increment the counter, everything goes fine. But when I try to decrease it, I get a "subscript out of range" error. I guess it happens because I have a negative number in the ReDim Preserve statement.

I'm a VBA newbie and can't figure out the solution for this.

The following code gives me a "Subscript of Range" error.

Please Login or Register  to view this content.

Originally Posted by nathansav

you cant do it that way, you have not defined i so it is 0. You will need to error trap the fact that new array doesnt exist with any data in.

if you have a 0 in A1, you are redimming the array as being -1 in size.

If element = 0 Then i=iif(i=0,0,i-1)

Nathansav, thank you very much for your answer. Didn't know about "IIf". This resolved the "subscript out of range".
However I understood I need to reformulate the question.

I realized I actually need negative numbers to appear in the resulting array.
I need to monitor all the iterations of the counter and if they are negative, or zero, I need them to display too.
If, for example, in the source column I start from zero (A2=0) , and I have three zeroes following it (A3=0; A4=0; A5=0) I want the following to display in the resulting column:
-1
-2
-3


Can this be done somehow with ReDim Preserve or any other method?

Best regards,
dejavision.