Trendline Coeeficients Vba Procedure

[nitn28]

hi evrybody, this group is full of Vba experts hope sumbosy help me
out in my problem

i want to get Trendline Coeeficients for second degree polynomial i
have recorded macro to calculate coefficients

but i want VBa procedure which can compute coeeficients for variable
no. of rows

in the procedure below i have to change the value of "i"
which is i = 81 below i have to change evrytime dat varies how can i
calculate "i" dynmaically

Sub Button1_Click()

' y = p*x^2 + q*x + r

For j = 1 To 81 Step 5

x0 = Cells(j + 2, "a").Value
xn = Cells(j + 6, "a").Value

Cells(j + 2, "g").Value = x0
Cells(j + 2, "h").Value = xn

Cells(j + 2, "D").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(LINEST(RC[-2]:R[2]C[-2],RC[-3]:R[2]C[-3]^{1,2}),1)"
Cells(j + 2, "E").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(LINEST(RC[-3]:R[2]C[-3],RC[-4]:R[2]C[-4]^{1,2}),1,2)"
Cells(j + 2, "F").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(LINEST(RC[-4]:R[2]C[-4],RC[-5]:R[2]C[-5]^{1,2}),1,3)"

p = Cells(j + 2, "d").Value
q = Cells(j + 2, "e").Value
r = Cells(j + 2, "f").Value

hope somebody find time to give your valuable suggestions

thanx alot

 

[merjet]

I can only guess at your worksheet's layout, but maybe the following.

At the start:
Dim iEnd as Long
iEnd = Cells(65536, "a").End(xlUp).Row

Change: For j = 1 To 81 Step 5
To: For j = 1 To iEnd Step 5

Hth,
Merjet

 

[Peter T]

You would need to explain how and on what basis you want to calculate your
value i.

In passing, try this in your loop to avoid the selects and replace the Index
method replaced with array formulas

Dim sFmla as string
sFmla = "=LINEST(R[1]C[-2]:R[3]C[-2],R[1]C[-3]:R[3]C[-3]^{1,2})"

For j = 1 To 81 Step 5
Cells(j + 2, 4).Resize(, 3).FormulaArray = sFmla

Regards,
Peter T

 

[Guest]

Have you tried my answer to your 30 Apr post of this question? If it did not
meet your needs, in what way?

Jerry