Monte Carlo Simulation macro (update random values, calculate results, copy/paste, repeat)

Hi all,

I've build a simulation model. It would be really helpful if I had a macro that would allow me to run the model multiple times, and each time:

- Update the random values before calculations
- Save the results in a different column each time (as not to paste over the results from previous runs)

Here is the file: Monte Carlo Simulation.xlsm

In sheet UI cell A1 you'll find the number of runs (repeats). So essentially the macro should stop once it has run the model X number of times. The model itself runs by activating the Sub Run_Model() that is already in the file.

In cell B2 you'll find the seed increase. This value should be added to the seed values in cells C4, D4, E4 and F4 of sheet RANDOM. So after the first run C4 goes from 1 to 2, and D4 goes from 10 to 11. After the second run, C4 goes from 2 to 3, and D4 goes from 11 to 12, etc. This way the four list of random numbers will be updates before the next run.

Thereafter the Sub Run_Model() macro should be run to calculate results. Finally (and this is the tricky bit), the results which can be found in the range B2:B5 of sheet OUTPUT should be copy/pasted to C2:C5 on the first run, but to D2:D5 on the second, and E2:E5 on the third, etc. So each time the model is repeated, the paste are moves to the right with one column.

Can anyone add this second macro to the file?

Please note: the attached file is not my actual model, but in terms of how the sheet relate to one another it is identical.

I'm not sure of your intentions.

perhaps this code does what you need

Please Login or Register  to view this content.

Vicho Palacios

Question: is it possible to add in a code to save the file before the next iteration line?

Originally Posted by Ronnet2

Question: is it possible to add in a code to save the file before the next iteration line?

Hi Rennet
Glad to know it worked for you
My mistake using 25 where should be nRounds, sory for that, but you fixdd it.
You mean to save the file after rumnnig the nRounds iterstions?
What is your protocole to name those files to be saved?
People use to save under the same string name plus a little ammend which chages every time following certain protocole
Vicho

Originally Posted by vichopalacios

Hi Rennet
Glad to know it worked for you
My mistake using 25 where should be nRounds, sory for that, but you fixdd it.
You mean to save the file after rumnnig the nRounds iterstions?
What is your protocole to name those files to be saved?
People use to save under the same string name plus a little ammend which chages every time following certain protocole
Vicho

It can save over the same file, after each run. Due to your macro the results of each run will all be saved (in different columns), so its ok to save over the same file.

The reason I wish to save between runs is because of the ''run_model'' macro. The actual model is much more complex than in this example file. Each run will take 1-2 hours, and I'll run the file for the next month every night and I fear Excel might crash somewhere along the way. So to avoid losing all the results, I'd like for the Excel file to save itself between runs. So should it crash during run 21, then at least I still have 20 runs of recorded results when I wake up

Ronnet2
you can insert:

Application.DisplayAlerts = False
Thisworkbook.Save
Application.DisplayAlerts = True