how to do one calculation followed by another in vba?

chuchu

New Member
Joined
Jan 1, 2018
Messages
2
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
'
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,046
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 1, 2018
Messages
2
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,046
Office Version
  1. 365
Platform
  1. Windows
You'll probably need to generate your first set of 60 numbers before you take the sum/average. Try it like this:

Code:
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,303
Messages
5,600,857
Members
414,407
Latest member
Zaner0445

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top