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

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

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

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