Rollnation
New Member
- Joined
- Jan 17, 2017
- Messages
- 17
I have created some VBA to quickly run a monte carlo simulation of forecast errors to create prediction intervals for my forecasts.
The VBA is simply 1000 iterations of simulated forecast errors.
Question: What is the best way to ensure the VBA will run only on WS "MonteCarlo" in this workbook?
Also, this was a recorded macro that I cleaned up a bit but still needs work...
Sub Monte_Carlo()
' Monte_Carlo Macro
' Keyboard Shortcut: Ctrl+m
For i = 1 To 1000
Columns("P:P").Select
Selection.Insert Shift:=xlToRight
Range("L3:L127").Copy
Range("P3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Q3").Select
Application.CutCopyMode = False
Next i
ActiveCell.FormulaR1C1 = "=IFERROR(PERCENTILE.EXC(RC[2]:RC[1088],0.975),"""")"
Selection.AutoFill Destination:=Range("N3:N127"), Type:=xlFillDefault
Range("N3:N127").Select
ActiveWindow.SmallScroll Down:=-135
Range("O3").Select
ActiveCell.FormulaR1C1 = "=IFERROR(PERCENTILE.EXC(RC[4]:RC[1090],0.025),"""")"
Selection.AutoFill Destination:=Range("M3:M127"), Type:=xlFillDefault
Range("M3:M127").Select
End Sub
The VBA is simply 1000 iterations of simulated forecast errors.
Question: What is the best way to ensure the VBA will run only on WS "MonteCarlo" in this workbook?
Also, this was a recorded macro that I cleaned up a bit but still needs work...
Sub Monte_Carlo()
' Monte_Carlo Macro
' Keyboard Shortcut: Ctrl+m
For i = 1 To 1000
Columns("P:P").Select
Selection.Insert Shift:=xlToRight
Range("L3:L127").Copy
Range("P3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Q3").Select
Application.CutCopyMode = False
Next i
ActiveCell.FormulaR1C1 = "=IFERROR(PERCENTILE.EXC(RC[2]:RC[1088],0.975),"""")"
Selection.AutoFill Destination:=Range("N3:N127"), Type:=xlFillDefault
Range("N3:N127").Select
ActiveWindow.SmallScroll Down:=-135
Range("O3").Select
ActiveCell.FormulaR1C1 = "=IFERROR(PERCENTILE.EXC(RC[4]:RC[1090],0.025),"""")"
Selection.AutoFill Destination:=Range("M3:M127"), Type:=xlFillDefault
Range("M3:M127").Select
End Sub