increment time

M

Matt S

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
 
M

Matt S

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
 
B

Bob Phillips

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 said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top