Home |
Search |
Today's Posts |
#1
|
|||
|
|||
error on second pass of loop.
What I am trying to do is find the total amount of time my site goes down. It
all works when there is only one error in a row, but when it enters the loop it crashes on it's second pass. Any Suggestions? Sub Total() Dim ObjErrorString, ObjOkString Dim ObjErrorCell, ObjErrorTime Dim ObjOkCell, ObjOkTime Dim ObjTotal Set ObjErrorCell = Cells.Find(What:="ERROR", After:=ActiveCell, SearchOrder:=xlByColumns, SearchDirection:=xlNext) ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) Do Until ObjErrorString < "ERROR" ObjErrorCell = ObjErrorCell.Offset(1, 0) Set ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) Loop ObjErrorCell.Select Set ObjErrorTime = ActiveCell.Offset(0, -ActiveCell.Column + 1) Set ObjOkCell = Cells.Find(What:="OK", After:=ActiveCell, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) ObjOkCell.Select Set ObjOkTime = ActiveCell.Offset(0, -ActiveCell.Column + 1) ObjTotal = ObjOkTime - ObjErrorTime Range("E389").Value = ObjTotal End Sub |
#2
|
|||
|
|||
I don't understand what you're doing, but this line:
Set ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) should be: ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) or Let ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) Left() is returning a string and Set is used with object variables. If this doesn't help get you a solution, you may want to post more details about how your data is laid out and what you want done. Rob wrote: What I am trying to do is find the total amount of time my site goes down. It all works when there is only one error in a row, but when it enters the loop it crashes on it's second pass. Any Suggestions? Sub Total() Dim ObjErrorString, ObjOkString Dim ObjErrorCell, ObjErrorTime Dim ObjOkCell, ObjOkTime Dim ObjTotal Set ObjErrorCell = Cells.Find(What:="ERROR", After:=ActiveCell, SearchOrder:=xlByColumns, SearchDirection:=xlNext) ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) Do Until ObjErrorString < "ERROR" ObjErrorCell = ObjErrorCell.Offset(1, 0) Set ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) Loop ObjErrorCell.Select Set ObjErrorTime = ActiveCell.Offset(0, -ActiveCell.Column + 1) Set ObjOkCell = Cells.Find(What:="OK", After:=ActiveCell, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) ObjOkCell.Select Set ObjOkTime = ActiveCell.Offset(0, -ActiveCell.Column + 1) ObjTotal = ObjOkTime - ObjErrorTime Range("E389").Value = ObjTotal End Sub -- Dave Peterson |
#3
|
|||
|
|||
Here is how my spread sheat is laid out and what i am trying to do:
Column 'A' = Date/Time of check Column 'D' = Check results I want to scan down Column 'D' and find what date/time an error occured, and if there is an error directly below that use that as the date/time. when i have the date/time of the last CONSECUTIVE error, I then want to search going up for the time above where it is OK. and record the difference in times. The suggestion below did not work, but i have taken out the word Set, thank you. "Dave Peterson" wrote: I don't understand what you're doing, but this line: Set ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) should be: ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) or Let ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) Left() is returning a string and Set is used with object variables. If this doesn't help get you a solution, you may want to post more details about how your data is laid out and what you want done. Rob wrote: What I am trying to do is find the total amount of time my site goes down. It all works when there is only one error in a row, but when it enters the loop it crashes on it's second pass. Any Suggestions? Sub Total() Dim ObjErrorString, ObjOkString Dim ObjErrorCell, ObjErrorTime Dim ObjOkCell, ObjOkTime Dim ObjTotal Set ObjErrorCell = Cells.Find(What:="ERROR", After:=ActiveCell, SearchOrder:=xlByColumns, SearchDirection:=xlNext) ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) Do Until ObjErrorString < "ERROR" ObjErrorCell = ObjErrorCell.Offset(1, 0) Set ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) Loop ObjErrorCell.Select Set ObjErrorTime = ActiveCell.Offset(0, -ActiveCell.Column + 1) Set ObjOkCell = Cells.Find(What:="OK", After:=ActiveCell, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) ObjOkCell.Select Set ObjOkTime = ActiveCell.Offset(0, -ActiveCell.Column + 1) ObjTotal = ObjOkTime - ObjErrorTime Range("E389").Value = ObjTotal End Sub -- Dave Peterson |
#4
|
|||
|
|||
So there's only one group of Errors and those rows are all grouped together?
And there's at least one OK above that Group? Option Explicit Sub testme() Dim ErrorCell As Range Dim OKCell As Range Dim wks As Worksheet Dim myRng As Range Set wks = Worksheets("Sheet1") With wks Set myRng = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp)) End With With myRng 'by starting at the top cell, we can look from the bottom up Set ErrorCell = .Cells.Find(what:="Error", _ after:=.Cells(1), LookIn:=xlValues, _ lookat:=xlWhole, searchorder:=xlByRows, _ searchdirection:=xlPrevious, MatchCase:=False) If ErrorCell Is Nothing Then MsgBox "No Error was found!" Exit Sub End If 'now start at the error cell and look up the range Set OKCell = .Cells.Find(what:="OK", _ after:=ErrorCell, LookIn:=xlValues, _ lookat:=xlWhole, searchorder:=xlByRows, _ searchdirection:=xlPrevious, MatchCase:=False) If OKCell Is Nothing Then MsgBox "Error cell found, but no OK cell found!" Exit Sub End If If OKCell.Row ErrorCell.Row Then MsgBox "Only ok's after the last error cell!" Exit Sub End If MsgBox ErrorCell.Address(0, 0) & vbLf & OKCell.Address(0, 0) End With With wks.Range("E389") .Value = ErrorCell.Offset(0, -3).Value - OKCell.Offset(0, -3).Value .NumberFormat = "[hh]:ss:mm" End With End Sub Rob wrote: Here is how my spread sheat is laid out and what i am trying to do: Column 'A' = Date/Time of check Column 'D' = Check results I want to scan down Column 'D' and find what date/time an error occured, and if there is an error directly below that use that as the date/time. when i have the date/time of the last CONSECUTIVE error, I then want to search going up for the time above where it is OK. and record the difference in times. The suggestion below did not work, but i have taken out the word Set, thank you. "Dave Peterson" wrote: I don't understand what you're doing, but this line: Set ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) should be: ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) or Let ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) Left() is returning a string and Set is used with object variables. If this doesn't help get you a solution, you may want to post more details about how your data is laid out and what you want done. Rob wrote: What I am trying to do is find the total amount of time my site goes down. It all works when there is only one error in a row, but when it enters the loop it crashes on it's second pass. Any Suggestions? Sub Total() Dim ObjErrorString, ObjOkString Dim ObjErrorCell, ObjErrorTime Dim ObjOkCell, ObjOkTime Dim ObjTotal Set ObjErrorCell = Cells.Find(What:="ERROR", After:=ActiveCell, SearchOrder:=xlByColumns, SearchDirection:=xlNext) ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) Do Until ObjErrorString < "ERROR" ObjErrorCell = ObjErrorCell.Offset(1, 0) Set ObjErrorString = Left(ObjErrorCell.Offset(1, 0).Value, 5) Loop ObjErrorCell.Select Set ObjErrorTime = ActiveCell.Offset(0, -ActiveCell.Column + 1) Set ObjOkCell = Cells.Find(What:="OK", After:=ActiveCell, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) ObjOkCell.Select Set ObjOkTime = ActiveCell.Offset(0, -ActiveCell.Column + 1) ObjTotal = ObjOkTime - ObjErrorTime Range("E389").Value = ObjTotal End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB for excel, how do I loop through code | Excel Discussion (Misc queries) | |||
loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) | |||
Printe Autofilter Criterias in a Loop | Excel Discussion (Misc queries) | |||
How to pass values of a cell? | Excel Discussion (Misc queries) | |||
pass fail | New Users to Excel |