Posting only a specific section of an array to worksheet range?

I'm just starting to learn VBA arrays so please overlook any newbie mistakes.

I have read a workbook range into a non-static two-dimensional array (data type = variant).

Please Login or Register  to view this content.

After closing the original workbook, I am looping through this array to find contiguous "blocks" of data. (These blocks are determined by checking the first column. If the next row in the array has a different value to the previous, that is the end of that 'block'. I only mentioned this info as background - if it doesn't help, just ignore it. It is working).

I wish to create a new workbook and write values from the current 'block' of the array to the first sheet in the new workbook (starting from cell A7 in Sheet 1 and continuing to the end of the block). But I can't work out how to only write a specific section of the array to the worksheet. Anyone?

Extract of code below:

Please Login or Register  to view this content.

Originally Posted by rylo

How about an example file, with code, and an explanation based on the data in the example file on what should go where.

G'day rylo. I was hoping to avoid that. (confidentiality reasons). I suppose it's not possible to work out what I'm doing wrong just by the description & code I provided? I will try to create a dummy version to upload for checking - but this will take a while.

Originally Posted by rylo

I'm assuming that you want to put each "block" into A7, but is this on a new workbook, or sheet within a workbook or what.

The A7 is in a new workbook, 1st sheet. (code extract below):

Please Login or Register  to view this content.

The A7 is the upper left corner of the intended 'paste' area. The paste area is variably sized to fit the data "block" out of the VBA array.

Originally Posted by rylo

How about an example file, with code, and an explanation based on the data in the example file on what should go where.

Please see attached. The XLSM contains the macro which should create separate workbooks based off the data in the "client list". The XLSX is a dummy client list for testing purposes.

What should go where = In the second exported workbook (TEST_SAMPLE CLIENT NAME 2.xlsx) I would expect to see A7 contain "TEST DATA", A8 contain "TEST DATA 2" & A9 contain "TEST DATA 3".

Originally Posted by rylo

build another array and put all relevant data into that array and when you have collected them all, then have it pasted back to A7.

I did look at creating a temp array which would be created from the main array. This temp array would contain the current "block" of the main array. I was hoping to write this temp array to the new workbook (the temp array would be built at the start of each loop and scrapped at the end of each loop).

However as a newbie to arrays, I ran into a lot of trouble trying to get this work. I still can't.

Do you know of a way to create a 2nd array based off a portion of a two dimensional array?

Originally Posted by rylo

Is there any particular reason that you are working with an array this way? Why don't you just keep your data file open, and work directly from that to the output file?

Actually that is the way I used to do it. But with the size of the client list (and other calls that are not included in the uploaded sample), it took a looong time to run (5-10 minutes from memory). I recently learned that using arrays to read/write data is much faster (I proved it on a macro related to this one - we are talking a speed jump of 12x to 15x faster!) which is why I want this code to use arrays.

you can use application.index to extract subsets of an array

Perhaps you should look into using a database, eg MySQL, Access, rather than Excel.

Originally Posted by JosephP

you can use application.index to extract subsets of an array

I have recently solved the problem by using a temp array (from the code provided by rylo)

However I am very interested in this suggestion you provided (The temp array as another item in memory. I would prefer to write only a segment of a single array).

The problems is that I don't know how to use Application.Index. Would it be possible to post an example based on the code in the workbook in post #4?

Originally Posted by rylo

hopefully should get you going.

It got me going! Thanks +1

That code was key to working out the temp/sub array solution.

For anyone interested, I am uploading the demo workbook containing the sub array solution.

I am marking this thread as solved.

I didn't find out how to post a specific section of an array to a workbook range. (Unless JosephP is onto something with Application.Index?)

However there is a 'work-around' which works for practical purposes. (Copy specific section of array to a temp/sub-array and then post the sub array to a workbook range)

UPDATE: Application.Index method works. Thanks JosephP

it would be

Please Login or Register  to view this content.

note your idiff calculation is 1 too many

please note I am not saying that is the most efficient approach. for large datasets you may find that using ado and recordsets is faster

Hello JosephP

Thank you for the tip on Application.Index.

I have modified a copy of the export code to use Application.Index but I can not get this to work. See attached workbook. It seems to loop on saving workbooks and the saved workbooks only contain the first line of code?

Would you be able to spare a few minutes to check where the application.index version is going wrong? If you are too busy/not interested then that's OK.

I think you will find that is not correct :-)

your loop in the runclientsreport_loop routine is incorrect-line 360 oughta be

Please Login or Register  to view this content.

rather than

Please Login or Register  to view this content.

JosephP,

Whoa. You are right (again ). I appreciate you taking the time to debug this. +1

This thread has been a valuable learning experience. Thank you JosephP and rylo. I now have two methods (Application.Index & temporary sub array) to deal with this situation in future.

If any one is interested, attached is the final version of the sample workbook containing the code for both methods. On the dummy import workbook, I received times of 13 seconds & 11 seconds, so there wasn't a lot to choose between them.


(In the real world, the speed up gained from this thread has been quite significant. On the actual (non-sample) project, it originally used to take well over 6 minutes to run (it was using an open workbook copy/paste, not an array). Switching to an array method based off the code provided by rylo took this down to under 2 minutes. Finally turning animation off took the macro time down to 48 seconds!)