Macro to Recalculate Spreadsheet [x] number of times

[bkanne]

Hi,

I'm trying to put together a macro that will recalculate a spreadsheet a specified number of times. In the uploaded example, I need the workbook to recalculate 1,000 times, per the figure specified in cell "D7".

In addition, I need cell "D9" to actively count the number of times the worksheet has been recalculated (in this case, from 1 to 1,000) and then revert to 1 afterwards (so it will show 1, 2, 3, 4, 5, etc all the way up to 1,000, and then go back to 1).

Can anyone help me with this?

Thank you so much!

 

[Hui]

Can you tell us what is the purpose of doing this?

 

[bkanne]

Absolutely.

In a spreadsheet that uses random functions, this setup will effectively simulate Monte-Carlo simulation when used in conjunction with self-referencing IF statements (the self-referencing IFs will capture and hold static each re-calculated result, but I can only record them if there is a counter cell that actively counts the number of recalculations).

 

[Hui]

I suspected as much which is why I asked

Have you read my post at
http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/

It explains how to achieve the same results without using VBA

 

[bkanne]

Thanks for the reply, some really helpful information there. I actually am already aware of this method, the idea is to create the simulation without using data tables at all, hence the need for VBA.

 

[Hui]

Here you go:

Sub Iterate()

Dim arr As Variant
Dim counter As Integer

Worksheets("Sheet1").Select
counter = Range("D7").Value

ReDim arr(1 To counter)

For i = 1 To counter

  Application.CalculateFull 'Calculate model
  arr(i) = Range("D7").Value 'load value to be stored into an array eg: NPV
  [D9].Value = i 'Update counter in cell D9
Next i

'Transfer results array to column E
Range("E11").Resize(counter, 1) = arr

End Sub

or see attached file

 

[bkanne]

Fantastic! Thank you Hui! I will give this a shot right now.

 

[bkanne]

This was extremely helpful - can't thank you enough. Worked perfectly.

I made a few minor cosmetic adjustments but overall this was just perfect. Thank you again.