# VBA A code within a loop

#### montecarlo2012

##### Well-known Member
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.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### DanteAmor

##### Well-known Member
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

##### Well-known Member
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
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

##### Well-known Member
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

##### Well-known Member
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
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.

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

##### Well-known Member
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
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]``

#### montecarlo2012

##### Well-known Member
DanteAmor You are G R E A T, THANK YOU SO MUCH. great lesson. awesome job.