Generate 1000 Iterations of a Calculated Value

[rwmill9716]

I have a program that randomly samples a population, then generates a value from that sampling. As shown in the image (and the table accessible from the web address included), columns E, G, I, K, and M use the randomly sampling function shown in row 4 to randomly sample data in column B. Five summary statistics are provided in row 6 and copied to columns P, Q, R, S, and T. When I hit F9 all, except column B data, change. I need a macro that hits F9 1,000 times then accumulates data for each iteration in columns P, Q, R, S, and T so finally, I'll have these data in rows 1 to 1,000.

http://www.triplescreenmethod.com/TSMNew/MonteCarloShare.xls

 

[StephenCrump]

Try:

Sub Test()

    Dim i As Long, j As Long, N As Long, k As Long
    Dim vResults As Variant, vResultsAll() As Double
    
    N = 1000
    k = 5
    ReDim vResultsAll(1 To N, 1 To k)
    Application.ScreenUpdating = False
    
    For i = 1 To N
        Calculate
        vResults = Range("F6:N6").Value
        For j = 1 To k
            vResultsAll(i, j) = vResults(1, j * 2 - 1)
        Next j
    Next i
    
    Range("P1").Resize(N, k).Value = vResultsAll
    Application.ScreenUpdating = True
    MsgBox "Finished"

End Sub

 

[rwmill9716]

Try:

Sub Test()

    Dim i As Long, j As Long, N As Long, k As Long
    Dim vResults As Variant, vResultsAll() As Double
   
    N = 1000
    k = 5
    ReDim vResultsAll(1 To N, 1 To k)
    Application.ScreenUpdating = False
   
    For i = 1 To N
        Calculate
        vResults = Range("F6:N6").Value
        For j = 1 To k
            vResultsAll(i, j) = vResults(1, j * 2 - 1)
        Next j
    Next i
   
    Range("P1").Resize(N, k).Value = vResultsAll
    Application.ScreenUpdating = True
    MsgBox "Finished"

End Sub

Stephen,
I get the attached error message when I run your macro. Thanks for working on this.
Ric

 

[rwmill9716]

Stephen,
I get the attached error message when I run your macro. Thanks for working on this.
Ric

Stephen, thanks, this works well.

 

[Peter_SSs]

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.