VBA-macro require a for next loop

montecarlo2012

Active Member
Joined
Jan 26, 2011
Messages
364
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,393
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,393
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

Active Member
Joined
Jan 26, 2011
Messages
364
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
35,541
Office Version
365
Platform
Windows
You need to replace $A$3:$A$20 with R3C1:R20C1
 

montecarlo2012

Active Member
Joined
Jan 26, 2011
Messages
364
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
35,541
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,089,270
Messages
5,407,303
Members
403,132
Latest member
Black_Mamba_1666

This Week's Hot Topics

Top