Why is my macro running slowly?

[RollingStone88]

Hi everyone.

I'm trying to run a basic simulation supported by the below macro. The purpose of the macro is to effectively copy and paste 45 values generated in row 18 (which are tied to rand() formula cells and thus change with each update) and paste them into a table starting from row 23 and with a table-depth as defined in cell B3. The intention is that once the 45 values in row 18 have been copied into row 23, the values in row 18 update and these new values are then then pasted into row 24 and so forth until the number of rows in cell B3 has been reached. I also have a helper cell that counts the number of rows generated, so I when I run the macro I can see how much progress has been made before putting the kettle on.

My question is: when I run the macro I can see the values being generated in row 18 updating multiple times per second, but the number of rows being counted (by the helper cell) in my table progresses at a much slower rate. It looks as though multiple "iterations" are occurring per row update, but (accepting my very basic experience with VBA) I can't see why that would be. Is there anything I'm doing wrong?

Thanks for any help

Please Login or Register  to view this content.

[CheeseSandwich]

My guess would be that it is caculating every RandBetween formula everytime it writes one value to the worksheet, you can get around this by switching off automatic calculation and then calulate when you wish to then' at the end switch it back to automatic as below:

Please Login or Register  to view this content.

You could just put the rand between part straight into the code as below but this man not suit your needs:

Please Login or Register  to view this content.

Hope this helps

[romperstomper]

I'd also suggest that since you appear to have blocks being copied, you don't write cell by cell as that will be a lot slower. You can do something like:

Please Login or Register  to view this content.

and then factor in the recalculation wherever you want it.

[CheeseSandwich]

Or write to the sheet only once:

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

The above will lose any formula you have in the gaps in the columns - they would be reaplced with values - not able to see if this will be an issue as we can't see the real data and layout.

[romperstomper]

The above will lose any formula you have in the gaps in the columns

You could avoid that by reading/writing the Formula property rather than the Value.

[RollingStone88]

Thanks very much both, I will try and incorporate.

[CheeseSandwich]

You could avoid that by reading/writing the Formula property rather than the Value.

Good point rorya: Every day is a school day...

Please Login or Register  to view this content.

Please Login or Register  to view this content.