Trendline Coeeficients Vba Procedure
 

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

Trendline Coeeficients Vba Procedure
 

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

Trendline Coeeficients Vba Procedure
 

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


wrote in message
oups.com...
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

Trendline Coeeficients Vba Procedure
 

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

Jerry

" wrote:

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