how to do one calculation followed by another in vba?

[chuchu]

hi guys, i have two formulas, one formula makes a output. then another formula picks out those outputs that i want to use for analysis

the first formula works like a charm...but i am not sure how to integrated the 2nd formula. all the inputs from the first formula are the same. in the below examples M72:N72 are sum and average of theoutputs from range E3:G3 x 60


Sheets("oversimulator").Select
Dim i As Long
For i = 0 To 60
Range("E3:G3").Offset(i, 0) = Range("E1:G1").Value

Range("E1:G1").Calculate



' battingsimulator
Dim ii As Long
For ii = 0 To 500
Range("S10:T10").Offset(i, 0) = Range("M72:N72").Value

Range("M72:N72").Calculate

Next i
'

 

[StephenCrump]

Welcome to the Forum!

I am guessing that the results in E1:G1 have some random element, i.e. so that the values you are putting into E3:G63 will contain 61 simulations?

I am not clear what you're trying to do with your second loop, but I'm guessing that you're trying to get 501 sets of sums and averages (i.e. 501 sets of of 61 simulations)?

In which case, you have your loops around the wrong way, i.e. your first loop should be from 0 (1?) to 500, and within that loop you generate your 61 (60?) simulations and calculate the sum/average.

This may take some time. At minimum, I recommend setting Application.ScreenUpdating to False while the code is running. My preference would be to use a VBA array to calculate results, as it will be much faster.

 

[chuchu]

ur spot on what i am doing

-is thesolution just to this?




' battingsimulator
Dim ii As Long
For ii = 0 To 500
Range("S10:T10").Offset(i, 0) = Range("M72:N72").Value

Range("M72:N72").Calculate



Sheets("oversimulator").Select
Dim i As Long
For i = 0 To 60
Range("E3:G3").Offset(i, 0) = Range("E1:G1").Value

Range("E1:G1").Calculate

Next i

 

[StephenCrump]

You'll probably need to generate your first set of 60 numbers before you take the sum/average. Try it like this:

Dim i As Long, ii As Long
Const N1 = 50, N2 = 60

With Worksheets("oversimulator")
    For ii = 1 To N1
        For i = 1 To N2
            .Range("E2:G2").Offset(i).Value = .Range("E1:G1").Value
        Next i
        .Range("S9:T9").Offset(ii).Value = .Range("M72:N72").Value
    Next ii
End With

I have set N1 to 50, rather than 500, so it won't take as long to run. I have also assumed that all results are on the same "oversimulator" worksheet?

It'll be much faster working in VBA. Try this, which assumes that starting in S10:U10 you want averages of the 60 results in columns E,F and G.

Dim i As Long, ii As Long, j As Long
Dim vResults As Variant
Dim dSum() As Double, dAverage() As Double
Const N1 = 500, N2 = 60, COLS = 3

ReDim dAverage(1 To N1, 1 To COLS)

Application.ScreenUpdating = False

With Sheets("oversimulator")
    For ii = 1 To N1
        ReDim dSum(1 To COLS)
        For i = 1 To N2
            .Calculate
            vResults = .Range("E1").Resize(, COLS).Value
            For j = 1 To COLS
                dSum(j) = dSum(j) + vResults(1, j)
            Next j
        Next i
        For j = 1 To COLS
            dAverage(ii, j) = dSum(j) / N2
        Next j
    Next ii
    .Range("S10").Resize(N1, COLS).Value = dAverage
End With