[VBA] Changing the range in the code FROM the spreadsheet

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
530
Office Version
2010
Platform
Windows
Hi Folks.
The code I am loading here is about to make calculation in the range ("B4:B18") "BUT" now I need to test the code in different ranges, example B4:B10, and after maybe B4:B22 and keep testing different ranges, so the "FAVOR, PLEASE" I need is type on ("I1") the start, and ("J1") the end, then I will be able to just type any value there and done.
Time ahead Thank you for reading this,
Code:
 Sub trend_Mrexcel()Dim c As Long, r As Long
c = 2
For r = 5 To 10
  Range("J" & r).FormulaR1C1 = "=TRUNC(TREND(R4C" & c & ":R18C" & c & "))"
  Range("K" & r).FormulaR1C1 = "=trunc(average(R4C" & c & ":R18C" & c & "))"
  Range("L" & r).FormulaR1C1 = "=TRUNC(FORECAST(18,R4C" & c & ":R18C" & c & ",R4C1:R18C1))"
  Range("M" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R4C" & c & ":R18C" & c & ",LN(R4C1:R18C1)),1,2))"
  Range("N" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R4C" & c & ":R18C" & c & "),LN(R4C1:R18C1),,),1,2)))"
  Range("O" & r).FormulaR1C1 = "=TRUNC(EXP(INDEX(LINEST(LN(R4C" & c & ":R18C" & c & "),R4C1:R18C1),1,2)))"
  Range("P" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R4C" & c & ":R18C" & c & ",R4C1:R18C1^{1,2}),1,3))"
  Range("Q" & r).FormulaR1C1 = "=TRUNC(INDEX(LINEST(R4C" & c & ":R18C" & c & ",R4C1:R18C1^{1,2,3}),1,4))"
  c = c + 1
  Next r
End Sub
Have a nice weekend.
 
Last edited:

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
530
Office Version
2010
Platform
Windows
mole999 thank you for your intervention. Could you "PLEASE" give me the guidelines?. if you don't mind. Thank you Sir.
 
Last edited:

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
530
Office Version
2010
Platform
Windows
mole999. Moderator. Sir. I am really thankful for the time you spent typing the website you suggested, for some reason wasn’t helpful, I am a beginner on this, so maybe that’s why.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,376
Messages
5,486,495
Members
407,549
Latest member
dommeehan

This Week's Hot Topics

Top