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

error on second pass of loop
 

It is difficult to see exactly what is going on here since you have decalred
everything as variant (which also makes for very slow inneficient code). The
first thing I notice is that you need to use a set statement on this line

change from
ObjErrorCell = ObjErrorCell.Offset(1, 0)
to
Set ObjErrorCell = ObjErrorCell.Offset(1, 0)

That is the first thing I notice, but as I said it is tough to tell with
everything set as variant.
--
HTH...

Jim Thomlinson


"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

error on second pass of loop
 

what should i be declaring them as? i assume ObjErrorString as string, but
what about the rest?

Dim ObjErrorString
Dim ObjErrorCell, ObjErrorTime
Dim ObjOkCell, ObjOkTime
Dim ObjTotal

"Jim Thomlinson" wrote:

It is difficult to see exactly what is going on here since you have decalred
everything as variant (which also makes for very slow inneficient code). The
first thing I notice is that you need to use a set statement on this line

change from
ObjErrorCell = ObjErrorCell.Offset(1, 0)
to
Set ObjErrorCell = ObjErrorCell.Offset(1, 0)

That is the first thing I notice, but as I said it is tough to tell with
everything set as variant.
--
HTH...

Jim Thomlinson


"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

error on second pass of loop
 

You've got other replies to your other posts.

Please pick one for followups.

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

error on second pass of loop
 

Your suggestion worked. I added Set to that line and it's working great.
Thanks. Next i am trying to add the whole thing to a loop to do until it
can't find anymore errors in the column. but instead it starts looking in the
next column. any suggestions?

"Jim Thomlinson" wrote:

It is difficult to see exactly what is going on here since you have decalred
everything as variant (which also makes for very slow inneficient code). The
first thing I notice is that you need to use a set statement on this line

change from
ObjErrorCell = ObjErrorCell.Offset(1, 0)
to
Set ObjErrorCell = ObjErrorCell.Offset(1, 0)

That is the first thing I notice, but as I said it is tough to tell with
everything set as variant.
--
HTH...

Jim Thomlinson


"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

error on second pass of loop
 

I pick this one... the code works great and the help from Jim helped alot.
Now I am looking for "Next Steps". Now I need to loop the code to run till
the end of the column. I've been checking all my posts. The ones from
yesterday were outdated and the one in general wasn't being seen by
developers.

"Dave Peterson" wrote:

You've got other replies to your other posts.

Please pick one for followups.

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