I'm trying to run a macro that will essentially run a dirty version of a Monte Carlo simulation
My outcomes are
set up as [probability1, outcome1, probability2, outcome2...etc.] that are arranged horizontally within a row.
also have a column with 10,000 rand() variables set up.
I'm trying to use the following code
Private Sub monte()
Dim sumCell As Variant
Dim sum As Variant
For j = 1 To 100
'100 is the number of sims that I want to run
'Activecell will be the cell that I want the final sum to show up in
Set sumCell = Activecell.offset(j-1,0).Value
For i = 1 To 10000
'10000 is the number of iterations I want in the monte carlo
If Activecell.offset(i-1,-1) < Activecell.offset(i-1,17) Then
sum = sum + Activecell.offset(i-1,16)
If Activecell.offset(i-1,1) < Activecell.offset(i-1,15) Then
'etc. for the number of outcomes I have
Set sumcell = sum
Set Activecell.offset(j-1,0).Value = sumcell
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I then assigned the monte() macro to a shortkey of ctrl+k. Everytime I pressed ctrl+k, it erased the sheet I was
working on. I have no idea why it was doing that, but I just shut everything down without saving to preserve my worksheet.
I basically just want to call the sum to a cell, then I can average it to find monte carlo expectation (as I
said, pretty dirty as I don't want to run a distribution).
Anyone know what is going on?