How to do Now + 1 second for range

[IREALLYambatman]

Hey guys, if I want to Increment the Range that I have setup below for Now + 1second and then Now+2s.. ect ect.. How do I do that? I tried Now + TIME(0,0,1).. and it gave a mismatch error.

Sub Fixer()
Worksheets("Default").Activate
Dim lRow As Long
lRow = Cells(Rows.Count, 3).End(xlUp).Row
MsgBox "Last Row: " & lRow
Worksheets("ELISA").Range(Range("B7"), Cells(lRow, 2)).Value = Now
Worksheets("ELISA").Range(Range("G7"), Cells(lRow, 7)).Value = "Water"
Worksheets("ELISA").Range(Range("Q7"), Cells(lRow, 17)).Value = "A"
Worksheets("ELISA").Range(Range("X7"), Cells(lRow, 24)).Value = "AO"
End Sub

 

[Rick Rothstein]

Time is a function in VB that returns the current time. You will need to use the TimeSerial function (same argument list you tried with TIME) in VB to create a time value from hour, minute, second components.

 

[IREALLYambatman]

Time is a function in VB that returns the current time. You will need to use the TimeSerial function (same argument list you tried with TIME) in VB to create a time value from hour, minute, second components.

God.. it's like I'm a genius! Thanks Rick! You rock!

For i = 1 To lRow    

Cells(i, 2).Value = Now + TimeSerial(0, 0, i)

Next i

 

[joeu2004]

For i = 1 To lRow
Cells(i, 2).Value = Now + TimeSerial(0, 0, i)
Next i

It would be more reliable to write:

Dim currTime as Double    ' I prefer Double instead of Date
currTime = Now
For i = 1 to lRow
     Cells(i, 2) = currTime + TimeSerial(0, 0, i)
Next

The important take-away is: do not call Now in a loop.

VBA Now is rounded to the second. But system time is normally updated every 15.625 milliseconds (1/64 seconds).

Even if your loop takes less than a second (very likely), if you start the loop near the end of the current second epoch, the value of Now might advance by one second during the loop.

Consequently, Now+Timeserial(0,0,i) might seem to increase by 2 seconds at least once.

 

[joeu2004]

It would be more reliable to write:
[....]
Cells(i, 2) = currTime + TimeSerial(0, 0, i)

In fact, if you intend to compare calculated time using an Excel lookup function (MATCH, VLOOKUP, etc) or VBA comparisons (e.g. x=y), it would be more reliable to write:

Cells(i, 2) = CDate(Format(currTime + TimeSerial(0, 0, i), "m/d/yyyy h:m:s"))

Caveat: You might need to customize "m/d/yyyy" and/or "h:m:s" for your localized region. I don't know.

That use of Format effectively rounds the calculated time to the same binary representation as if we had entered the time manually.

Otherwise, an infinitesimal arithmetic difference might arise, which affects lookup function and VBA comparisons, but usually not Excel logical comparisons (e.g. A1=B1). The infinitesimal difference is usually invisible, due to formatting limitations of Excel and VBA.