Please explane

D

David Fisher

I have used the VB script for entering time but for some reason I don't
know why, but when I enter a single number in a col cell for "data" that
contains a single number I get 0.

In the address box it shows .004871111 for the number 7 I just enter.

I have format these col's for "data" numbers as number with no decimals.

If I enter #3 or a 4 (9845) figure number, I get invalid time popup.
When I click the popup, the numbers are enter. Why can I do this and not
7 or 21?

To make this more odd, I still get the same problem when I reopen the
workbook after I removed the VB code save the sheet and close the
workbook and VB editor. I have also reformat the cols for time to
00:00:00 too.

Any Idea what is going on?

How Do I get around the VB script for entering time so I can enter 1 or
2 figures numbers in cols not related to time? I have set the cols as
mytimecol to be used as range in the VB script

The following VB script is still attach to the workbook and it is only
to calculate the time it takes to get from one point to another.

Function RunTime(EndTime As Range) As Double
Dim StartTime As Double
Dim org As Range
Dim STstr As String, ETstr As String
Dim stH As Long, stMIN As Long, stSEC As Long
Dim etH As Long, etMIN As Long, etSEC As Long
Dim col As Long, EndCol As Long, rw As Long
Dim i As Long
Const startCol = 15 'Column O
Const LabelRow = 4
Dim temp As Double

Dim ar
Const c1 = "Bus Departs At", c2 = "Stop #" 'this defines the Start
Time

ar = Array(c1, c2)

EndCol = EndTime.Column - 1
rw = EndTime.Row

If EndTime.Value = 0 Then
RunTime = 0
Exit Function
End If

If Not IsNumeric(EndTime.Value) Then Exit Function

If EndTime = 0 Then
RunTime = 0
Exit Function
End If

StartTime = 0
For col = startCol To EndCol
If InStr(1, Cells(LabelRow, col), c1) + _
InStr(1, Cells(LabelRow, col), c2) > 0 Then
StartTime = Cells(rw, col).Value
End If
If StartTime > 0 Then Exit For
Next col

If StartTime = 0 Then
RunTime = 0
Exit Function
End If


STstr = Format(StartTime, "00:00:00")
ETstr = Format(EndTime, "00:00:00")

stH = Left(STstr, 2)
stMIN = Mid(STstr, 3, 2)
stSEC = Right(STstr, 2)

etH = Left(ETstr, 2)
etMIN = Mid(ETstr, 3, 2)
etSEC = Right(ETstr, 2)

temp = TimeSerial(etH, etMIN, etSEC) - TimeSerial(stH, stMIN, stSEC)

RunTime = CDbl(Format(temp, "hh:mm:ss"))



End Function
 

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

Similar Threads

Need code check 1
Check code 1
MyTimeCols 0
RunTime code problem 4
Is this right 2
Need help calculating time in VBA 1
Use Stopwatch to trigger event 2
Outlook 2010 Macro help please 0

Top