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? 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. Sub Total() Dim ObjErrorString Dim ObjErrorCell, ObjErrorTime Dim ObjOkCell, ObjOkTime Dim ObjTotal Set ObjErrorCell = Cells.Find(What:="ERROR", After:=ActiveCell, SearchOrder:=xlByColumns, SearchDirection:=xlNext) ObjErrorString = "ERROR" Do Until InStr(1, ObjErrorCell.Offset(1, 0).Value, ObjErrorString, vbTextCompare) < 1 ObjErrorCell = ObjErrorCell.Offset(1, 0) 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
|
|||
|
|||
You have another response at your initial thread.
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? 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. Sub Total() Dim ObjErrorString Dim ObjErrorCell, ObjErrorTime Dim ObjOkCell, ObjOkTime Dim ObjTotal Set ObjErrorCell = Cells.Find(What:="ERROR", After:=ActiveCell, SearchOrder:=xlByColumns, SearchDirection:=xlNext) ObjErrorString = "ERROR" Do Until InStr(1, ObjErrorCell.Offset(1, 0).Value, ObjErrorString, vbTextCompare) < 1 ObjErrorCell = ObjErrorCell.Offset(1, 0) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error on second pass of loop. | Excel Discussion (Misc queries) | |||
Do Loop | Excel Worksheet Functions | |||
Do Loop | Excel Worksheet Functions | |||
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) |