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

#### montecarlo2012

##### Active 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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### mole999

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

#### montecarlo2012

##### Active 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

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

1,089,337
Messages
5,407,678
Members
403,158
Latest member
Limerick2030