Quote Originally Posted by xld
The way I read that statement was that you were loading A1, which triggered changes gto B1:I1, and then you copied those VALUES down 999 rows.

Then you changed A1, and repeated the process.

And so on.

What I didn't get a feeling for was what you did with those values.

As far as I can see, both Test4 and Test5 seem to do that, although they do need a driver, something to load A1 and call the macro.

Am I missing your real intent here?
I completely understand where you were coming from now.

I should explain the simulation process like this:
  1. Set a value for A1, say 255.
  2. Recalculate B1:I1 for this value of A1 (255).
  3. Copy the values recalculated in B1:I1 down to B2:I2.
  4. Reset a the next value for A1 (say 256), and recalculate B1:I1 for this new A1 value (256) and now paste in B3:I3, and so on for 1000 values of A1.
Hope this explains it better.

Please note you may ask why we don't just have A2:A1001 preloaded with input values, and why we don't just copy the formulas down and recalculate. The reason is that the calculations are pretty intensive and to minimise memory overhead I'd like the loop to evaluate the output row (i.e. the yellow highlighted cells in our example workbook) for a given input, and its more efficient to value paste down.

This way we only need to re-calculate a very small section (i.e. one row) and paste down and let VBA do a fast looping, hence this exercise. Also this particular worksheet is more suited and designed to use the proposed looping approach rather than autofill approach.

Your autofill solution provides a very efficient way to do this type of process (copying formulas down rapidly), but for this case I would like to know how to optimally loop-value paste.

Hope this helps clarify.