Looping Worksheet & Capturing Output For Monte Carlo Analysis

[jklunde]

I'm relatively experienced with Excel (among normal people, relatively less so in this crowd) and have very little experience with VB. I'm trying to complete a Monte Carlo simulation to better understand distribution of returns for a specific financial analysis model.

I have built a worksheet that uses Excel's random variable generator to create a 100 period model, and I want to capture the outputs of these 100 periods for 5,000 or more iterations in another worksheet to analyze distibution of results.

I'm guessing a macro is the best way to accomplish this, but don't really know how to accomplish. Thanks!

Be easy on me, I'm a virgin poster.

 

[jklunde]

Here's a macro I recorded that does the first three iterations of what I'm looking for, although I'm sure there's a lot of garbage in here too from recording.

Calculate
Sheets("Randomized Flows").Select
Range("B38").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Iterations").Select
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Randomized Flows").Select
Application.CutCopyMode = False
Calculate
Selection.Copy
Sheets("Iterations").Select
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Randomized Flows").Select
Application.CutCopyMode = False
Calculate
Selection.Copy
Sheets("Iterations").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Basically, I want to:
calculate the sheet (F9)
copy cells B38:MW38 from sheet Randomized Flows
paste values to D2 in sheet Iterations

repeat 5000 times, just moving the cell for the last step down one row each iteration.

 

[Peter_SSs]

Welcome to the MrExcel board!

Test this on a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> jklunde()<br>    <SPAN style="color:#00007F">Dim</SPAN> iter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, NoCols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    iter = 10 <SPAN style="color:#007F00">'<-- change to suit no. of iterations required.</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Randomized Flows")<br>        <SPAN style="color:#00007F">Set</SPAN> myRange = .Range("B38", .Range("B38").End(xlToRight))<br>        NoCols = myRange.Columns.Count<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> r = 2 <SPAN style="color:#00007F">To</SPAN> 2 + iter - 1<br>        Sheets("Iterations").Cells(r, "D").Resize(, NoCols).Value = myRange.Value<br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Peter_SSs]

Just realised that you may have Calculation set to manual. If so, try this modification to the For...Next loop.

For r = 2 To 2 + iter - 1
    myRange.Calculate
    Sheets("Iterations").Cells(r, "D").Resize(, NoCols).Value = myRange.Value
Next r

 

[agbiggs]

This code mostly works for me, but it doesn't recalculate between iterations. I.e., it will recalculate the workbook once, then give me 10 rows of the same output. I'm a total VBA beginner so I don't know why this would, but figured I'd point it out since otherwise this code is really helpful in getting a start on Monte Carlo sims in Excel. Thanks.