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

J

jwalkerack

Board Regular
Joined
Jun 19, 2013
Messages
81
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

[TABLE="width: 139"]
<tbody>[TR]
[TD]time [/TD]
[TD]Distance[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]67[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]89[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]189[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]220[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 139"]
<tbody>[TR]
[TD]time[/TD]
[TD]Distance[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]67[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]89[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]189[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]220[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 139"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]time [/TD]
[TD]Distance [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7.5[/TD]
[TD="align: right"]1.5[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]2.5[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]46[/TD]
[TD="align: right"]4.5[/TD]
[/TR]
[TR]
[TD="align: right"]67[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]78[/TD]
[TD="align: right"]5.5[/TD]
[/TR]
[TR]
[TD="align: right"]89[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]94.5[/TD]
[TD="align: right"]6.5[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]120[/TD]
[TD="align: right"]7.5[/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]164.5[/TD]
[TD="align: right"]8.5[/TD]
[/TR]
[TR]
[TD="align: right"]189[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]204.5[/TD]
[TD="align: right"]9.5[/TD]
[/TR]
[TR]
[TD="align: right"]220[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 139"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7.5[/TD]
[TD="align: right"]1.5[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]2.5[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]46[/TD]
[TD="align: right"]4.5[/TD]
[/TR]
[TR]
[TD="align: right"]67[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]78[/TD]
[TD="align: right"]5.5[/TD]
[/TR]
[TR]
[TD="align: right"]89[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]94.5[/TD]
[TD="align: right"]6.5[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]120[/TD]
[TD="align: right"]7.5[/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]164.5[/TD]
[TD="align: right"]8.5[/TD]
[/TR]
[TR]
[TD="align: right"]189[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]204.5[/TD]
[TD="align: right"]9.5[/TD]
[/TR]
[TR]
[TD="align: right"]220[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
<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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Run-time error '380' Could not set the Value property. Invalid property value
Replies
7
Views
799
aabrazil1
A
J
  • Solved
Coloured segments on a spider plot
Replies
1
Views
420
John David McKinley
J
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
269
Cquake
C
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
114
Fluff
Fluff
K
  • Question Question
populate amounts for column debit,credit for each customer from file to another
Replies
0
Views
333
KalilMe
K

Forum statistics

Threads
1,223,228
Messages
6,170,876
Members
452,363
Latest member
merico17

Share this page

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