Wait with TimeValue using the seconds from a worksheet cell

[Tom Joseph]

I am trying to substitue the following:

Application.Wait Now + TimeValue("0:0:5")

With a TimeValue that uses a cell value as the number of seconds, for
example from Worksheets("Tables").Range("C9")

Can someone please help with the proper syntax format?

Thanks,

 

[OssieMac]

Hi Tom,

The following example might help. A lot of the code could be placed in one
line of code but I separated the steps and included comments to help you
understand it more easily.

I assumed that the value in C9 is a simple number like 5 to represent 5
seconds. Is this assumption correct? If not then might have to re-look at the
code.

If you copy the code into a blank workbook and place a button from the Forms
Toolbar (not the Controls Toolbar) on a worksheet to run the code then you
can watch the values change in cells A1 and A2.

Feel free to get back to me if not what you need.

Sub TimeWaitTest()

Dim dblWaitTime As Double
Dim strWaitTime As String
Dim timeWaitTime As Date 'Note time variable is actually a date
Dim timeFinish As Date

'Format cells A1 and A2 to time
Range("A1:A2").NumberFormat = "hh:mm:ss"

'Following converts seconds entered as a number _
to a fraction of a day
dblWaitTime = Worksheets("Tables").Range("C9") / 24 / 60 / 60

'Following converts fraction of a day to a string _
that is formatted as time
strWaitTime = Format(dblWaitTime, "h:m:s")

'Convert string to time value
timeWaitTime = TimeValue(strWaitTime)

'Save the time to finish
timeFinish = Now() + timeWaitTime

Range("A1") = timeFinish
Do While Now() <= timeFinish
Range("A2") = Now()
Loop


End Sub

 

[OssieMac]

Hi agaion Tom,

You better discard that answer. I realize I was off in fairy land. You want
Wait not what I have given you. will have another look at it.

 

[OssieMac]

Hi Tom,

Try this one instead.

Sub TimeWaitTest2()

Dim dblWaitTime As Double
Dim strWaitTime As String
Dim timeWaitTime As Date 'Note time variable is actually a date
Dim timeFinish As Date

'Format cells A1 and A2 to time
Range("A1:A2").NumberFormat = "hh:mm:ss"

'Following converts seconds entered as a number _
to a fraction of a day
dblWaitTime = Worksheets("Tables").Range("C9") / 24 / 60 / 60

'Following converts fraction of a day to a string _
that is formatted as time
strWaitTime = Format(dblWaitTime, "h:m:s")

'Convert string to time value
timeWaitTime = TimeValue(strWaitTime)

Range("A1") = Now()
Application.Wait Now() + timeWaitTime
Range("A2") = Now()

 

[OssieMac]

I am definitely off in fairy land today Tom. My apologies for that. You don't
need all of my last answer either just the following.

Sub TimeWaitTest3()

Dim dblWaitTime As Double
Dim timeFinish As Date

'Format cells A1 and A2 to time
Range("A1:A2").NumberFormat = "hh:mm:ss"

'Following converts seconds entered as a number _
to a fraction of a day
dblWaitTime = Worksheets("Tables").Range("C9") / 24 / 60 / 60

Range("A1") = Now()
Application.Wait Now() + dblWaitTime
Range("A2") = Now()

End Sub

 

[Tom Joseph]

I greatly appreciate the help on this. I will give it a try.

Best,

Tom

 

[Dave Peterson]

application.wait now + timeserial(0,0,Worksheets("Tables").Range("C9").value)

You may want to add a check to see if that value makes sense.

 

[Tom Joseph]

Thanks, Dave.

I appreciate the help.

application.wait now + timeserial(0,0,Worksheets("Tables").Range("C9").value)

You may want to add a check to see if that value makes sense.