kathyb0527
09-19-2012, 02:10 PM
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.
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
Thanks for the help!
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
Thanks for the help!