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.
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.  Reply With Quote

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

have you thought about using a named range  Reply With Quote

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.  Reply With Quote

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  Reply With Quote

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.  Reply With Quote

User Tag List

Tags for this Thread

=truncindexlinestr4c, code, r.formular1c1, r18c, vba & excel 2010  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•