montecarlo2012
Well-known Member
- Joined
- Jan 26, 2011
- Messages
- 986
- Office Version
- 2010
- Platform
- Windows
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.
and here is the report-example generate by this code
Thank you for reading this post, have a nice night.
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
2 | J | K | L | M | N | O | P | Q |
TREND | AVERAGE | forecast | logarith | power | expon | 2nd Poly | 3erd.Poly | |
5 | 9 | 11 | 14 | 7 | 3 | 5 | 11 | 9 |
6 | 14 | 18 | 21 | 10 | 6 | 9 | 9 | 5 |
7 | 23 | 28 | 32 | 17 | 15 | 21 | 13 | 10 |
8 | 31 | 34 | 37 | 27 | 27 | 30 | 23 | 22 |
9 | 36 | 40 | 44 | 32 | 32 | 36 | 26 | 34 |
10 | 47 | 46 | 46 | 43 | 42 | 46 | 38 | 37 |
Thank you for reading this post, have a nice night.
Last edited by a moderator: