Using the Changing values of a range to construct a new data array

Hello Everyone,

I asked a herculean question yesterday and get no response. I've made some progress, but I need some help here.


So if you run the macros while on the 'Example sheet', you'll see the loop. Its coping the rows, placing them into a named range, and another named range executes user-defined calculations. The sheet recalculates every time. The calculations are insignificant and are for example purposes. So each copy and paste, I actually want to be constructing new rows of a two dimensional progress.

So to my point questions. How do I take these output calculations and put them in a separate array. Right now they are copy and pasting just so you see what all of the information I want to obtain. In the future, the user will define calculations in the cells (not in vba) so unfortunately, I need to go about it this way.

Please Login or Register  to view this content.

Anyhelp would be much appreciated. In the upcoming days I will start worrying myself with efficiency because this could be a massive time suck. With the few iterations I have now, its already kind of slow.

I can already see the improvements in the performance. Thanks a ton for that. So its appending but when I test to see the new, appended array it repeats the final output twice, juxtoposed. So if I loop to spit it out gives me an array of 1 x 16 dimensions or if I change how it outputs, 16 x 1.

An example is below.


Do I need to change something in the AppendArray2D or in constructing aTmp

Thank you for the help thus far though!

Please Login or Register  to view this content.

I'm uncertain exactly how

I think I may have caused some confustion. I have an input arrray and I want to produce an output array. I want that output array to be separate. After, I want to use recreate regurgitate that output array somewhere else. So if I have 144 inputs, I will produce 144 ouputs. (Note: it could be more. I will be referencing a named range that may have different dimensions but for now lets just assume they have the same dimensions.) So the result of two dimensional 18 by 8 array, should be 144 outputs, which is the same dimensions as the inputs (18*8)

Right now the code produces an array of of 1152 and 1 dimensions. If I attempt to Produce

Please Login or Register  to view this content.

I will receive an error because the data array only has one "column" so to speak.

Please Login or Register  to view this content.

will produce something, because the new data array is 1152 by 1. This isn't however what I want.


It seems like there is something that is just off by a bit. I'm not certain what it is though

That is excellent. For now, the output data needs to be the same dimensions, but its likely that the outputs will be even larger. What part of the code should I modify, if any for that situation? I'm a little hazy on the relationship between the named range 'exoutput', where user-defined calcs will be and AppendArray2D and Get2dArray.

Thanks in advance for this bit of troubleshooting, you've been an amazing help thus far!