Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to jump from a Form procedure to a Workbook or Module procedure? | Excel Programming | |||
Calling a procedure in a procedure | Excel Programming | |||
Calling a procedure in a procedure | Excel Programming | |||
Calling a procedure in a procedure | Excel Programming |