need vba array/loop macro help

michaelsmith559

Well-known Member
Joined
Oct 6, 2013
Messages
842
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.

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">2353</td><td style="text-align: right;;">0.337754</td><td style="text-align: right;;"></td><td style="text-align: center;;">SLOPE</td><td style="text-align: center;;">INTERCEPT</td><td style="text-align: right;;"></td><td style="text-align: right;;">2353</td><td style="text-align: right;;">0.337754</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">8298</td><td style="text-align: right;;">0.025981</td><td style="text-align: right;;"></td><td style="text-align: center;;">-5.24429E-05</td><td style="text-align: center;;">0.4611523</td><td style="text-align: right;;"></td><td style="text-align: right;;">2354</td><td style="text-align: right;;">0.337702</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">22105</td><td style="text-align: right;;">-0.19919</td><td style="text-align: right;;"></td><td style="text-align: center;;">-1.63084E-05</td><td style="text-align: center;;">0.1613078</td><td style="text-align: right;;"></td><td style="text-align: right;;">2355</td><td style="text-align: right;;">0.337649</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">25226</td><td style="text-align: right;;">0.129905</td><td style="text-align: right;;"></td><td style="text-align: center;;">0.000105445</td><td style="text-align: center;;">-2.5300495</td><td style="text-align: right;;"></td><td style="text-align: right;;">2356</td><td style="text-align: right;;">0.337597</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">29937</td><td style="text-align: right;;">-1.49824</td><td style="text-align: right;;"></td><td style="text-align: center;;">-0.000345606</td><td style="text-align: center;;">8.848153</td><td style="text-align: right;;"></td><td style="text-align: right;;">2357</td><td style="text-align: right;;">0.337544</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">33885</td><td style="text-align: right;;">0.268471</td><td style="text-align: right;;"></td><td style="text-align: center;;">0.000447496</td><td style="text-align: center;;">-14.894928</td><td style="text-align: right;;"></td><td style="text-align: right;;">2358</td><td style="text-align: right;;">0.337492</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">34659</td><td style="text-align: right;;">-1.68877</td><td style="text-align: right;;"></td><td style="text-align: center;;">-0.002528736</td><td style="text-align: center;;">85.9547</td><td style="text-align: right;;"></td><td style="text-align: right;;">2359</td><td style="text-align: right;;">0.337439</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">41356</td><td style="text-align: right;;">1.151828</td><td style="text-align: right;;"></td><td style="text-align: center;;">0.00042416</td><td style="text-align: center;;">-16.389729</td><td style="text-align: right;;"></td><td style="text-align: right;;">2360</td><td style="text-align: right;;">0.337387</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">43757</td><td style="text-align: right;;">0.874697</td><td style="text-align: right;;"></td><td style="text-align: center;;">-0.000115423</td><td style="text-align: center;;">5.9252785</td><td style="text-align: right;;"></td><td style="text-align: right;;">2361</td><td style="text-align: right;;">0.337335</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">44050</td><td style="text-align: right;;">1.238432</td><td style="text-align: right;;"></td><td style="text-align: center;;">0.001241417</td><td style="text-align: center;;">-53.445992</td><td style="text-align: right;;"></td><td style="text-align: right;;">2362</td><td style="text-align: right;;">0.337282</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">47084</td><td style="text-align: right;;">-0.63221</td><td style="text-align: right;;"></td><td style="text-align: center;;">-0.000616558</td><td style="text-align: center;;">28.39783</td><td style="text-align: right;;"></td><td style="text-align: right;;">2363</td><td style="text-align: right;;">0.33723</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">60327</td><td style="text-align: right;;">0.770772</td><td style="text-align: right;;"></td><td style="text-align: center;;">0.000105941</td><td style="text-align: center;;">-5.6203397</td><td style="text-align: right;;"></td><td style="text-align: right;;">2364</td><td style="text-align: right;;">0.337177</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">65847</td><td style="text-align: right;;">0.614886</td><td style="text-align: right;;"></td><td style="text-align: center;;">-2.82403E-05</td><td style="text-align: center;;">2.4744255</td><td style="text-align: right;;"></td><td style="text-align: right;;">2365</td><td style="text-align: right;;">0.337125</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">67821</td><td style="text-align: right;;">-0.5456</td><td style="text-align: right;;"></td><td style="text-align: center;;">-0.000587887</td><td style="text-align: center;;">39.325468</td><td style="text-align: right;;"></td><td style="text-align: right;;">2366</td><td style="text-align: right;;">0.337072</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">72219</td><td style="text-align: right;;">-1.23843</td><td style="text-align: right;;"></td><td style="text-align: center;;">-0.000157533</td><td style="text-align: center;;">10.138425</td><td style="text-align: right;;"></td><td style="text-align: right;;">2367</td><td style="text-align: right;;">0.33702</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">73485</td><td style="text-align: right;;">-1.32504</td><td style="text-align: right;;"></td><td style="text-align: center;;">-6.84073E-05</td><td style="text-align: center;;">3.7018739</td><td style="text-align: right;;"></td><td style="text-align: right;;">2368</td><td style="text-align: right;;">0.336967</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">76307</td><td style="text-align: right;;">-0.8747</td><td style="text-align: right;;"></td><td style="text-align: center;;">0.000159581</td><td style="text-align: center;;">-13.051878</td><td style="text-align: right;;"></td><td style="text-align: right;;">2369</td><td style="text-align: right;;">0.336915</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">86696</td><td style="text-align: right;;">-0.49364</td><td style="text-align: right;;"></td><td style="text-align: center;;">3.66788E-05</td><td style="text-align: center;;">-3.673545</td><td style="text-align: right;;"></td><td style="text-align: right;;">2370</td><td style="text-align: right;;">0.336863</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">106719</td><td style="text-align: right;;">0.199188</td><td style="text-align: right;;"></td><td style="text-align: center;;">3.46017E-05</td><td style="text-align: center;;">-3.4934659</td><td style="text-align: right;;"></td><td style="text-align: right;;">2371</td><td style="text-align: right;;">0.33681</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">108501</td><td style="text-align: right;;">0.112585</td><td style="text-align: right;;"></td><td style="text-align: center;;">-4.85991E-05</td><td style="text-align: center;;">5.3856373</td><td style="text-align: right;;"></td><td style="text-align: right;;">2372</td><td style="text-align: right;;">0.336758</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">115628</td><td style="text-align: right;;">-0.94398</td><td style="text-align: right;;"></td><td style="text-align: center;;">-0.000148248</td><td style="text-align: center;;">16.197652</td><td style="text-align: right;;"></td><td style="text-align: right;;">2373</td><td style="text-align: right;;">0.336705</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">120163</td><td style="text-align: right;;">0.043302</td><td style="text-align: right;;"></td><td style="text-align: center;;">0.000217703</td><td style="text-align: center;;">-26.116496</td><td style="text-align: right;;"></td><td style="text-align: right;;">2374</td><td style="text-align: right;;">0.336653</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">128077</td><td style="text-align: right;;">-0.32043</td><td style="text-align: right;;"></td><td style="text-align: center;;">-4.5961E-05</td><td style="text-align: center;;">5.5661114</td><td style="text-align: right;;"></td><td style="text-align: right;;">2375</td><td style="text-align: right;;">0.3366</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet7</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U1</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T1,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U2</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T2,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U3</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T3,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U4</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T4,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U5</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T5,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U6</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T6,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U7</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T7,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U8</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T8,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U9</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T9,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U10</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T10,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U11</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T11,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U12</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T12,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U13</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T13,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U14</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T14,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U15</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T15,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U16</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T16,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U17</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T17,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U18</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T18,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U19</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T19,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U20</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T20,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U21</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T21,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U22</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T22,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">U23</th><td style="text-align:left">=INDEX(<font color="Blue">$B$1:$B$173,MATCH(<font color="Red">T23,$A$1:$A$173,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA1</th><td style="text-align:left">=$W$2*Z1+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA2</th><td style="text-align:left">=$W$2*Z2+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA3</th><td style="text-align:left">=$W$2*Z3+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA4</th><td style="text-align:left">=$W$2*Z4+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA5</th><td style="text-align:left">=$W$2*Z5+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA6</th><td style="text-align:left">=$W$2*Z6+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA7</th><td style="text-align:left">=$W$2*Z7+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA8</th><td style="text-align:left">=$W$2*Z8+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA9</th><td style="text-align:left">=$W$2*Z9+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA10</th><td style="text-align:left">=$W$2*Z10+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA11</th><td style="text-align:left">=$W$2*Z11+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA12</th><td style="text-align:left">=$W$2*Z12+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA13</th><td style="text-align:left">=$W$2*Z13+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA14</th><td style="text-align:left">=$W$2*Z14+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA15</th><td style="text-align:left">=$W$2*Z15+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA16</th><td style="text-align:left">=$W$2*Z16+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA17</th><td style="text-align:left">=$W$2*Z17+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA18</th><td style="text-align:left">=$W$2*Z18+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA19</th><td style="text-align:left">=$W$2*Z19+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA20</th><td style="text-align:left">=$W$2*Z20+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA21</th><td style="text-align:left">=$W$2*Z21+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA22</th><td style="text-align:left">=$W$2*Z22+$X$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">AA23</th><td style="text-align:left">=$W$2*Z23+$X$2</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W2:X2</th><td style="text-align:left">{=LINEST(<font color="Blue">U1:U2,T1:T2,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W3:X3</th><td style="text-align:left">{=LINEST(<font color="Blue">U2:U3,T2:T3,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W4:X4</th><td style="text-align:left">{=LINEST(<font color="Blue">U3:U4,T3:T4,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W5:X5</th><td style="text-align:left">{=LINEST(<font color="Blue">U4:U5,T4:T5,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W6:X6</th><td style="text-align:left">{=LINEST(<font color="Blue">U5:U6,T5:T6,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W7:X7</th><td style="text-align:left">{=LINEST(<font color="Blue">U6:U7,T6:T7,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W8:X8</th><td style="text-align:left">{=LINEST(<font color="Blue">U7:U8,T7:T8,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W9:X9</th><td style="text-align:left">{=LINEST(<font color="Blue">U8:U9,T8:T9,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W10:X10</th><td style="text-align:left">{=LINEST(<font color="Blue">U9:U10,T9:T10,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W11:X11</th><td style="text-align:left">{=LINEST(<font color="Blue">U10:U11,T10:T11,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W12:X12</th><td style="text-align:left">{=LINEST(<font color="Blue">U11:U12,T11:T12,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W13:X13</th><td style="text-align:left">{=LINEST(<font color="Blue">U12:U13,T12:T13,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W14:X14</th><td style="text-align:left">{=LINEST(<font color="Blue">U13:U14,T13:T14,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W15:X15</th><td style="text-align:left">{=LINEST(<font color="Blue">U14:U15,T14:T15,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W16:X16</th><td style="text-align:left">{=LINEST(<font color="Blue">U15:U16,T15:T16,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W17:X17</th><td style="text-align:left">{=LINEST(<font color="Blue">U16:U17,T16:T17,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W18:X18</th><td style="text-align:left">{=LINEST(<font color="Blue">U17:U18,T17:T18,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W19:X19</th><td style="text-align:left">{=LINEST(<font color="Blue">U18:U19,T18:T19,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W20:X20</th><td style="text-align:left">{=LINEST(<font color="Blue">U19:U20,T19:T20,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W21:X21</th><td style="text-align:left">{=LINEST(<font color="Blue">U20:U21,T20:T21,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W22:X22</th><td style="text-align:left">{=LINEST(<font color="Blue">U21:U22,T21:T22,TRUE,FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">W23:X23</th><td style="text-align:left">{=LINEST(<font color="Blue">U22:U23,T22:T23,TRUE,FALSE</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

michaelsmith559

Well-known Member
Joined
Oct 6, 2013
Messages
842
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
 

michaelsmith559

Well-known Member
Joined
Oct 6, 2013
Messages
842
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
 

michaelsmith559

Well-known Member
Joined
Oct 6, 2013
Messages
842
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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
Top