Results 1 to 7 of 7

Thread: VBA-macro require a for next 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-macro require a for next loop

    Hello folks.
    PLEASE, I have a macro, and I see some kind of loop concept to apply here but still thinking, so PLEASE somebody can give me a hand here.
    Code:
     
    Sub Coe_fficient_trend_line()
    Range("P5").Formula = "=TRUNC(TREND(B3:B20))"
    Range("P6").Formula = "=TRUNC(TREND(C3:C20))"
    Range("P7").Formula = "=TRUNC(TREND(D3:D20))"
    Range("P8").Formula = "=TRUNC(TREND(E3:E20))"
    Range("P9").Formula = "=TRUNC(TREND(F3:F20))"
    Range("P10").Formula = "=TRUNC(TREND(G3:G20))"
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Range("Q5").Formula = "=TRUNC(FORECAST(17,B3:B20,$A$3:$A$20))"
    Range("Q6").Formula = "=TRUNC(FORECAST(17,c3:c20,$A$3:$A$20))"
    Range("Q7").Formula = "=TRUNC(FORECAST(17,d3:d20,$A$3:$A$20))"
    Range("Q8").Formula = "=TRUNC(FORECAST(17,e3:e20,$A$3:$A$20))"
    Range("Q9").Formula = "=TRUNC(FORECAST(17,f3:f20,$A$3:$A$20))"
    Range("Q10").Formula = "=TRUNC(FORECAST(17,g3:g20,$A$3:$A$20))"
    End Sub
    I can see something go from 5 to 10 and also somehting go from 3 to 20, thats all I can see it, so please guys I would like how FOR NEXT loop will be apply here without use [with....end with].
    thank you for reading this post.

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA-macro require a for next loop

    So, something like this....I've done the first sectio, you can do the rest, hopefully

    Code:
    Sub Coe_fficient_trend_line2()
    Dim c As Long, r As Long
    c = 2
    For r = 5 To 10
      Range("P" & r).FormulaR1C1 = "=TRUNC(TREND(R3C" & c & ":R20C" & c & "))"
      c = c + 1
    Next r
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA-macro require a for next loop

    This for the total code, but is UNTESTED

    Code:
    Sub Coe_fficient_trend_line2()
    Dim c As Long, r As Long
    c = 2
    For r = 5 To 10
    Range("P" & r).FormulaR1C1 = "=TRUNC(TREND(R3C" & c & ":R20C" & c & "))"
    Range("Q" & r).FormulaR1C1 = "=TRUNC(FORECAST(17,R3C" & c & ":R20C" & c & ",$A$3:$A$20))"
    c = c + 1
    Next r
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

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

    Default Re: VBA-macro require a for next loop

    Michael M. Thank you. and Good Morning. The first code work good, now I just tried the rest of my formulas and error occurred. Here it is the rest, I am trying. As a note the $A$3:$A$20 is just the digits from 1 to 20 in case this info is useful.
    I am really thanks full I got the lesson about B3:B20 is replaced by R!C!, good to know, books and youtube I watch do not explain like this, I really appreciate this Sir, you are very nice person doing this.
    Code:
    Sub Michael_M()
    Dim c As Long, r As Long
    c = 2
    For r = 5 To 10
      Range("P" & r).FormulaR1C1 = "=TRUNC(TREND(R3C" & c & ":R20C" & c & "))"
      Range("O" & r).FormulaR1C1 = "=trunc(average(R3C" & c & ":R20C" & c & "))"
      Range("Q" & r).FormulaR1C1 = "=TRUNC(FORECAST(17,R3C" & c & ":R20C" & c & ",$A$3:$A$20))"
      Range("R" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",LN($A$3:$A$20)),1,2))"
      Range("S" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R3C" & c & ":R20C" & c & "),LN($A$3:$A$20),,),1,2)))"
      Range("T" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R3C" & c & ":R20C" & c & "),$A$3:$A$20),1,2)))"
      Range("U" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",$A$3:$A$20^{1,2}),1,3))"
      Range("V" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",$A$3:$A$20^{1,2,3}),1,4))"
      c = c + 1
    Next r
    End Sub
    And the data Example is:
    Code:
    1 17 24 26 28 51 53
    2 5 15 31 32 45 47
    3 29 32 48 50 51 53
    4 8 14 27 38 49 51
    5 23 29 33 39 45 48
    6 16 22 36 38 42 43
    7 10 12 18 25 27 49
    8 1 11 12 24 31 33
    9 1 4 14 33 36 43
    10 18 27 28 30 35 43
    11 1 17 27 36 42 45
    12 2 4 21 27 32 53
    13 4 15 19 23 31 53
    14 2 25 31 45 52 49
    15 5 18 28 37 42 53
    16 18 22 34 39 48 50
    17 4 8 36 37 40 46
    18 9 18 22 37 44 53
    19 10 13 23 49 51 50
    20 13 17 19 23 39 34
    21 2 21 23 25 33 53
    22 11 29 30 37 45 32
    23 1 5 13 18 28 51
    24 5 23 24 26 29 48
    25 3 16 26 30 34 27
    26 8 9 19 23 26 50
    27 4 15 39 40 42 53
    28 20 25 34 35 46 51
    29 10 12 18 27 36 53
    30 14 30 32 34 52 52
    31 2 12 16 29 43 44
    32 9 27 31 37 41 49
    33 2 12 40 44 47 53
    34 2 5 10 35 51 47
    35 2 6 15 20 24 53

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA-macro require a for next loop

    You need to replace $A$3:$A$20 with R3C1:R20C1
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  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-macro require a for next loop

    Fluff, Sir. Good Morning.
    Thank you for taking the trouble to help me. I do appreciate it.
    Is working.
    Just in case somebody else are in the same spot I am posting the final results as a references.
    Code:
    Sub Michael_M_and_Fluff()
    Dim c As Long, r As Long
    c = 2
    For r = 5 To 10
      Range("P" & r).FormulaR1C1 = "=TRUNC(TREND(R3C" & c & ":R20C" & c & "))"
      Range("O" & r).FormulaR1C1 = "=trunc(average(R3C" & c & ":R20C" & c & "))"
      Range("Q" & r).FormulaR1C1 = "=TRUNC(FORECAST(17,R3C" & c & ":R20C" & c & ",R3C1:R20C1))"
      Range("R" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",LN(R3C1:R20C1)),1,2))"
      Range("S" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R3C" & c & ":R20C" & c & "),LN(R3C1:R20C1),,),1,2)))"
      Range("T" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R3C" & c & ":R20C" & c & "),R3C1:R20C1),1,2)))"
      Range("U" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",R3C1:R20C1^{1,2}),1,3))"
      Range("V" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R3C" & c & ":R20C" & c & ",R3C1:R20C1^{1,2,3}),1,4))"
      c = c + 1
    Next r
    End Sub

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA-macro require a for next loop

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •