Loop question
 

I am trying to loop through my code WHILE in a certain range, NOT FOR each
cell in the range. Can anyone help?

Loop question
 

Rob,

While *what* is in a certain range? Something like the
following:

Dim Rng As Range
Set Rng = Range("A1")
Do While Not Application.Intersect(Rng,Range("A1:A10")) Is
Nothing
' do something with Rng
Set Rng = Rng(2,1)
Loop

But rather than this, why not just use For Each?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Rob" wrote in message
...
I am trying to loop through my code WHILE in a certain range,
NOT FOR each
cell in the range. Can anyone help?

Loop question
 

The reason I can't use For Each is because:
the code within the loop is searching for the word "ERROR" within the cells
(along with doing a few other things). I do not need to run the code for each
cell. I just need it to stop running when it has finished searching within my
range. My range currently goes from E1 to E333, so once the
Cells.Find(what:="ERROR" portion of my code passes cell E333, I would like it
to stop running.

"Chip Pearson" wrote:

Rob,

While *what* is in a certain range? Something like the
following:

Dim Rng As Range
Set Rng = Range("A1")
Do While Not Application.Intersect(Rng,Range("A1:A10")) Is
Nothing
' do something with Rng
Set Rng = Rng(2,1)
Loop

But rather than this, why not just use For Each?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Rob" wrote in message
...
I am trying to loop through my code WHILE in a certain range,
NOT FOR each
cell in the range. Can anyone help?

Loop question
 

Public Function FindStuff(ByVal LookFor As String) As Variant
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Range("E1", "E333") '***The only change
Set rngCurrent = rngToSearch.Find(LookFor)

If rngCurrent Is Nothing Then
MsgBox LookFor & " was not found."
Else
Set rngFirst = rngCurrent
Set rngFound = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set FindStuff = rngFound
End If
End Function


--
HTH...

Jim Thomlinson


"Rob" wrote:

I have already found ways to search for what i want to find and do all of
what i am trying to do. All I am trying to do now is CONTAIN MY SEARCH
BETWEEN CELL E1 AND E333. My code along with all the code others have posted
to help works just fine(and thank you because it has helped in making my code
more efficient), but they all search the entire worksheet. I ONLY WANT TO
SEARCH BETWEEN CELL E1 AND E333.

"Jim Thomlinson" wrote:

This code finds all of the errors. I was unsure if you wanted all or just the
last error.

Sub Test()
Dim rngFound As Range

Set rngFound = FindStuff("ERROR")
If Not rngFound Is Nothing Then rngFound.Select
End Sub

Public Function FindStuff(ByVal LookFor As String) As Variant
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Cells
Set rngCurrent = rngToSearch.Find(LookFor)

If rngCurrent Is Nothing Then
MsgBox LookFor & " was not found."
Else
Set rngFirst = rngCurrent
Set rngFound = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set FindStuff = rngFound
End If
End Function

--
HTH...

Jim Thomlinson


"Rob" wrote:

The reason I can't use For Each is because:
the code within the loop is searching for the word "ERROR" within the cells
(along with doing a few other things). I do not need to run the code for each
cell. I just need it to stop running when it has finished searching within my
range. My range currently goes from E1 to E333, so once the
Cells.Find(what:="ERROR" portion of my code passes cell E333, I would like it
to stop running.

"Chip Pearson" wrote:

Rob,

While *what* is in a certain range? Something like the
following:

Dim Rng As Range
Set Rng = Range("A1")
Do While Not Application.Intersect(Rng,Range("A1:A10")) Is
Nothing
' do something with Rng
Set Rng = Rng(2,1)
Loop

But rather than this, why not just use For Each?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Rob" wrote in message
...
I am trying to loop through my code WHILE in a certain range,
NOT FOR each
cell in the range. Can anyone help?

Loop question
 

Before I try to figure out what exactly this is doing your variables are
closer to correct, but not quite there yet. Take a look at this site...

http://www.cpearson.com/excel/variables.htm
--
HTH...

Jim Thomlinson


"Rob" wrote:

THANK YOU THANK YOU THANK YOU JIM!!!
You have taken me one step closer to my goal, as you did with my last
post the other day. But now I have to fit all the rest of my logic in. My
goal is not to just select all of the cells. Here is the logic I had already
that does exactly what I am trying to do perfectly, but only one instance at
a time instead of all instances between E1 and E333.

Sub Total()
Dim ObjErrorString As String
Dim ObjErrorCell, ObjOkCell As Range
Dim ObjErrorTime, ObjOkTime As Range
Dim ObjSingleTotal, OjbCompleteTotal
'loop should start here
Set ObjErrorCell = Cells.Find(what:="ERROR", after:=ActiveCell,
searchorder:=xlByColumns, searchdirection:=xlNext) finds next cell containing
ERROR
ObjErrorString = "ERROR"
Do Until InStr(1, ObjErrorCell.Offset(1, 0).Value, ObjErrorString,
vbTextCompare) < 1 'finds last consecutive cell containing ERROR
Set ObjErrorCell = ObjErrorCell.Offset(1, 0)
Loop
ObjErrorCell.Select
Set ObjErrorTime = ActiveCell.Offset(0, -ActiveCell.Column + 1) 'records
the time of last consecutive ERROR
Set ObjOkCell = Cells.Find(what:="OK", after:=ActiveCell,
searchorder:=xlByColumns, searchdirection:=xlPrevious) 'finds previous cell
containing OK
ObjOkCell.Select
Set ObjOkTime = ActiveCell.Offset(0, -ActiveCell.Column + 1) 'records the
time of previous OK
ObjSingleTotal = ObjOkTime - ObjErrorTime 'calculates the difference
ObjCompleteTotal = ObjCompleteTotal + ObjSingleTotal 'adds that to total
time down
ObjErrorCell.Select
'loop should end here
Range("E388").Value = ObjCompleteTotal 'puts the total in dedicated cell
End Sub

"Jim Thomlinson" wrote:

Public Function FindStuff(ByVal LookFor As String) As Variant
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Range("E1", "E333") '***The only change
Set rngCurrent = rngToSearch.Find(LookFor)

If rngCurrent Is Nothing Then
MsgBox LookFor & " was not found."
Else
Set rngFirst = rngCurrent
Set rngFound = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set FindStuff = rngFound
End If
End Function


--
HTH...

Jim Thomlinson


"Rob" wrote:

I have already found ways to search for what i want to find and do all of
what i am trying to do. All I am trying to do now is CONTAIN MY SEARCH
BETWEEN CELL E1 AND E333. My code along with all the code others have posted
to help works just fine(and thank you because it has helped in making my code
more efficient), but they all search the entire worksheet. I ONLY WANT TO
SEARCH BETWEEN CELL E1 AND E333.

"Jim Thomlinson" wrote:

This code finds all of the errors. I was unsure if you wanted all or just the
last error.

Sub Test()
Dim rngFound As Range

Set rngFound = FindStuff("ERROR")
If Not rngFound Is Nothing Then rngFound.Select
End Sub

Public Function FindStuff(ByVal LookFor As String) As Variant
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Cells
Set rngCurrent = rngToSearch.Find(LookFor)

If rngCurrent Is Nothing Then
MsgBox LookFor & " was not found."
Else
Set rngFirst = rngCurrent
Set rngFound = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set FindStuff = rngFound
End If
End Function

--
HTH...

Jim Thomlinson


"Rob" wrote:

The reason I can't use For Each is because:
the code within the loop is searching for the word "ERROR" within the cells
(along with doing a few other things). I do not need to run the code for each
cell. I just need it to stop running when it has finished searching within my
range. My range currently goes from E1 to E333, so once the
Cells.Find(what:="ERROR" portion of my code passes cell E333, I would like it
to stop running.

"Chip Pearson" wrote:

Rob,

While *what* is in a certain range? Something like the
following:

Dim Rng As Range
Set Rng = Range("A1")
Do While Not Application.Intersect(Rng,Range("A1:A10")) Is
Nothing
' do something with Rng
Set Rng = Rng(2,1)
Loop

But rather than this, why not just use For Each?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Rob" wrote in message
...
I am trying to loop through my code WHILE in a certain range,
NOT FOR each
cell in the range. Can anyone help?

Loop question
 

THANK YOU THANK YOU THANK YOU JIM!!!
You have taken me one step closer to my goal, as you did with my last
post the other day. But now I have to fit all the rest of my logic in. My
goal is not to just select all of the cells. Here is the logic I had already
that does exactly what I am trying to do perfectly, but only one instance at
a time instead of all instances between E1 and E333.

Sub Total()
Dim ObjErrorString As String
Dim ObjErrorCell, ObjOkCell As Range
Dim ObjErrorTime, ObjOkTime As Range
Dim ObjSingleTotal, OjbCompleteTotal
'loop should start here
Set ObjErrorCell = Cells.Find(what:="ERROR", after:=ActiveCell,
searchorder:=xlByColumns, searchdirection:=xlNext) finds next cell containing
ERROR
ObjErrorString = "ERROR"
Do Until InStr(1, ObjErrorCell.Offset(1, 0).Value, ObjErrorString,
vbTextCompare) < 1 'finds last consecutive cell containing ERROR
Set ObjErrorCell = ObjErrorCell.Offset(1, 0)
Loop
ObjErrorCell.Select
Set ObjErrorTime = ActiveCell.Offset(0, -ActiveCell.Column + 1) 'records
the time of last consecutive ERROR
Set ObjOkCell = Cells.Find(what:="OK", after:=ActiveCell,
searchorder:=xlByColumns, searchdirection:=xlPrevious) 'finds previous cell
containing OK
ObjOkCell.Select
Set ObjOkTime = ActiveCell.Offset(0, -ActiveCell.Column + 1) 'records the
time of previous OK
ObjSingleTotal = ObjOkTime - ObjErrorTime 'calculates the difference
ObjCompleteTotal = ObjCompleteTotal + ObjSingleTotal 'adds that to total
time down
ObjErrorCell.Select
'loop should end here
Range("E388").Value = ObjCompleteTotal 'puts the total in dedicated cell
End Sub

"Jim Thomlinson" wrote:

Public Function FindStuff(ByVal LookFor As String) As Variant
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Range("E1", "E333") '***The only change
Set rngCurrent = rngToSearch.Find(LookFor)

If rngCurrent Is Nothing Then
MsgBox LookFor & " was not found."
Else
Set rngFirst = rngCurrent
Set rngFound = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set FindStuff = rngFound
End If
End Function


--
HTH...

Jim Thomlinson


"Rob" wrote:

I have already found ways to search for what i want to find and do all of
what i am trying to do. All I am trying to do now is CONTAIN MY SEARCH
BETWEEN CELL E1 AND E333. My code along with all the code others have posted
to help works just fine(and thank you because it has helped in making my code
more efficient), but they all search the entire worksheet. I ONLY WANT TO
SEARCH BETWEEN CELL E1 AND E333.

"Jim Thomlinson" wrote:

This code finds all of the errors. I was unsure if you wanted all or just the
last error.

Sub Test()
Dim rngFound As Range

Set rngFound = FindStuff("ERROR")
If Not rngFound Is Nothing Then rngFound.Select
End Sub

Public Function FindStuff(ByVal LookFor As String) As Variant
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Cells
Set rngCurrent = rngToSearch.Find(LookFor)

If rngCurrent Is Nothing Then
MsgBox LookFor & " was not found."
Else
Set rngFirst = rngCurrent
Set rngFound = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set FindStuff = rngFound
End If
End Function

--
HTH...

Jim Thomlinson


"Rob" wrote:

The reason I can't use For Each is because:
the code within the loop is searching for the word "ERROR" within the cells
(along with doing a few other things). I do not need to run the code for each
cell. I just need it to stop running when it has finished searching within my
range. My range currently goes from E1 to E333, so once the
Cells.Find(what:="ERROR" portion of my code passes cell E333, I would like it
to stop running.

"Chip Pearson" wrote:

Rob,

While *what* is in a certain range? Something like the
following:

Dim Rng As Range
Set Rng = Range("A1")
Do While Not Application.Intersect(Rng,Range("A1:A10")) Is
Nothing
' do something with Rng
Set Rng = Rng(2,1)
Loop

But rather than this, why not just use For Each?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Rob" wrote in message
...
I am trying to loop through my code WHILE in a certain range,
NOT FOR each
cell in the range. Can anyone help?

Loop question
 

Actually... I have flipped my spreadsheet and streamlined my code:

Sub Total()
Dim ObjErrorCell, ObjOkCell As Range
Dim ObjErrorTime, ObjOkTime As Range
Dim ObjSingleTotal, OjbCompleteTotal As Long
Set ObjErrorCell = Cells.Find(what:="ERROR", after:=ActiveCell,
searchorder:=xlByColumns, searchdirection:=xlNext)
Set ObjErrorTime = ObjErrorCell.Offset(0, -ObjErrorCell.Column + 1)
ObjErrorCell.Select
Set ObjOkCell = Cells.Find(what:="OK", after:=ActiveCell,
searchorder:=xlByColumns, searchdirection:=xlNext)
Set ObjOkTime = ObjOkCell.Offset(0, -ObjOkCell.Column + 1)
ObjOkCell.Select
ObjSingleTotal = ObjOkTime - ObjErrorTime
ObjCompleteTotal = ObjCompleteTotal + ObjSingleTotal
Range("E388").Value = ObjCompleteTotal
End Sub

"Jim Thomlinson" wrote:

Public Function FindStuff(ByVal LookFor As String) As Variant
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Range("E1", "E333") '***The only change
Set rngCurrent = rngToSearch.Find(LookFor)

If rngCurrent Is Nothing Then
MsgBox LookFor & " was not found."
Else
Set rngFirst = rngCurrent
Set rngFound = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set FindStuff = rngFound
End If
End Function


--
HTH...

Jim Thomlinson


"Rob" wrote:

I have already found ways to search for what i want to find and do all of
what i am trying to do. All I am trying to do now is CONTAIN MY SEARCH
BETWEEN CELL E1 AND E333. My code along with all the code others have posted
to help works just fine(and thank you because it has helped in making my code
more efficient), but they all search the entire worksheet. I ONLY WANT TO
SEARCH BETWEEN CELL E1 AND E333.

"Jim Thomlinson" wrote:

This code finds all of the errors. I was unsure if you wanted all or just the
last error.

Sub Test()
Dim rngFound As Range

Set rngFound = FindStuff("ERROR")
If Not rngFound Is Nothing Then rngFound.Select
End Sub

Public Function FindStuff(ByVal LookFor As String) As Variant
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Cells
Set rngCurrent = rngToSearch.Find(LookFor)

If rngCurrent Is Nothing Then
MsgBox LookFor & " was not found."
Else
Set rngFirst = rngCurrent
Set rngFound = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set FindStuff = rngFound
End If
End Function

--
HTH...

Jim Thomlinson


"Rob" wrote:

The reason I can't use For Each is because:
the code within the loop is searching for the word "ERROR" within the cells
(along with doing a few other things). I do not need to run the code for each
cell. I just need it to stop running when it has finished searching within my
range. My range currently goes from E1 to E333, so once the
Cells.Find(what:="ERROR" portion of my code passes cell E333, I would like it
to stop running.

"Chip Pearson" wrote:

Rob,

While *what* is in a certain range? Something like the
following:

Dim Rng As Range
Set Rng = Range("A1")
Do While Not Application.Intersect(Rng,Range("A1:A10")) Is
Nothing
' do something with Rng
Set Rng = Rng(2,1)
Loop

But rather than this, why not just use For Each?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Rob" wrote in message
...
I am trying to loop through my code WHILE in a certain range,
NOT FOR each
cell in the range. Can anyone help?

Loop question
 

This code finds all of the errors. I was unsure if you wanted all or just the
last error.

Sub Test()
Dim rngFound As Range

Set rngFound = FindStuff("ERROR")
If Not rngFound Is Nothing Then rngFound.Select
End Sub

Public Function FindStuff(ByVal LookFor As String) As Variant
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Cells
Set rngCurrent = rngToSearch.Find(LookFor)

If rngCurrent Is Nothing Then
MsgBox LookFor & " was not found."
Else
Set rngFirst = rngCurrent
Set rngFound = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set FindStuff = rngFound
End If
End Function

--
HTH...

Jim Thomlinson


"Rob" wrote:

The reason I can't use For Each is because:
the code within the loop is searching for the word "ERROR" within the cells
(along with doing a few other things). I do not need to run the code for each
cell. I just need it to stop running when it has finished searching within my
range. My range currently goes from E1 to E333, so once the
Cells.Find(what:="ERROR" portion of my code passes cell E333, I would like it
to stop running.

"Chip Pearson" wrote:

Rob,

While *what* is in a certain range? Something like the
following:

Dim Rng As Range
Set Rng = Range("A1")
Do While Not Application.Intersect(Rng,Range("A1:A10")) Is
Nothing
' do something with Rng
Set Rng = Rng(2,1)
Loop

But rather than this, why not just use For Each?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Rob" wrote in message
...
I am trying to loop through my code WHILE in a certain range,
NOT FOR each
cell in the range. Can anyone help?

Loop question
 

Thank you both Jim and Chip. It's funny that Chip was the first to respond to
my question and now you recommend reading his literature. I have read the
link you sent me and now have a btter understanding of how Excel handles
variables. I am now going back to read through the rest of his site. It is
very informative. Thank you.

"Jim Thomlinson" wrote:

Before I try to figure out what exactly this is doing your variables are
closer to correct, but not quite there yet. Take a look at this site...

http://www.cpearson.com/excel/variables.htm
--
HTH...

Jim Thomlinson


"Rob" wrote:

THANK YOU THANK YOU THANK YOU JIM!!!
You have taken me one step closer to my goal, as you did with my last
post the other day. But now I have to fit all the rest of my logic in. My
goal is not to just select all of the cells. Here is the logic I had already
that does exactly what I am trying to do perfectly, but only one instance at
a time instead of all instances between E1 and E333.

Sub Total()
Dim ObjErrorString As String
Dim ObjErrorCell, ObjOkCell As Range
Dim ObjErrorTime, ObjOkTime As Range
Dim ObjSingleTotal, OjbCompleteTotal
'loop should start here
Set ObjErrorCell = Cells.Find(what:="ERROR", after:=ActiveCell,
searchorder:=xlByColumns, searchdirection:=xlNext) finds next cell containing
ERROR
ObjErrorString = "ERROR"
Do Until InStr(1, ObjErrorCell.Offset(1, 0).Value, ObjErrorString,
vbTextCompare) < 1 'finds last consecutive cell containing ERROR
Set ObjErrorCell = ObjErrorCell.Offset(1, 0)
Loop
ObjErrorCell.Select
Set ObjErrorTime = ActiveCell.Offset(0, -ActiveCell.Column + 1) 'records
the time of last consecutive ERROR
Set ObjOkCell = Cells.Find(what:="OK", after:=ActiveCell,
searchorder:=xlByColumns, searchdirection:=xlPrevious) 'finds previous cell
containing OK
ObjOkCell.Select
Set ObjOkTime = ActiveCell.Offset(0, -ActiveCell.Column + 1) 'records the
time of previous OK
ObjSingleTotal = ObjOkTime - ObjErrorTime 'calculates the difference
ObjCompleteTotal = ObjCompleteTotal + ObjSingleTotal 'adds that to total
time down
ObjErrorCell.Select
'loop should end here
Range("E388").Value = ObjCompleteTotal 'puts the total in dedicated cell
End Sub

"Jim Thomlinson" wrote:

Public Function FindStuff(ByVal LookFor As String) As Variant
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Range("E1", "E333") '***The only change
Set rngCurrent = rngToSearch.Find(LookFor)

If rngCurrent Is Nothing Then
MsgBox LookFor & " was not found."
Else
Set rngFirst = rngCurrent
Set rngFound = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set FindStuff = rngFound
End If
End Function


--
HTH...

Jim Thomlinson


"Rob" wrote:

I have already found ways to search for what i want to find and do all of
what i am trying to do. All I am trying to do now is CONTAIN MY SEARCH
BETWEEN CELL E1 AND E333. My code along with all the code others have posted
to help works just fine(and thank you because it has helped in making my code
more efficient), but they all search the entire worksheet. I ONLY WANT TO
SEARCH BETWEEN CELL E1 AND E333.

"Jim Thomlinson" wrote:

This code finds all of the errors. I was unsure if you wanted all or just the
last error.

Sub Test()
Dim rngFound As Range

Set rngFound = FindStuff("ERROR")
If Not rngFound Is Nothing Then rngFound.Select
End Sub

Public Function FindStuff(ByVal LookFor As String) As Variant
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Cells
Set rngCurrent = rngToSearch.Find(LookFor)

If rngCurrent Is Nothing Then
MsgBox LookFor & " was not found."
Else
Set rngFirst = rngCurrent
Set rngFound = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set FindStuff = rngFound
End If
End Function

--
HTH...

Jim Thomlinson


"Rob" wrote:

The reason I can't use For Each is because:
the code within the loop is searching for the word "ERROR" within the cells
(along with doing a few other things). I do not need to run the code for each
cell. I just need it to stop running when it has finished searching within my
range. My range currently goes from E1 to E333, so once the
Cells.Find(what:="ERROR" portion of my code passes cell E333, I would like it
to stop running.

"Chip Pearson" wrote:

Rob,

While *what* is in a certain range? Something like the
following:

Dim Rng As Range
Set Rng = Range("A1")
Do While Not Application.Intersect(Rng,Range("A1:A10")) Is
Nothing
' do something with Rng
Set Rng = Rng(2,1)
Loop

But rather than this, why not just use For Each?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Rob" wrote in message
...
I am trying to loop through my code WHILE in a certain range,
NOT FOR each
cell in the range. Can anyone help?

Loop question
 

Something like this should be close...

Sub Total()
Dim rngErrorCell As Range
Dim rngFirstError As Range
Dim rngToSearch As Range
Dim rngOkCell As Range
Dim rngErrorTime As Range
Dim rngOkTime As Range
Dim wks As Worksheet
Dim lngSingleTotal As Long
Dim lngCompleteTotal As Long

Set wks = ActiveSheet
Set rngToSearch = wks.Range("E1", "E333")
Set rngErrorCell = rngToSearch.Find("ERROR", ActiveCell)

If Not rngErrorCell Is Nothing Then
Set rngFirstError = rngErrorCell
Do
Set rngErrorTime = rngErrorCell.Offset(0, -rngErrorCell.Column +
1)
Set rngOkCell = rngToSearch.Find(what:="OK", after:=rngErrorCell)
Set rngOkTime = rngOkCell.Offset(0, -rngOkCell.Column + 1)
lngSingleTotal = rngOkTime - rngErrorTime
lngCompleteTotal = lngCompleteTotal + lngSingleTotal
Set rngErrorCell = rngToSearch.FindNext(rngErrorCell)
Loop Until rngErrorCell.Address = rngFirstError.Address
End If
Range("E388").Value = lngCompleteTotal
End Sub
--
HTH...

Jim Thomlinson


"Rob" wrote:

Actually... I have flipped my spreadsheet and streamlined my code:

Sub Total()
Dim ObjErrorCell, ObjOkCell As Range
Dim ObjErrorTime, ObjOkTime As Range
Dim ObjSingleTotal, OjbCompleteTotal As Long
Set ObjErrorCell = Cells.Find(what:="ERROR", after:=ActiveCell,
searchorder:=xlByColumns, searchdirection:=xlNext)
Set ObjErrorTime = ObjErrorCell.Offset(0, -ObjErrorCell.Column + 1)
ObjErrorCell.Select
Set ObjOkCell = Cells.Find(what:="OK", after:=ActiveCell,
searchorder:=xlByColumns, searchdirection:=xlNext)
Set ObjOkTime = ObjOkCell.Offset(0, -ObjOkCell.Column + 1)
ObjOkCell.Select
ObjSingleTotal = ObjOkTime - ObjErrorTime
ObjCompleteTotal = ObjCompleteTotal + ObjSingleTotal
Range("E388").Value = ObjCompleteTotal
End Sub

"Jim Thomlinson" wrote:

Public Function FindStuff(ByVal LookFor As String) As Variant
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Range("E1", "E333") '***The only change
Set rngCurrent = rngToSearch.Find(LookFor)

If rngCurrent Is Nothing Then
MsgBox LookFor & " was not found."
Else
Set rngFirst = rngCurrent
Set rngFound = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set FindStuff = rngFound
End If
End Function


--
HTH...

Jim Thomlinson


"Rob" wrote:

I have already found ways to search for what i want to find and do all of
what i am trying to do. All I am trying to do now is CONTAIN MY SEARCH
BETWEEN CELL E1 AND E333. My code along with all the code others have posted
to help works just fine(and thank you because it has helped in making my code
more efficient), but they all search the entire worksheet. I ONLY WANT TO
SEARCH BETWEEN CELL E1 AND E333.

"Jim Thomlinson" wrote:

This code finds all of the errors. I was unsure if you wanted all or just the
last error.

Sub Test()
Dim rngFound As Range

Set rngFound = FindStuff("ERROR")
If Not rngFound Is Nothing Then rngFound.Select
End Sub

Public Function FindStuff(ByVal LookFor As String) As Variant
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Cells
Set rngCurrent = rngToSearch.Find(LookFor)

If rngCurrent Is Nothing Then
MsgBox LookFor & " was not found."
Else
Set rngFirst = rngCurrent
Set rngFound = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set FindStuff = rngFound
End If
End Function

--
HTH...

Jim Thomlinson


"Rob" wrote:

The reason I can't use For Each is because:
the code within the loop is searching for the word "ERROR" within the cells
(along with doing a few other things). I do not need to run the code for each
cell. I just need it to stop running when it has finished searching within my
range. My range currently goes from E1 to E333, so once the
Cells.Find(what:="ERROR" portion of my code passes cell E333, I would like it
to stop running.

"Chip Pearson" wrote:

Rob,

While *what* is in a certain range? Something like the
following:

Dim Rng As Range
Set Rng = Range("A1")
Do While Not Application.Intersect(Rng,Range("A1:A10")) Is
Nothing
' do something with Rng
Set Rng = Rng(2,1)
Loop

But rather than this, why not just use For Each?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Rob" wrote in message
...
I am trying to loop through my code WHILE in a certain range,
NOT FOR each
cell in the range. Can anyone help?

Loop question
 

I have already found ways to search for what i want to find and do all of
what i am trying to do. All I am trying to do now is CONTAIN MY SEARCH
BETWEEN CELL E1 AND E333. My code along with all the code others have posted
to help works just fine(and thank you because it has helped in making my code
more efficient), but they all search the entire worksheet. I ONLY WANT TO
SEARCH BETWEEN CELL E1 AND E333.

"Jim Thomlinson" wrote:

This code finds all of the errors. I was unsure if you wanted all or just the
last error.

Sub Test()
Dim rngFound As Range

Set rngFound = FindStuff("ERROR")
If Not rngFound Is Nothing Then rngFound.Select
End Sub

Public Function FindStuff(ByVal LookFor As String) As Variant
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Cells
Set rngCurrent = rngToSearch.Find(LookFor)

If rngCurrent Is Nothing Then
MsgBox LookFor & " was not found."
Else
Set rngFirst = rngCurrent
Set rngFound = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set FindStuff = rngFound
End If
End Function

--
HTH...

Jim Thomlinson


"Rob" wrote:

The reason I can't use For Each is because:
the code within the loop is searching for the word "ERROR" within the cells
(along with doing a few other things). I do not need to run the code for each
cell. I just need it to stop running when it has finished searching within my
range. My range currently goes from E1 to E333, so once the
Cells.Find(what:="ERROR" portion of my code passes cell E333, I would like it
to stop running.

"Chip Pearson" wrote:

Rob,

While *what* is in a certain range? Something like the
following:

Dim Rng As Range
Set Rng = Range("A1")
Do While Not Application.Intersect(Rng,Range("A1:A10")) Is
Nothing
' do something with Rng
Set Rng = Rng(2,1)
Loop

But rather than this, why not just use For Each?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Rob" wrote in message
...
I am trying to loop through my code WHILE in a certain range,
NOT FOR each
cell in the range. Can anyone help?