#### montecarlo2012

##### Active Member

- Joined
- Jan 26, 2011

- Messages
- 364

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
```

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]
```