vba multidimensional arrays

[jordan2322]

Hello all,

I am having trouble with adding elements to my multi dimensional array.
everytime i try to re-dimension it, increasing its number of elements by 1 i receive an error.
here is the code i am using

Please Login or Register  to view this content.

i wish for my array to be redimensioned every time the for loop repeats, so that the new values of
.Cells(top_row, new_relocation_column).value , top_row, bottom_row all get added to the newly created element positions in the array

could somebody please let me know what im doing wrong.

Many thanks!!
Jordan

[MrShorty]

From the help file for the redim statement http://msdn.microsoft.com/en-us/libr.../gg251578.aspx

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.

You're going to have to rethink how you are going to use and structure this array variable to meet this restriction. Maybe just put the indices in a different order so that one you are changing is the second index.

[AB33]

Please Login or Register  to view this content.

Dimentioned as 0 row and 2 columns and yet you have

Please Login or Register  to view this content.

Going down to the last row and assigning values to zero Redim array

[AlphaFrog]

MrShorty has it correct. Have the row dimension as the 2nd dimension e.g. Resources(c,r)

Afterwards, you could then use the Transpose worksheet function to change your array back to the r,c e.g:
Resources = Application.Transpose(Resources)

[jordan2322]

Is there perhaps an easier way to build this 2 dimensional array. In one dimension i want it to hold 3 elements, in the other dimension i want it to create a new row for the first dimension to be able to hold another 3 elements.

i.e. first pass:
myArray(0,0)=X: myArray(1,0) = Y: myArray(2,0) = Z

second pass:
myArray(0,1) = X+1: myArray(1,1) = Y+1, myArray(2,1) = Z+1

nth pass
myArray(0,n) = X+N: myArray(1,N) = Y+N, myArray(2,N) = Z+N

regards
Jordan

[MrShorty]

I think AlphFrog has touched on the easiest approach -- "transpose" the array so the first dimension is the one fixed at 3 then have the 2nd dimension increase as needed. Before trying to propose more difficult ideas, is there something about his suggestion that you don't like? Or have you not yet had a chance to try it out? If the latter, please give it a try and see if you can get it to work. If you feel you can't transpose the array, please explain why so we can understand exactly what you want to do and why his suggestion won't help.

[jordan2322]

got it sorted, thanks for your help guys!!

Jordan