need vba array/loop macro help

michaelsmith559

Well-known Member
Joined
Oct 6, 2013
Messages
881
Office Version
  1. 2013
  2. 2007
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
TUVWXYZAA
123530.337754SLOPEINTERCEPT23530.337754
282980.025981-5.24429E-050.461152323540.337702
322105-0.19919-1.63084E-050.161307823550.337649
4252260.1299050.000105445-2.530049523560.337597
529937-1.49824-0.0003456068.84815323570.337544
6338850.2684710.000447496-14.89492823580.337492
734659-1.68877-0.00252873685.954723590.337439
8413561.1518280.00042416-16.38972923600.337387
9437570.874697-0.0001154235.925278523610.337335
10440501.2384320.001241417-53.44599223620.337282
1147084-0.63221-0.00061655828.3978323630.33723
12603270.7707720.000105941-5.620339723640.337177
13658470.614886-2.82403E-052.474425523650.337125
1467821-0.5456-0.00058788739.32546823660.337072
1572219-1.23843-0.00015753310.13842523670.33702
1673485-1.32504-6.84073E-053.701873923680.336967
1776307-0.87470.000159581-13.05187823690.336915
1886696-0.493643.66788E-05-3.67354523700.336863
191067190.1991883.46017E-05-3.493465923710.33681
201085010.112585-4.85991E-055.385637323720.336758
21115628-0.94398-0.00014824816.19765223730.336705
221201630.0433020.000217703-26.11649623740.336653
23128077-0.32043-4.5961E-055.566111423750.3366
Sheet7
Cell Formulas
RangeFormula
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Here is part of the code I am working with:

Code:
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
 
Upvote 0
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

Code:
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
 
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top