VBA  A code within a loop
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VBA A code within a loop

  1. #1
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA A code within a loop

    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:
    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.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,213
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA A code within a loop

    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 155
            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 by DanteAmor; Jul 18th, 2019 at 09:02 PM.
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA A code within a loop

    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:
     
    TREND AVERAGE forecast logarith power expon 2nd Poly 3erd.Poly
    9 11 14 7 3 5 11 9
    14 18 21 10 6 9 9 5
    23 28 32 17 15 21 13 10
    31 34 37 27 27 30 23 22
    36 40 44 32 32 36 26 34
    47 46 46 43 42 46 38 37
    number two
    Code:
    12 11 11 13 14 11 14 26
    18 18 18 17 15 16 15 21
    29 28 28 27 27 30 19 27
    34 34 34 31 31 34 25 27
    40 41 41 38 38 40 30 41
    50 47 44 50 51 50 46 49
    number three
    Code:
    14 11 8 17 20 14 15 35
    22 18 14 24 24 21 18 33
    33 28 24 32 34 34 22 34
    37 35 33 36 36 37 31 32
    42 41 40 40 40 42 31 37
    50 46 44 49 49 50 45 44
    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.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,213
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA A code within a loop

    Quote Originally Posted by montecarlo2012 View Post
    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:
    c = 2For 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.
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA A code within a loop

    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.

  6. #6
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA A code within a loop

    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.

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,213
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA A code within a loop

    Quote Originally Posted by montecarlo2012 View Post
    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 6
            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
    Regards Dante Amor

  8. #8
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA A code within a loop

    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.

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,213
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA A code within a loop

    Quote Originally Posted by montecarlo2012 View Post
    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 6
    Regards Dante Amor

  10. #10
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA A code within a loop

    DanteAmor You are G R E A T, THANK YOU SO MUCH. great lesson. awesome job.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •