inserting rows

[bradyj7]

Hi there,

I have a time stamp in column B and a speed in column C. However the first few seconds are always missed in the recording so the first speed value is usually 20 km/hr or above. This is an example

       Date/time	    Speed(km/h)
24/01/2011 08:04:29	25

I'm looking for a macro that will insert 5 rows before this and will linearly insert speed values from 0 what ever the speed value is. So it would look like this

Date/time	                     Speed(km/h)
24/01/2011 08:04:24	0
24/01/2011 08:04:25	5
24/01/2011 08:04:26	10
24/01/2011 08:04:27	15
24/01/2011 08:04:28	20
24/01/2011 08:04:29	25

Any ideas?

Thank you

 

[JamesW]

Hi,

Not perfect, but try:

Sub test()
    Dim i As Long
    Dim kmh As Double
    kmh = Range("B2").Value / 5
    For i = 1 To 5
        Range("A2").EntireRow.Insert
        Range("B2").Value = Range("B3").Value - kmh
        Range("A2").Value = Range("A3").Value - (1 / 60 / 60 / 24)
    Next i
    Range("A2:A7").NumberFormat = "dd/mm/yyyy hh:mm:ss"
End Sub

 

[Peter_SSs]

Similar but doing the 5 rows at once.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Insert_Values()<br>    Range("A2:B6").Insert Shift:=xlDown<br>    <SPAN style="color:#00007F">With</SPAN> Range("A2:B6")<br>        <SPAN style="color:#00007F">With</SPAN> .Resize(, 1)<br>            .Formula = "=A3-TIME(0,0,1)"<br>            .NumberFormat = "dd/mm/yyyy hh:mm:ss"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        .Offset(, 1).Resize(, 1).Formula = "=B3-0.2*B$7"<br>        .Value = .Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[bradyj7]

Hi,

Thanks for the replies but I cannot get it to work. This is what I have

Sub Insert_Values()
    Range("A2:AO6").Insert Shift:=xlDown
    With Range("A2:AO6")
        With .Resize(, 2)
            .Formula = "=A3-TIME(0,0,1)"
            .NumberFormat = "dd/mm/yyyy hh:mm:ss"
        End With
        .Offset(, 2).Resize(, 2).Formula = "=C3-0.2*C$7"
       .Value = .Value
    End With
End Sub

I am getting an error on

 .Value = .Value

But it still is not inserting time or speed in the 5 new rows.

Thanks for the help

John

 

[Peter_SSs]

1. What error are you getting? (The code does not error with the data I have, but see the next point)

2. This code seems to bear little resemblence to the originally stated problem:
- Original problem was about 2 columns (B:C). New code seems to have something to do with 41 columns, starting at column A.
- Original problem was to insert date/time in a single column (B). New code appears to be inserting date/time in two columns (A:B)
- Original problem was to insert speed in a single column (C). New code appears to be inserting speed in two columns (C:D)

Can you explain just what you are trying to do?

Can you provide sample data for cells A2:D2 that results in the error when you run this code?