VBA-macro require a for next loop

montecarlo2012

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

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,961
Office Version
2013
Platform
Windows
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,961
Office Version
2013
Platform
Windows
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
 

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
530
Office Version
2010
Platform
Windows
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:
[TABLE="width: 448"]
 <colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="class: xl65, width: 64, align: right"]1[/TD]
  [TD="class: xl65, width: 64, align: right"]17[/TD]
  [TD="class: xl65, width: 64, align: right"]24[/TD]
  [TD="class: xl65, width: 64, align: right"]26[/TD]
  [TD="class: xl65, width: 64, align: right"]28[/TD]
  [TD="class: xl65, width: 64, align: right"]51[/TD]
  [TD="class: xl65, width: 64, align: right"]53[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]15[/TD]
  [TD="class: xl65, align: right"]31[/TD]
  [TD="class: xl65, align: right"]32[/TD]
  [TD="class: xl65, align: right"]45[/TD]
  [TD="class: xl65, align: right"]47[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]29[/TD]
  [TD="class: xl65, align: right"]32[/TD]
  [TD="class: xl65, align: right"]48[/TD]
  [TD="class: xl65, align: right"]50[/TD]
  [TD="class: xl65, align: right"]51[/TD]
  [TD="class: xl65, align: right"]53[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]14[/TD]
  [TD="class: xl65, align: right"]27[/TD]
  [TD="class: xl65, align: right"]38[/TD]
  [TD="class: xl65, align: right"]49[/TD]
  [TD="class: xl65, align: right"]51[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]29[/TD]
  [TD="class: xl65, align: right"]33[/TD]
  [TD="class: xl65, align: right"]39[/TD]
  [TD="class: xl65, align: right"]45[/TD]
  [TD="class: xl65, align: right"]48[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]16[/TD]
  [TD="class: xl65, align: right"]22[/TD]
  [TD="class: xl65, align: right"]36[/TD]
  [TD="class: xl65, align: right"]38[/TD]
  [TD="class: xl65, align: right"]42[/TD]
  [TD="class: xl65, align: right"]43[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65, align: right"]25[/TD]
  [TD="class: xl65, align: right"]27[/TD]
  [TD="class: xl65, align: right"]49[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65, align: right"]24[/TD]
  [TD="class: xl65, align: right"]31[/TD]
  [TD="class: xl65, align: right"]33[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]14[/TD]
  [TD="class: xl65, align: right"]33[/TD]
  [TD="class: xl65, align: right"]36[/TD]
  [TD="class: xl65, align: right"]43[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65, align: right"]27[/TD]
  [TD="class: xl65, align: right"]28[/TD]
  [TD="class: xl65, align: right"]30[/TD]
  [TD="class: xl65, align: right"]35[/TD]
  [TD="class: xl65, align: right"]43[/TD]
 [/TR]
 [TR]
  [TD="class: xl66, align: right"]11[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]17[/TD]
  [TD="class: xl65, align: right"]27[/TD]
  [TD="class: xl65, align: right"]36[/TD]
  [TD="class: xl65, align: right"]42[/TD]
  [TD="class: xl65, align: right"]45[/TD]
 [/TR]
 [TR]
  [TD="class: xl66, align: right"]12[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]21[/TD]
  [TD="class: xl65, align: right"]27[/TD]
  [TD="class: xl65, align: right"]32[/TD]
  [TD="class: xl65, align: right"]53[/TD]
 [/TR]
 [TR]
  [TD="class: xl66, align: right"]13[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]15[/TD]
  [TD="class: xl65, align: right"]19[/TD]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]31[/TD]
  [TD="class: xl65, align: right"]53[/TD]
 [/TR]
 [TR]
  [TD="class: xl66, align: right"]14[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]25[/TD]
  [TD="class: xl65, align: right"]31[/TD]
  [TD="class: xl65, align: right"]45[/TD]
  [TD="class: xl65, align: right"]52[/TD]
  [TD="class: xl65, align: right"]49[/TD]
 [/TR]
 [TR]
  [TD="class: xl66, align: right"]15[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65, align: right"]28[/TD]
  [TD="class: xl65, align: right"]37[/TD]
  [TD="class: xl65, align: right"]42[/TD]
  [TD="class: xl65, align: right"]53[/TD]
 [/TR]
 [TR]
  [TD="class: xl66, align: right"]16[/TD]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65, align: right"]22[/TD]
  [TD="class: xl65, align: right"]34[/TD]
  [TD="class: xl65, align: right"]39[/TD]
  [TD="class: xl65, align: right"]48[/TD]
  [TD="class: xl65, align: right"]50[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]17[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]36[/TD]
  [TD="class: xl65, align: right"]37[/TD]
  [TD="class: xl65, align: right"]40[/TD]
  [TD="class: xl65, align: right"]46[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65, align: right"]22[/TD]
  [TD="class: xl65, align: right"]37[/TD]
  [TD="class: xl65, align: right"]44[/TD]
  [TD="class: xl65, align: right"]53[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]19[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]49[/TD]
  [TD="class: xl65, align: right"]51[/TD]
  [TD="class: xl65, align: right"]50[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]20[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]17[/TD]
  [TD="class: xl65, align: right"]19[/TD]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]39[/TD]
  [TD="class: xl65, align: right"]34[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]21[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]21[/TD]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]25[/TD]
  [TD="class: xl65, align: right"]33[/TD]
  [TD="class: xl65, align: right"]53[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]22[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]29[/TD]
  [TD="class: xl65, align: right"]30[/TD]
  [TD="class: xl65, align: right"]37[/TD]
  [TD="class: xl65, align: right"]45[/TD]
  [TD="class: xl65, align: right"]32[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65, align: right"]28[/TD]
  [TD="class: xl65, align: right"]51[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]24[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]24[/TD]
  [TD="class: xl65, align: right"]26[/TD]
  [TD="class: xl65, align: right"]29[/TD]
  [TD="class: xl65, align: right"]48[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]25[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]16[/TD]
  [TD="class: xl65, align: right"]26[/TD]
  [TD="class: xl65, align: right"]30[/TD]
  [TD="class: xl65, align: right"]34[/TD]
  [TD="class: xl65, align: right"]27[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]26[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]19[/TD]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]26[/TD]
  [TD="class: xl65, align: right"]50[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]27[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]15[/TD]
  [TD="class: xl65, align: right"]39[/TD]
  [TD="class: xl65, align: right"]40[/TD]
  [TD="class: xl65, align: right"]42[/TD]
  [TD="class: xl65, align: right"]53[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]28[/TD]
  [TD="class: xl65, align: right"]20[/TD]
  [TD="class: xl65, align: right"]25[/TD]
  [TD="class: xl65, align: right"]34[/TD]
  [TD="class: xl65, align: right"]35[/TD]
  [TD="class: xl65, align: right"]46[/TD]
  [TD="class: xl65, align: right"]51[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]29[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65, align: right"]27[/TD]
  [TD="class: xl65, align: right"]36[/TD]
  [TD="class: xl65, align: right"]53[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]30[/TD]
  [TD="class: xl65, align: right"]14[/TD]
  [TD="class: xl65, align: right"]30[/TD]
  [TD="class: xl65, align: right"]32[/TD]
  [TD="class: xl65, align: right"]34[/TD]
  [TD="class: xl65, align: right"]52[/TD]
  [TD="class: xl65, align: right"]52[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]31[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65, align: right"]16[/TD]
  [TD="class: xl65, align: right"]29[/TD]
  [TD="class: xl65, align: right"]43[/TD]
  [TD="class: xl65, align: right"]44[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]32[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]27[/TD]
  [TD="class: xl65, align: right"]31[/TD]
  [TD="class: xl65, align: right"]37[/TD]
  [TD="class: xl65, align: right"]41[/TD]
  [TD="class: xl65, align: right"]49[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]33[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65, align: right"]40[/TD]
  [TD="class: xl65, align: right"]44[/TD]
  [TD="class: xl65, align: right"]47[/TD]
  [TD="class: xl65, align: right"]53[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]34[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]35[/TD]
  [TD="class: xl65, align: right"]51[/TD]
  [TD="class: xl65, align: right"]47[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]35[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]15[/TD]
  [TD="class: xl65, align: right"]20[/TD]
  [TD="class: xl65, align: right"]24[/TD]
  [TD="class: xl65, align: right"]53[/TD]
[/TR]
</tbody>[/TABLE]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,610
Office Version
365
Platform
Windows
You need to replace $A$3:$A$20 with R3C1:R20C1
 

montecarlo2012

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,610
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,261
Messages
5,485,734
Members
407,511
Latest member
Arunabh

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top