Redim Preserve multidimensional array not working

[newbi004]

Hi

I have the following code to transfer a selection of data from one worksheet ("All SAP Data") to another worksheet ("M&T Data only").

The "All SAP Data" has over 50k rows and 16 columns of data. Column 14 contains reference codes such as 6050, 6054, 6067, 6155 etc. (there are about 35 reference codes to select from).

I want to loop down each row of data in "All SAP Data" and if that row contains 6050 in column 14, transfer data in that row from columns 5, 7, 9, 10, 11, 12 and 14 to "M&T Data only".

Eventually, I will be wanting to select a number of reference codes, say 6050, 6067, 6155 etc. (up to 10) but for the time being I was testing this only for 6050.

The code I have is:

Please Login or Register  to view this content.

I get a type Subscript out of range error at this point:

Please Login or Register  to view this content.

Could someone point out why it's doing that please?

[Izandol]

aDataIn only has 2 dimensions therefore Ubound(aDataIn, 7) will not work. I do not understand why you use Preserve if there is no data in aDataOut to preserve, but if you use this you may only resize the last dimension of aDataOut.

[newbi004]

Hi Izandol

Many thanks for replying. I am not confident with arrays and have adapted the code which works for a 1D array to deal with a 2D array. Would you be able to guide me how to deal with a 2D array?

[Izandol]

I have not tested this:

Please Login or Register  to view this content.

[newbi004]

Izandol

Your suggestion stopped the error. However, I think my output code must be wrong because it is only printing the first 7 rows as follows:

Capture.PNG

The output code is :

Please Login or Register  to view this content.

Thanks again for helping me progress. What could be going wrong with my code?

[Izandol]

Please check the output code in my version. You do not want to transpose, and your row count is incorrect because of your header row.

[AB33]

Please Login or Register  to view this content.

[newbi004]

So sorry about the delay in coming back, Izandol...your code worked perfectly and read through all the data and processed it within 1 sec!! Fantastic. Many thanks for your help.

AB33, your code worked just as well. Thanks for this.

Wow...what a fantastic Forum!