Insert row bewteen every data set , then calcuate the differnce and populate

[jwalkerack]

Hello everyone , i m not sure if i m saying this right but here goes . i have data set of two values . For example time versus distance. For each time value there is a corresponding distance. i would like to be able have a macro that inserts a row between every time and distance point i have . Next i would like to have a macro that automatically calcuates and populates the difference between the rows.

So my original data set would be as follows

time	Distance
6	1
9	2
15	3
25	4
67	5
89	6
100	7
140	8
189	9
220

<tbody>
</tbody>

This is it with inserted rows one after each column that has data

time	Distance
6	1
9	2
15	3
25	4
67	5
89	6
100	7
140	8
189	9
220	10

<tbody>
</tbody>

i know the calculation to work out the difference between the 2 value. Is the bottom value subtracted by the top value and then halved and added to the bottom value . IE 2-1= 1/2= 0.5 +1= 1.5 so 1.5 would be the second row entry for distance field . Is it possible to have a two macros that will constantly allow me to double my data set ?


i want to have something like this in the end and be able to repeat the several times , each time increasing my dataset

time	Distance
6	1
7.5	1.5
9	2
12	2.5
15	3
20	3.5
25	4
46	4.5
67	5
78	5.5
89	6
94.5	6.5
100	7
120	7.5
140	8
164.5	8.5
189	9
204.5	9.5
220	10

<colgroup><col><col></colgroup><tbody>
</tbody>

6	1
7.5	1.5
9	2
12	2.5
15	3
20	3.5
25	4
46	4.5
67	5
78	5.5
89	6
94.5	6.5
100	7
120	7.5
140	8
164.5	8.5
189	9
204.5	9.5
220	10

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[sijpie]

<font face=Courier New>Option Explicit<br><br><br><SPAN style="color:#00007F">Sub</SPAN> InterpolateTD()<br><SPAN style="color:#007F00">' Set cursor somewhere in range _<br>  where interpolation is required. _<br>  The macro will insert a row between _<br>  each of the existing rows and interpolate _<br>  between the data.</SPAN><br>  <br>    <SPAN style="color:#00007F">Dim</SPAN> rRng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> lRC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#007F00">' set range object to topp left cell of range</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rRng = ActiveCell.CurrentRegion.Cells(1, 1)<br>    <br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> rRng <> vbNullString<br>        <SPAN style="color:#00007F">If</SPAN> IsNumeric(rRng) And rRng.Offset(0, 1) <> vbNullString <SPAN style="color:#00007F">Then</SPAN><br>            rRng.Offset(1, 0).EntireRow.Insert<br>            <SPAN style="color:#00007F">With</SPAN> rRng.Offset(1, 0)<br>                .Value = (rRng.Value + rRng.Offset(2, 0).Value) / 2<br>                .Offset(0, 1).Value = (rRng.Offset(0, 1).Value + rRng.Offset(2, 1).Value) / 2<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#007F00">' set the range 2 rows down to repeat the action</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rRng = rRng.Offset(2, 0)<br>    <SPAN style="color:#00007F">Loop</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>