Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to increment time & subtract time? | Excel Worksheet Functions | |||
How to Increment Time using EXCEL ?? | Excel Programming | |||
Increment formula for time | Excel Discussion (Misc queries) | |||
An uneven time-increment | Charts and Charting in Excel | |||
Time and Date increment | Excel Discussion (Misc queries) |