VBA A code within a loop

montecarlo2012

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
986
Office Version
  1. 2010
Platform
  1. 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.
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

2JKLMNOPQ
TRENDAVERAGEforecastlogarith power expon2nd Poly3erd.Poly
591114735119
6141821106995
72328321715211310
83134372727302322
93640443232362634
104746464342463837

Thank you for reading this post, have a nice night.
 
Last edited by a moderator:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
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:
Upvote 0
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
TRENDAVERAGEforecastlogarithpowerexpon2nd Poly3erd.Poly
91114735119
141821106995
2328321715211310
3134372727302322
3640443232362634
4746464342463837

number two
1211111314111426
1818181715161521
2928282727301927
3434343131342527
4041413838403041
5047445051504649

number three
141181720141535
2218142424211833
3328243234342234
3735333636373132
4241404040423137
5046444949504544

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.
 
Last edited by a moderator:
Upvote 0
montecarlo2012 said:
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.
Click to expand...


Rich (BB code): 
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

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.
 
Last edited by a moderator:
Upvote 0
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))"
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.
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0
montecarlo2012 said:
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.
Click to expand...


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
 
Upvote 0
DanteAmor, First Thanks for doing this.
Ok, now talk about the code. Return the right results for only six times,
try and tell me.
Click to expand...
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.
 
Upvote 0
montecarlo2012 said:
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.
Click to expand...


Just change this by the desired number

Code: 
For n = 1 To [B][COLOR=#0000ff]6[/COLOR][/B]
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
index match excel formula to vba code
Replies
9
Views
365
DanteAmor
DanteAmor
montecarlo2012
  • Question Question
[VBA] Changing the range in the code FROM the spreadsheet
Replies
4
Views
855
montecarlo2012
montecarlo2012
montecarlo2012
  • Question Question
VBA-macro require a for next loop
Replies
6
Views
959
Fluff
Fluff
montecarlo2012
  • Question Question
vba coefficient trend line input the range
Replies
0
Views
942
montecarlo2012
montecarlo2012
An Quala
  • Solved
VBA Code Mistake
Replies
5
Views
569
An Quala
An Quala

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top