Sorry. I gave an example that I actually had tested again to be sure.
If you are talking of this example, you must be wrong.
If you think you did what I said applying it to your own code, I don’t know in what way you failed.
Eventually attach your demonstrating sheet now as already other contributors told you, and we will soon see. Otherwise this should be stopped to not waste more time…
@Lupp I am attaching my test spreadsheet. It contains the macro function I want to use (already sent yesterday to @JohnSUN).
I would understand very well if you did not answer me. In any case, thank you for the time and patience you have already given me.
dataFromOtherSheets-test.ods (12.0 KB)
Thank you, this is much easier to work with than some “dregs”
dataFromOtherSheets-test1.ods (12.9 KB)
A few clarifications. Your main problem was the line
if isDaySheet(oSheet) Then
In other words, you don’t know in advance how much data you will get by sorting through the sheets from nStartSheet To nEndSheet
This happens all the time with poorly designed data and leads to more complex code. This can usually be solved with two consecutive cycles. For example, you can go through all the sheets once and count the data from how many sheets will actually end up in the function result, based on the count obtained, determine the size of the resulting array and go through all the sheets again, filling the array with data.
Another option is to describe the resulting array as a dynamic array of arrays, iterate over all matching sheets, and add each row read to the end of the result array. If this were a procedure that would eventually output the resulting data to a spreadsheet sheet using setDataArray(), then that would be sufficient. But since this is a function and the result must be a two-dimensional array, it will take two more loops to convert, as in the procedures shown by @Villeroy
Since the accumulated data will in any case have to be rewritten into an array with other dimensions, you can use the technique shown in the attached document: сollect all the data into a long one-dimensional array, into a long chain, and at the end, cut this chain into pieces of the required length and place it in the result array.
1 Like
@JohnSUN Thank you so much ! In fact, starting from the code that you initially gave me, all what has to be done is to add an intermediate array and copy it in the desired order into the result array. Thanks again for your help and also for the clarifications!