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

#### montecarlo2012

##### Well-known Member
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.
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:

### 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).

#### mole999

##### Moderator
have you thought about using a named range

• montecarlo2012

#### montecarlo2012

##### Well-known Member
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
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.