Hi there,
I have data/time data and speed data in columns A and B as below.
You will see that the speed is logged every few seconds approx 3-6 secs. I would like to insert rows so that there is a row for every second. I have a macro that does this (see below). However I also need to insert speed values in the new blank rows also based. I need the new values to be evenly spread between the actual values that were recorded. So it would probably have to look at the difference between the two original speed values and divide it by the number of rows that were inserted plus one. Here an example of what I hope it can do
Heres the macro that inserts the new rows for each second. It would probably best to modify it.
I hope you can understand.
All help appreciated
John
I have data/time data and speed data in columns A and B as below.
Code:
Date Speed
28/03/2011 08:08:23 0
28/03/2011 08:08:26 12
28/03/2011 08:08:31 43
28/03/2011 08:08:37 50
28/03/2011 08:08:41 51
28/03/2011 08:08:47 51
28/03/2011 08:08:51 49
28/03/2011 08:08:57 45
28/03/2011 08:09:01 45
28/03/2011 08:09:07 50
28/03/2011 08:09:11 52
28/03/2011 08:09:17 24
28/03/2011 08:09:21 0You will see that the speed is logged every few seconds approx 3-6 secs. I would like to insert rows so that there is a row for every second. I have a macro that does this (see below). However I also need to insert speed values in the new blank rows also based. I need the new values to be evenly spread between the actual values that were recorded. So it would probably have to look at the difference between the two original speed values and divide it by the number of rows that were inserted plus one. Here an example of what I hope it can do
Code:
Date Speed
28/03/2011 08:08:23 0
28/03/2011 08:08:24 4
28/03/2011 08:08:25 8
28/03/2011 08:08:26 12
28/03/2011 08:08:27 18.2
28/03/2011 08:08:28 24.4
28/03/2011 08:08:29 30.6
28/03/2011 08:08:30 36.8
28/03/2011 08:08:31 43Heres the macro that inserts the new rows for each second. It would probably best to modify it.
Code:
Sub Insertrows()
Dim i As Long, j As Long
Dim FR As Long: FR = 2
Dim LR As Long: LR = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
Dim Str As String
Dim Tm1 As Date
Dim Tm2 As Date
Dim Tm3 As Integer
Dim Dif As Double
Dif = 1 / 24 / 60 / 60
For i = LR To (FR + 1) Step -1
Tm1 = Cells(i - 1, 1)
Tm2 = Cells(i, 1)
Tm3 = DateDiff("s", Tm1, Tm2)
If Tm3 > 1 Then
For j = (Tm3 - 1) To 1 Step -1
Rows(i).Insert Shift:=xlDown
Cells(i, 1) = Tm1 + (Dif * j)
Cells(i, 1).NumberFormat = "dd/mm/yyyy hh:mm:ss"
Next j
End If
Next i
End SubI hope you can understand.
All help appreciated
John
Last edited:
