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

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?

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?

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?

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?