VBA A code within a loop

montecarlo2012

Active Member
Joined
Jan 26, 2011
Messages
364
Hi.
I have a dynamic array at B3:G2650 and in A3:A20 the numbers from 1 to 17.
The double-code uploaded here return the coefficient trend line results at J3:Q10 respectively in this frame and the other just highlight the first row of the array on the report.
Until here everything is fine.
The REAL CHALLENGE start when I need to generate the same report as many times the range 17 go through the all 2650 rows.
What really mean is 2650/17= 155 times. So my question is: how to loop a code and get the results in different cells accordingly to the answers.
Example, if the first answer is on J3:Q10 the second is expected at T3:AA10, the third at J15:Q20 and the next at T15:AA20 and so on.
Code:
Sub Probably_N()
Cells(4, 10) = "TREND": Cells(4, 11) = "AVERAGE": Cells(4, 12) = "forecast": Cells(4, 17) = "3erd.Poly": Cells(4, 16) = "2nd Poly"
Cells(4, 15) = " expon": Cells(4, 14) = " power": Cells(4, 13) = "logarith"
    Dim rng As Range, fnd As Range
            For Each rng In Range("B3:g3")
                Set fnd = Range("J5:Q10").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
                        If Not fnd Is Nothing Then
                            fnd.Interior.ColorIndex = 6
                        End If
            Next rng
End Sub
Sub trend_Montecarlo()
Dim c As Long, r As Long
c = 2
For r = 5 To 10
         Range("J" & r).FormulaR1C1 = "=TRUNC(TREND(R3C" & c & ":R20C" & c & "))"
         Range("K" & r).FormulaR1C1 = "=trunc(average(R3C" & c & ":R20C" & c & "))"
         Range("L" & r).FormulaR1C1 = "=TRUNC(FORECAST(17,R3C" & c & ":R20C" & c & ",R3C1:R20C1))"
         Range("M" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",LN(R3C1:R20C1)),1,2))"
         Range("N" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R3C" & c & ":R20C" & c & "),LN(R3C1:R20C1),,),1,2)))"
         Range("O" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R3C" & c & ":R20C" & c & "),R3C1:R20C1),1,2)))"
         Range("P" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",R3C1:R20C1^{1,2}),1,3))"
         Range("Q" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",R3C1:R20C1^{1,2,3}),1,4))"
         c = c + 1
  Next r
and here is the report-example generate by this code

Code:
[TABLE="width: 597"]
<colgroup><col><col span="2"><col><col span="3"><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]2[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]TREND[/TD]
[TD]AVERAGE[/TD]
[TD]forecast[/TD]
[TD]logarith[/TD]
[TD] power[/TD]
[TD] expon[/TD]
[TD]2nd Poly[/TD]
[TD]3erd.Poly[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]37[/TD]
[/TR]
</tbody>[/TABLE]
Thank you for reading this post, have a nice night.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,192
Office Version
2007
Platform
Windows
I hope this helps you


Code:
Sub trend_Montecarlo()
    Dim c As Long, r As Long, i As Long, k As Long, n As Long
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    i = 5
    k = 10
    For n = 1 To [COLOR=#ff0000]155[/COLOR]
        c = 2
        For r = i To i + 5
            Cells(r, k) = "=TRUNC(TREND(R3C" & c & ":R20C" & c & "))"
            Cells(r, k + 1) = "=trunc(average(R3C" & c & ":R20C" & c & "))"
            Cells(r, k + 2) = "=TRUNC(FORECAST(17,R3C" & c & ":R20C" & c & ",R3C1:R20C1))"
            Cells(r, k + 3) = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",LN(R3C1:R20C1)),1,2))"
            Cells(r, k + 4) = "=TRUNC(EXP(INDEX(LINEST(LN(R3C" & c & ":R20C" & c & "),LN(R3C1:R20C1),,),1,2)))"
            Cells(r, k + 5) = "=TRUNC(EXP(INDEX(LINEST(LN(R3C" & c & ":R20C" & c & "),R3C1:R20C1),1,2)))"
            Cells(r, k + 6) = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",R3C1:R20C1^{1,2}),1,3))"
            Cells(r, k + 7) = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",R3C1:R20C1^{1,2,3}),1,4))"
            c = c + 1
        Next r
        If k = 10 Then
            k = 20
        Else
            k = 10
            i = i + 10
        End If
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "End"
End Sub
 
Last edited:

montecarlo2012

Active Member
Joined
Jan 26, 2011
Messages
364
DanteAmor Thank you So much for your input.
Definitely this structure repeat 155 time the code, the results are not what I expected, supposedly every frame have different results, because the code, is walking down, a every time the results of course return different numbers, I repeat the code manually 8 times and this is the expected results: number one
Code:
 [TABLE="width: 517"]
<colgroup><col span="2"><col><col span="3"><col span="2"></colgroup><tbody>[TR]
[TD]TREND[/TD]
[TD]AVERAGE[/TD]
[TD]forecast[/TD]
[TD]logarith[/TD]
[TD] power[/TD]
[TD] expon[/TD]
[TD]2nd Poly[/TD]
[TD]3erd.Poly[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]37[/TD]
[/TR]
</tbody>[/TABLE]
number two
Code:
[TABLE="width: 511"]
<colgroup><col span="3"><col><col><col span="3"></colgroup><tbody>[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]49[/TD]
[/TR]
</tbody>[/TABLE]
number three
Code:
[TABLE="width: 517"]
<colgroup><col span="2"><col><col span="3"><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]44[/TD]
[/TR]
</tbody>[/TABLE]
sorry I don't think is necesary keep going farther, so this is more or less three examples for the idea I am looking for; but really thank you so much Mr. DanteAmor for the help you provide.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,192
Office Version
2007
Platform
Windows
DanteAmor Thank you So much for your input.
Definitely this structure repeat 155 time the code, the results are not what I expected, supposedly every frame have different results, because the code, is walking down, a every time the results of course return different numbers, I repeat the code manually 8 times and this is the expected results: number one
sorry I don't think is necesary keep going farther, so this is more or less three examples for the idea I am looking for; but really thank you so much Mr. DanteAmor for the help you provide.

Code:
[COLOR=#ff0000]c = 2[/COLOR]For r = 5 To 10
         Range("J" & r).FormulaR1C1 = "=TRUNC(TREND(R3C" & c & ":R20C" & c & "))"
         Range("K" & r).FormulaR1C1 = "=trunc(average(R3C" & c & ":R20C" & c & "))"
         Range("L" & r).FormulaR1C1 = "=TRUNC(FORECAST(17,R3C" & c & ":R20C" & c & ",R3C1:R20C1))"
         Range("M" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",LN(R3C1:R20C1)),1,2))"
         Range("N" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R3C" & c & ":R20C" & c & "),LN(R3C1:R20C1),,),1,2)))"
         Range("O" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R3C" & c & ":R20C" & c & "),R3C1:R20C1),1,2)))"
         Range("P" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",R3C1:R20C1^{1,2}),1,3))"
         Range("Q" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",R3C1:R20C1^{1,2,3}),1,4))"
         c = c + 1
  Next r
That's your macro where you have a variable c (column), but I don't know how it should change so that it takes the new values.
That's why you have to adapt the macro I gave you.
Or explain to me how the formula should move.
 

montecarlo2012

Active Member
Joined
Jan 26, 2011
Messages
364
DanteAmor, Thank you for your kindness.
You are the Ninja here and I am the new-bee, so the only thing maybe I can say is; this Coefficient trend lines functions are a long algebra formulas, What about IF I show you what I change in the code every time I used maybe help, Sorry Sir, but I really appreciate you get involved in this little personal project, so here it is the changes:
Code:
For r = 5 To 10  Range("T" & r).FormulaR1C1 = "=TRUNC(TREND(R4C" & c & ":R21C" & c & "))"
  Range("U" & r).FormulaR1C1 = "=trunc(average(R4C" & c & ":R21C" & c & "))"
  Range("V" & r).FormulaR1C1 = "=TRUNC(FORECAST(17,R4C" & c & ":R21C" & c & ",R3C1:R20C1))"
  Range("W" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R4C" & c & ":R21C" & c & ",LN(R3C1:R20C1)),1,2))"
  Range("X" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R4C" & c & ":R21C" & c & "),LN(R3C1:R20C1),,),1,2)))"
  Range("Y" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R4C" & c & ":R21C" & c & "),R3C1:R20C1),1,2)))"
  Range("Z" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R4C" & c & ":R21C" & c & ",R3C1:R20C1^{1,2}),1,3))"
  Range("AA" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R4C" & c & ":R21C" & c & ",R3C1:R20C1^{1,2,3}),1,4))"
Changes here : T:AA and R4:R21
Code:
  For r = 15 To 20  Range("J" & r).FormulaR1C1 = "=TRUNC(TREND(R5C" & c & ":R22C" & c & "))"
  Range("K" & r).FormulaR1C1 = "=trunc(average(R5C" & c & ":R22C" & c & "))"
  Range("L" & r).FormulaR1C1 = "=TRUNC(FORECAST(17,R5C" & c & ":R22C" & c & ",R3C1:R20C1))"
  Range("M" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R5C" & c & ":R22C" & c & ",LN(R3C1:R20C1)),1,2))"
  Range("N" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R5C" & c & ":R22C" & c & "),LN(R3C1:R20C1),,),1,2)))"
  Range("O" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R5C" & c & ":R22C" & c & "),R3C1:R20C1),1,2)))"
  Range("P" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R5C" & c & ":R22C" & c & ",R3C1:R20C1^{1,2}),1,3))"
  Range("Q" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R5C" & c & ":R22C" & c & ",R3C1:R20C1^{1,2,3}),1,4))"
R5:R22
Code:
  For r = 15 To 20  Range("T" & r).FormulaR1C1 = "=TRUNC(TREND(R6C" & c & ":R23C" & c & "))"
  Range("U" & r).FormulaR1C1 = "=trunc(average(R6C" & c & ":R23C" & c & "))"
  Range("V" & r).FormulaR1C1 = "=TRUNC(FORECAST(17,R6C" & c & ":R23C" & c & ",R3C1:R20C1))"
  Range("W" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R6C" & c & ":R23C" & c & ",LN(R3C1:R20C1)),1,2))"
  Range("X" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R6C" & c & ":R23C" & c & "),LN(R3C1:R20C1),,),1,2)))"
  Range("Y" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R6C" & c & ":R23C" & c & "),R3C1:R20C1),1,2)))"
  Range("Z" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R6C" & c & ":R23C" & c & ",R3C1:R20C1^{1,2}),1,3))"
  Range("AA" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R6C" & c & ":R23C" & c & ",R3C1:R20C1^{1,2,3}),1,4))"
R6:R23
Code:
 For r = 25 To 30  Range("J" & r).FormulaR1C1 = "=TRUNC(TREND(R7C" & c & ":R24C" & c & "))"
  Range("K" & r).FormulaR1C1 = "=trunc(average(R7C" & c & ":R24C" & c & "))"
  Range("L" & r).FormulaR1C1 = "=TRUNC(FORECAST(17,R7C" & c & ":R24C" & c & ",R3C1:R20C1))"
  Range("M" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R7C" & c & ":R24C" & c & ",LN(R3C1:R20C1)),1,2))"
  Range("N" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R7C" & c & ":R24C" & c & "),LN(R3C1:R20C1),,),1,2)))"
  Range("O" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R7C" & c & ":R24C" & c & "),R3C1:R20C1),1,2)))"
  Range("P" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R7C" & c & ":R24C" & c & ",R3C1:R20C1^{1,2}),1,3))"
  Range("Q" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R7C" & c & ":R24C" & c & ",R3C1:R20C1^{1,2,3}),1,4))"            [CODE]
R7:R24
Please. Let me know IF this help some how. Sorry about this, I really tried on internet and books before here but there nothing about to putting a whole code inside a loop, or how really work the control variable when  you are in this king of forecasting problem, everywhere they show the same recipe FOR "control variable" start , end -do something next, with this little peace for me is really difficult to figure out what you are doing, you are awesome.
 

montecarlo2012

Active Member
Joined
Jan 26, 2011
Messages
364
Hi, just in case still want to help me, I would like to see at list the average formula working , meaning the average the only thing is doing is
sum(B3:B20)/17
sum(B4:B21)/17
sum(B5:B22)/17 and so on until the last row,
Thank you.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,192
Office Version
2007
Platform
Windows
DanteAmor, Thank you for your kindness.
You are the Ninja here and I am the new-bee, so the only thing maybe I can say is; this Coefficient trend lines functions are a long algebra formulas, What about IF I show you what I change in the code every time I used maybe help, Sorry Sir, but I really appreciate you get involved in this little personal project, so here it is the changes:

Please. Let me know IF this help some how. Sorry about this, I really tried on internet and books before here but there nothing about to putting a whole code inside a loop, or how really work the control variable when you are in this king of forecasting problem, everywhere they show the same recipe FOR "control variable" start , end -do something next, with this little peace for me is really difficult to figure out what you are doing, you are awesome.

your formula:
R7C" & c & ":R24C" & c & ",R3C1:R20

I think it should be

R7C" & c & ":R24C" & c & ",R7C1:R24


I updated it in the macro but try and tell me.


Let's try a cycle of 6.



Code:
Sub trend_Montecarlo()
    Dim c As Long, r As Long, i As Long, k As Long, n As Long
    Dim j As Long, m As Long
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Range("J5:AA31").ClearContents
    
    i = 5
    k = 10
    j = 3
    m = 20
    For n = 1 To [B]6[/B]
        c = 2
        For r = i To i + 5
            Cells(r, k).FormulaR1C1 = "=TRUNC(TREND(R" & j & "C" & c & ":R" & m & "C" & c & "))"
            Cells(r, k + 1) = "=trunc(average(R" & j & "C" & c & ":R" & m & "C" & c & "))"
            Cells(r, k + 2) = "=TRUNC(FORECAST(17,R" & j & "C" & c & ":R" & m & "C" & c & ",R" & j & "C1:R" & m & "C1))"
            Cells(r, k + 3) = "=TRUNC(INDEX(LINEST(R" & j & "C" & c & ":R" & m & "C" & c & ",LN(R" & j & "C1:R" & m & "C1)),1,2))"
            Cells(r, k + 4) = "=TRUNC(EXP(INDEX(LINEST(LN(R" & j & "C" & c & ":R" & m & "C" & c & "),LN(R" & j & "C1:R" & m & "C1),,),1,2)))"
            Cells(r, k + 5) = "=TRUNC(EXP(INDEX(LINEST(LN(R" & j & "C" & c & ":R" & m & "C" & c & "),R" & j & "C1:R" & m & "C1),1,2)))"
            Cells(r, k + 6) = "=TRUNC(INDEX(LINEST(R" & j & "C" & c & ":R" & m & "C" & c & ",R" & j & "C1:R" & m & "C1^{1,2}),1,3))"
            Cells(r, k + 7) = "=TRUNC(INDEX(LINEST(R" & j & "C" & c & ":R" & m & "C" & c & ",R" & j & "C1:R" & m & "C1^{1,2,3}),1,4))"
            c = c + 1
        Next r
        j = j + 1
        m = m + 1
        If k = 10 Then
            k = 20
        Else
            k = 10
            i = i + 10
        End If
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "End"
End Sub
 

montecarlo2012

Active Member
Joined
Jan 26, 2011
Messages
364
DanteAmor, First Thanks for doing this.
Ok, now talk about the code. Return the right results for only six times,
try and tell me.
so at this point, the only thing left to do is make it go to the final 17 rows of the Dynamic array. One more time Thanks.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,192
Office Version
2007
Platform
Windows
DanteAmor, First Thanks for doing this.
Ok, now talk about the code. Return the right results for only six times, so at this point, the only thing left to do is make it go to the final 17 rows of the Dynamic array. One more time Thanks.

Just change this by the desired number

Code:
For n = 1 To [B][COLOR=#0000ff]6[/COLOR][/B]
 

Forum statistics

Threads
1,089,333
Messages
5,407,642
Members
403,156
Latest member
Fahad Saleem

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top