need vba array/loop macro help

[michaelsmith559]

I have row numbers listed in column T and values I need in column U. I need to fill in the values between row numbers. Column(s) W and X contain the slope and intercept for the two rows and values. I need a macro that can list the row numbers in column T in order (maybe put into an array) and then the appropriate slope and intercept applied to it. The results will be placed in the corresponding rows in Sheet4, Column C. Column(s) T and U go from T1:T173; U1:U173. Column(s) W and X go from W2:W173; X2:X173. I am trying to automate the missing values. I don't want to have to list the range of numbers by dragging down a cell and copying the slope and intercept formula down the column and then copying and pasting the results into sheet4 Column C. Here is a screenshot of what I am wanting. Column Z is the row numbers listed and Column AA is the result of the slope/intercept formula. The values in AA would then be copied and pasted into sheet4 Column C. Thanks for any help.

Excel 2012
	T	U	V	W	X	Y	Z	AA
1	2353	0.337754		SLOPE	INTERCEPT		2353	0.337754
2	8298	0.025981		-5.24429E-05	0.4611523		2354	0.337702
3	22105	-0.19919		-1.63084E-05	0.1613078		2355	0.337649
4	25226	0.129905		0.000105445	-2.5300495		2356	0.337597
5	29937	-1.49824		-0.000345606	8.848153		2357	0.337544
6	33885	0.268471		0.000447496	-14.894928		2358	0.337492
7	34659	-1.68877		-0.002528736	85.9547		2359	0.337439
8	41356	1.151828		0.00042416	-16.389729		2360	0.337387
9	43757	0.874697		-0.000115423	5.9252785		2361	0.337335
10	44050	1.238432		0.001241417	-53.445992		2362	0.337282
11	47084	-0.63221		-0.000616558	28.39783		2363	0.33723
12	60327	0.770772		0.000105941	-5.6203397		2364	0.337177
13	65847	0.614886		-2.82403E-05	2.4744255		2365	0.337125
14	67821	-0.5456		-0.000587887	39.325468		2366	0.337072
15	72219	-1.23843		-0.000157533	10.138425		2367	0.33702
16	73485	-1.32504		-6.84073E-05	3.7018739		2368	0.336967
17	76307	-0.8747		0.000159581	-13.051878		2369	0.336915
18	86696	-0.49364		3.66788E-05	-3.673545		2370	0.336863
19	106719	0.199188		3.46017E-05	-3.4934659		2371	0.33681
20	108501	0.112585		-4.85991E-05	5.3856373		2372	0.336758
21	115628	-0.94398		-0.000148248	16.197652		2373	0.336705
22	120163	0.043302		0.000217703	-26.116496		2374	0.336653
23	128077	-0.32043		-4.5961E-05	5.5661114		2375	0.3366
Sheet7

Cell Formulas
Range	Formula
U1	=INDEX($B$1:$B$173,MATCH(T1,$A$1:$A$173,0))
U2	=INDEX($B$1:$B$173,MATCH(T2,$A$1:$A$173,0))
U3	=INDEX($B$1:$B$173,MATCH(T3,$A$1:$A$173,0))
U4	=INDEX($B$1:$B$173,MATCH(T4,$A$1:$A$173,0))
U5	=INDEX($B$1:$B$173,MATCH(T5,$A$1:$A$173,0))
U6	=INDEX($B$1:$B$173,MATCH(T6,$A$1:$A$173,0))
U7	=INDEX($B$1:$B$173,MATCH(T7,$A$1:$A$173,0))
U8	=INDEX($B$1:$B$173,MATCH(T8,$A$1:$A$173,0))
U9	=INDEX($B$1:$B$173,MATCH(T9,$A$1:$A$173,0))
U10	=INDEX($B$1:$B$173,MATCH(T10,$A$1:$A$173,0))
U11	=INDEX($B$1:$B$173,MATCH(T11,$A$1:$A$173,0))
U12	=INDEX($B$1:$B$173,MATCH(T12,$A$1:$A$173,0))
U13	=INDEX($B$1:$B$173,MATCH(T13,$A$1:$A$173,0))
U14	=INDEX($B$1:$B$173,MATCH(T14,$A$1:$A$173,0))
U15	=INDEX($B$1:$B$173,MATCH(T15,$A$1:$A$173,0))
U16	=INDEX($B$1:$B$173,MATCH(T16,$A$1:$A$173,0))
U17	=INDEX($B$1:$B$173,MATCH(T17,$A$1:$A$173,0))
U18	=INDEX($B$1:$B$173,MATCH(T18,$A$1:$A$173,0))
U19	=INDEX($B$1:$B$173,MATCH(T19,$A$1:$A$173,0))
U20	=INDEX($B$1:$B$173,MATCH(T20,$A$1:$A$173,0))
U21	=INDEX($B$1:$B$173,MATCH(T21,$A$1:$A$173,0))
U22	=INDEX($B$1:$B$173,MATCH(T22,$A$1:$A$173,0))
U23	=INDEX($B$1:$B$173,MATCH(T23,$A$1:$A$173,0))
AA1	=$W$2*Z1+$X$2
AA2	=$W$2*Z2+$X$2
AA3	=$W$2*Z3+$X$2
AA4	=$W$2*Z4+$X$2
AA5	=$W$2*Z5+$X$2
AA6	=$W$2*Z6+$X$2
AA7	=$W$2*Z7+$X$2
AA8	=$W$2*Z8+$X$2
AA9	=$W$2*Z9+$X$2
AA10	=$W$2*Z10+$X$2
AA11	=$W$2*Z11+$X$2
AA12	=$W$2*Z12+$X$2
AA13	=$W$2*Z13+$X$2
AA14	=$W$2*Z14+$X$2
AA15	=$W$2*Z15+$X$2
AA16	=$W$2*Z16+$X$2
AA17	=$W$2*Z17+$X$2
AA18	=$W$2*Z18+$X$2
AA19	=$W$2*Z19+$X$2
AA20	=$W$2*Z20+$X$2
AA21	=$W$2*Z21+$X$2
AA22	=$W$2*Z22+$X$2
AA23	=$W$2*Z23+$X$2
W2:X2	{=LINEST(U1:U2,T1:T2,TRUE,FALSE)}
W3:X3	{=LINEST(U2:U3,T2:T3,TRUE,FALSE)}
W4:X4	{=LINEST(U3:U4,T3:T4,TRUE,FALSE)}
W5:X5	{=LINEST(U4:U5,T4:T5,TRUE,FALSE)}
W6:X6	{=LINEST(U5:U6,T5:T6,TRUE,FALSE)}
W7:X7	{=LINEST(U6:U7,T6:T7,TRUE,FALSE)}
W8:X8	{=LINEST(U7:U8,T7:T8,TRUE,FALSE)}
W9:X9	{=LINEST(U8:U9,T8:T9,TRUE,FALSE)}
W10:X10	{=LINEST(U9:U10,T9:T10,TRUE,FALSE)}
W11:X11	{=LINEST(U10:U11,T10:T11,TRUE,FALSE)}
W12:X12	{=LINEST(U11:U12,T11:T12,TRUE,FALSE)}
W13:X13	{=LINEST(U12:U13,T12:T13,TRUE,FALSE)}
W14:X14	{=LINEST(U13:U14,T13:T14,TRUE,FALSE)}
W15:X15	{=LINEST(U14:U15,T14:T15,TRUE,FALSE)}
W16:X16	{=LINEST(U15:U16,T15:T16,TRUE,FALSE)}
W17:X17	{=LINEST(U16:U17,T16:T17,TRUE,FALSE)}
W18:X18	{=LINEST(U17:U18,T17:T18,TRUE,FALSE)}
W19:X19	{=LINEST(U18:U19,T18:T19,TRUE,FALSE)}
W20:X20	{=LINEST(U19:U20,T19:T20,TRUE,FALSE)}
W21:X21	{=LINEST(U20:U21,T20:T21,TRUE,FALSE)}
W22:X22	{=LINEST(U21:U22,T21:T22,TRUE,FALSE)}
W23:X23	{=LINEST(U22:U23,T22:T23,TRUE,FALSE)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[michaelsmith559]

Here is part of the code I am working with:

Sub ValuesBetweenDataPoints()
Dim myRow As Long
myRow = Range("AA1").Offset(0, -1)
Range("Z1") = 2353
Range("Z1").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Stop:=8298, Trend:=False
Range("AA1").Select
ActiveCell.Formula = "=$w$2*z1+$x$2"
Range("AA1").Copy Range("AA2", Range("Z1").End(xlDown).Offset(, 1))
Range(Range("AA1"), Range("AA1").End(xlDown)).Select
Selection.Copy
Sheets("Sheet4").Select
Cells(myRow, 3).PasteSpecial xlPasteValues
End Sub

 

[michaelsmith559]

Here is more code I have added. When I run the macro it gives an error on "Cells(myrow, 3).PasteSpecial xlPasteValues" Not sure what is wrong. Also how can I make this macro automatically loop through the row values and apply the correct slope/intercept formula. I am having to change: Range("Z1") = 2535; the stop value, and the activecell.formula everytime I run the macro. Can it be automated? Thanks for any help. Mike

Sub ValuesBetweenDataPoints()
Dim myRow As Long
myRow = Sheets("Sheet7").Range("Z1").Value
Range("Z1") = 67822
Range("Z1").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Stop:=72219, Trend:=False
Range("AA1").Select
ActiveCell.Formula = "=$W$15*z1+$X$15"
Range("AA1").Copy Range("AA2", Range("Z1").End(xlDown).Offset(, 1))
Range(Range("AA1"), Range("AA1").End(xlDown)).Select
Selection.Copy
Sheets("Sheet4").Select
Cells(myRow, 3).PasteSpecial xlPasteValues
ActiveCell.End(xlDown).Offset(1, 0).Select
Sheets("Sheet7").Select
Columns("Z:AA").Select
Selection.ClearContents
Range("Y1").Select
End Sub

 

[michaelsmith559]

Here is the code updated again, and getting closer to what I need. I need help placing the For R and For J loops. Everytime a next R starts a next J needs to start. Example, the range of values from T1 to T2 will have the slope/intercept in W2 and X2 applied to each value. The next time it loops for the new range of values from T2 to T3, it should use W3 and X3 for all of those ranges values, etc on down the column. Here is the code, obviously not correct yet.

Sub ValuesBetweenDataPoints()
Dim myRow As Long
j = Range("W2").End(xlDown).Row
myRow = Range("AA1").Offset(0, -1).Value
LastRow = Range("T1").End(xlDown).Row
For R = 1 To LastRow
Range("Z1").Value = Range("T" & R).Value
Range("Z1").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Stop:=Range("T" & R).Offset(1, 0), Trend:=False
For j = 2 To LastRow
Range("AA1").Select
ActiveCell.FormulaR1C1 = "=R" & [j] & "C23*RC[-1]+R" & [j] & "C24"
Range("AA1").Copy Range(("AA1"), Range("Z1").End(xlDown).Offset(, 1))
Range(Range("AA1"), Range("AA1").End(xlDown)).Select
Selection.Copy
Sheets("Sheet4").Select
Cells(myRow, 3).PasteSpecial xlPasteValues
ActiveCell.End(xlDown).Offset(1, 0).Select
Sheets("Sheet1").Select
Columns("Z:AA").Select
Selection.ClearContents
Range("Y1").Select
Next j
Next R
End Sub