# Thread: [VBA] Changing the range in the code FROM the spreadsheet Thanks:  1 Post #5312559 (1) Likes:  1 Post #5312559 (1)

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

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.

2. ## Re: [VBA] Changing the range in the code FROM the spreadsheet

have you thought about using a named range

3. ## Re: [VBA] Changing the range in the code FROM the spreadsheet

mole999 thank you for your intervention. Could you "PLEASE" give me the guidelines?. if you don't mind. Thank you Sir.

4. ## Re: [VBA] Changing the range in the code FROM the spreadsheet

your code should refer to the named range, so you only redfine the named range and the code should work the same > https://www.lifewire.com/define-and-...-excel-4178633

5. ## Re: [VBA] Changing the range in the code FROM the spreadsheet

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.