Trendline Coeeficients Vba Procedure

N

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
 
M

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
 
P

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
 
J

Jerry W. Lewis

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

Jerry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top