How do I shorten and optimize this code
Posted by Steve on January 21, 2001 12:03 PM
I have written this piece of code to calculate the time taken to do a particular job based on a cell entry in the form of 900-930 or 1015-100 etc. These are the timein-timeout values keyed into the cell in column 6 of that row on the sheet. The total time taken (in minutes) is then placed into the cell in column 23 of that row on the sheet. My question is this: Is there an easier way to do this or a more optimized way for execution speed?
---snip
If Cells(Row, 6) <> "" And IsNumeric(Left(Cells(Row, 6), 2)) Then
Dim tintout As Long
Dim inhour As Long
Dim inmin As Long
Dim outhout As Long
Dim outmin As Long
Dim lentio As Long
Dim tin As Long
Dim tout As Long
mycell = Cells(Row, 6)
hyphen = InStr(mycell, "-")
lentio = Len(Cells(Row, 6))
If lentio = 7 And hyphen = 4 Then
inhour = Left(mycell, 1) * 60
inmin = Mid(mycell, 2, 2)
outhour = Mid(mycell, 5, 1) * 60
outmin = Right(mycell, 2)
End If
If lentio = "8" And hyphen = "4" Then
inhour = Left(mycell, 1) * 60
inmin = Mid(mycell, 2, 2)
outhour = Mid(mycell, 5, 2) * 60
outmin = Right(mycell, 2)
End If
If lentio = "8" And hyphen = "5" Then
inhour = Left(mycell, 2) * 60
inmin = Mid(mycell, 3, 2)
outhour = Mid(mycell, 6, 1) * 60
outmin = Right(mycell, 2)
End If
If lentio = "9" And hyphen = "5" Then
inhour = Left(mycell, 2) * 60
inmin = Mid(mycell, 3, 2)
outhour = Mid(mycell, 6, 2) * 60
outmin = Right(mycell, 2)
End If
tin = inhour + inmin
tout = outhour + outmin
If tout < tin Then
tout = tout + 720
End If
Cells(Row, 23) = tout - tin
Else
Cells(Row, 23) = ""
End If
---snip