Dynamic Array Fails to Redim Preserve

I am not posting my workbook. It contains significant personal data. The attached JPG gives an indication of the nature of the data the workbook contains.

The two Redim Perserve statements in the subroutine RedimPerserve() are intended to keep the upper bounds of both arrays synchronized. The first statement for RowNumber() works fine. The second statement for ColumnContent() fails with "Subscript out of range". The data in the attached JPG shows that the breakpoint data stored by VBA should allow the second statement to successfully function. I am doing the Redim Preserve on the last dimension of the array.

Why is this error message presenting? What can I do to fix it?

Rod

For me to believe is insufficient for you to know. - rodalsa

"Subscript out of range".
Could mean a number of things, but most likely guess would be your reference to worksheet, or range is wrong. Since you have an array, it is most likely that the range you are selecting is out of boundary, so check the array dim and upper and lower boundaries.

You can only redim preserve the final dimension of an array, consider:

Please Login or Register  to view this content.

Thanks for the quick replies.

AB33:

Public RowNumber() As String
Public ColumnContent() As Variant

These two arrays are Redim Preserve(d) to increase the size of the array in the following code segment;

Please Login or Register  to view this content.

While digging around on your suggestion I uncovered the following solution to my problem;

I found that the redim preserve code for increasing the size of both of the columncontent() array's dimension were not stated as 1 to x. See above code. I changed both dimension to 1 to x as in;

Please Login or Register  to view this content.

I then ran the code without modifying the Redim Preserve statements that reduced the last dimension. That statement was;

Please Login or Register  to view this content.

The statement failed. I changed the first dimension to 1 to Ubound(ColumnContent,1) shown here;

Please Login or Register  to view this content.

and the statement no longer produced an error. Problem solved if after the move to End Sub (the next sequential statement) these debug.prints check out...

' BEFORE the correcting code revisions and unable to get to the next sequential code line due to "Subscript out of range" ' error...

Debug.Print ColumnContent(3,12)
' Returns - "Runtime error '9':"
' "Subscript out of range" as it should because Ubound(ColumnContent,2) was 11 and

Debug.Print ColumnContent(3,11) 'returns
1.0 Biopsy skin lesion (11100) 'as expected because Ubound(ColumnContent,2) was 11 and

Debug.Print ColumnContent(3,10) 'returns
1.0 Routine venipuncture (3415) 'as expected 10 < 11.

'AFTER the correcting code revisions and in the break mode on the next sequential line..

Debug.Print ColumnContent(3,12)
' Returns - "Runtime error '9':"
' "Subscript out of range" as it should because Ubound(ColumnContent,2) is now 10 and

Debug.Print ColumnContent(3,11) ' now
' Returns - "Runtime error '9':"
' "Subscript out of range" as it should because Ubound(ColumnContent,2) is now 10 and

Debug.Print ColumnContent(3,10) 'returns
1.0 Routine venipuncture (3415) 'as expected 10 = 10.

' Clearly showing that the Redim Preserve now works as expected.

I should have known. These masters of humanity insist on uniformity. Redim Preserve with the dimensions stated as x to y first and you better Redim Preserve with the dimensions as x to y everywhere else.

Hey. Thanks again. I am marking this thread solved and bumping your *.

Rod

For me to believe is insufficient for you to know. - rodalsa