Find Times Greater than Time Entered

[kathyb0527]

I've written something that calculates the amount of time something has been stored (based on multiple inputs) and tells the user the maximum amount of time. If that time exceeds a specific time limit, I need to identify which ones are outside the limit (I used a pivot table and even the pivot table can be hundreds of lines long). Since the time is in hours, I can't figure out how to get the user input to match (or convert) the hours. For Example: If I use an inputbox, the user enters 4 for the number of hours, when the script searches, it looks for 4 not 0.1666667. If the user puts in 4:00, I get a type mismatch. I'm posting the script in case it helps with the explanation.


[VBA]Sub FindcritoutBT()
Dim c As Range
Dim i As Integer
Dim iBTtime As Long
Dim rPtime As Range
Dim strSampNo As String
Dim rSampName As Range


i = 2
iBTtime = Application.InputBox("Enter Maximum Number of Hours Established Stability")
Set rPtime = Worksheets("Pivot Table").Range("C3:C" & Range("A" & Rows.Count).End(xlUp).Row)

For Each c In rPtime
If c.Value > iBTtime Then
strSampNo = c.Offset(0, -2).Value
Worksheets("Bench Top").Activate
Cells.Find(What:=strSampNo, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, -14).Select
Selection.Copy
Sheets("Pivot Table").Select
Range("E" & i).Select
ActiveSheet.Paste
i = i + 1
End If
Next c
End Sub[/VBA]

Thanks for the help!

[GarysStudent]

I've written something that calculates the amount of time something has been stored (based on multiple inputs) and tells the user the maximum amount of time. If that time exceeds a specific time limit, I need to identify which ones are outside the limit (I used a pivot table and even the pivot table can be hundreds of lines long). Since the time is in hours, I can't figure out how to get the user input to match (or convert) the hours. For Example: If I use an inputbox, the user enters 4 for the number of hours, when the script searches, it looks for 4 not 0.1666667. If the user puts in 4:00, I get a type mismatch. I'm posting the script in case it helps with the explanation.


[vba]Sub FindcritoutBT()
Dim c As Range
Dim i As Integer
Dim iBTtime As Long
Dim rPtime As Range
Dim strSampNo As String
Dim rSampName As Range


i = 2
iBTtime = Application.InputBox("Enter Maximum Number of Hours Established Stability")
Set rPtime = Worksheets("Pivot Table").Range("C3:C" & Range("A" & Rows.Count).End(xlUp).Row)

For Each c In rPtime
If c.Value > iBTtime Then
strSampNo = c.Offset(0, -2).Value
Worksheets("Bench Top").Activate
Cells.Find(What:=strSampNo, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, -14).Select
Selection.Copy
Sheets("Pivot Table").Select
Range("E" & i).Select
ActiveSheet.Paste
i = i + 1
End If
Next c
End Sub[/vba]

Thanks for the help!

You need to take better control of the input:

[VBA]

Sub DoYouHaveTheTime()
Dim Inte As Integer, MyTime As Date, tDBL As Double
Inte = Application.InputBox(Prompt:="enter integer hours", Type:=1)
MyTime = TimeSerial(Inte, 0, 0)
tDBL = CDbl(MyTime)
MsgBox
MyTime & vbCrLf & tDBL
End Sub
[/VBA]

Then later in your code use either MyTime or tDBL, which ever works best.