increment time
 

hi,

I'm trying to write a macro and got stuck trying to increment my time value.

Cell A6 has the following information for when I started my test:
Sun Jun 22 14:33:37 2008 (GMT-04:00)
Then A8 starts at zero, A9 = 1, A10=2, etc.

What I'd like to do is replace A8 with the time from A6 and then increment
the time by one second downward in the same format.

I know I can get the time stamp for A8 using the MID function, which I
successfully have done, but how do I make the next line increment the time?

Thanks so much!
Matt

increment time
 

This is what I've come up with below. Any suggestions?

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("G1").FormulaR1C1 = "=MID(R6C1,12,8)"
Range("G2").FormulaR1C1 = "=MID(R1C7,8,1)"
Range("G3").FormulaR1C1 = Range("G2") + 1
Range("G4").FormulaR1C1 = Mid(Range("G1"), 1, 7) & Range("G3").Value
Range("A8").Value = Range("G1").Value
Range("A9").Value = Range("G4").Value
Range("A8:A9").NumberFormat = "[$-F400]h:mm:ss AM/PM"
Range("A8:A9").AutoFill Destination:=Range("A8:A" & LastRow)
Range("G1:G4").Clear


"Matt S" wrote:

hi,

I'm trying to write a macro and got stuck trying to increment my time value.

Cell A6 has the following information for when I started my test:
Sun Jun 22 14:33:37 2008 (GMT-04:00)
Then A8 starts at zero, A9 = 1, A10=2, etc.

What I'd like to do is replace A8 with the time from A6 and then increment
the time by one second downward in the same format.

I know I can get the time stamp for A8 using the MID function, which I
successfully have done, but how do I make the next line increment the time?

Thanks so much!
Matt

increment time
 

Sub IncrementTime()
Dim LastRow As Long

Range("G1").FormulaR1C1 = "=--MID(R6C1,12,8)"
LastRow = (1 - Range("G1").Value) * 24 * 60 * 60
Range("G2").FormulaR1C1 = "=R1C7+TIME(0,0,1)"
Range("A8").Value = Range("G1").Value
Range("A9").Value = Range("G2").Value
Range("A8:A9").AutoFill Destination:=Range("A8:A" & LastRow + 8)
Range("A8:A" & LastRow + 8).NumberFormat = "hh:mm:ss"
Range("G1:G4").Clear
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Matt S" wrote in message
...
This is what I've come up with below. Any suggestions?

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("G1").FormulaR1C1 = "=MID(R6C1,12,8)"
Range("G2").FormulaR1C1 = "=MID(R1C7,8,1)"
Range("G3").FormulaR1C1 = Range("G2") + 1
Range("G4").FormulaR1C1 = Mid(Range("G1"), 1, 7) & Range("G3").Value
Range("A8").Value = Range("G1").Value
Range("A9").Value = Range("G4").Value
Range("A8:A9").NumberFormat = "[$-F400]h:mm:ss AM/PM"
Range("A8:A9").AutoFill Destination:=Range("A8:A" & LastRow)
Range("G1:G4").Clear


"Matt S" wrote:

hi,

I'm trying to write a macro and got stuck trying to increment my time
value.

Cell A6 has the following information for when I started my test:
Sun Jun 22 14:33:37 2008 (GMT-04:00)
Then A8 starts at zero, A9 = 1, A10=2, etc.

What I'd like to do is replace A8 with the time from A6 and then
increment
the time by one second downward in the same format.

I know I can get the time stamp for A8 using the MID function, which I
successfully have done, but how do I make the next line increment the
time?

Thanks so much!
Matt