I am trying to find any cell with the word stop in it and turn that
cell red. Later in the code I have all red cells delete. However, it
doesn't always work. Sometimes it does other times it doesn't.
Typically after I try running the program more than twice it stops
working. I've tried doing a step by step debug and it just skips over
the code as if the word stop was not in the sheet. My code is as
follows:

With Worksheets(1).Range("a1:a500")
Set c = .Find("Stop", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 3
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

I tried just deleting the cells within the find but it would do the
same thing so I tried it this way hoping because it's a simplier code
it would work better but that is not working out. Please help. If
there is a way to use a if statement or any other way please let me
know. THANKS TO ALL

I added some test code into your program to make it easier to find where the
problem is. This way you don't have to step through the loop 500 times.
You can change the $C$5 to any cell in the range to help find out where the
code is stopping.


With Worksheets(1).Range("a1:a500")
Set c = .Find("Stop", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'add test code to determine where the problem is
if c.address = "$C$5" then
a = 1 'add break point here
end if

c.Interior.ColorIndex = 3
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With


" wrote:

I am trying to find any cell with the word stop in it and turn that
cell red. Later in the code I have all red cells delete. However, it
doesn't always work. Sometimes it does other times it doesn't.
Typically after I try running the program more than twice it stops
working. I've tried doing a step by step debug and it just skips over
the code as if the word stop was not in the sheet. My code is as
follows:

With Worksheets(1).Range("a1:a500")
Set c = .Find("Stop", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 3
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

I tried just deleting the cells within the find but it would do the
same thing so I tried it this way hoping because it's a simplier code
it would work better but that is not working out. Please help. If
there is a way to use a if statement or any other way please let me
know. THANKS TO ALL

I just ran it like four times and it worked flawlessly. This happens
usually. Then I tried it again and it messed up. I tried adding your
test code and it never even went to that line. Once the code reads
If Not c Is Nothing Then it bumps it to the end if. For some reason
it searches the file and doesn't find the word "stop". But it's the
first word in the excel file.

Do you have any On Error staements in your code. Try commenting these out.
the error path may be getting you confused. Instead you can add a break
point in the error code.

" wrote:

I just ran it like four times and it worked flawlessly. This happens
usually. Then I tried it again and it messed up. I tried adding your
test code and it never even went to that line. Once the code reads
If Not c Is Nothing Then it bumps it to the end if. For some reason
it searches the file and doesn't find the word "stop". But it's the
first word in the excel file.

Yes, I did have on error throughout my code, they were down toward the
end of the code. This part is like I open a text file and then I have
this code. I tried to comment the on errors and still no change. I
closed down my file and re-opened it and it worked fine again for like
3 runs then it messed up.

Two possible problems
1) If you are running with Excel 2007 they are still bugs that haven't been
fixed
2) did you run three times with the On Error commented out. You said you
shutdown then ran 3 times. Were the comments to the On error still In or Out?

" wrote:

Yes, I did have on error throughout my code, they were down toward the
end of the code. This part is like I open a text file and then I have
this code. I tried to comment the on errors and still no change. I
closed down my file and re-opened it and it worked fine again for like
3 runs then it messed up.

I'm running 2000 and yes I tried running it a couple of times and
still nothing. Do you know if there is any other way to do this
without using the Find command?

When you use find you need to define most of the parameters of the find
operation as the end user has the ability to change these parameters which
persist. Generally speaking your code is fine otherwise. Were you intending
to do some deleting. If so then here is some code for you to try...

Sub FindStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set rngToSearch = Worksheets(1).Range("A1:A500")
Set rngFound = rngToSearch.Find(What:="stop", _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
MatchCase:=False)
If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
With rngFoundAll
.Select 'You will probably want to comment this line out
.Interior.ColorIndex = 3
'.EntireRow.Delete
End With
End If

End Sub
--
HTH...

Jim Thomlinson


" wrote:

I am trying to find any cell with the word stop in it and turn that
cell red. Later in the code I have all red cells delete. However, it
doesn't always work. Sometimes it does other times it doesn't.
Typically after I try running the program more than twice it stops
working. I've tried doing a step by step debug and it just skips over
the code as if the word stop was not in the sheet. My code is as
follows:

With Worksheets(1).Range("a1:a500")
Set c = .Find("Stop", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 3
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

I tried just deleting the cells within the find but it would do the
same thing so I tried it this way hoping because it's a simplier code
it would work better but that is not working out. Please help. If
there is a way to use a if statement or any other way please let me
know. THANKS TO ALL

his method is much simplier than find

Set SearchRange = Worksheets(1).Range("a1:a500")
For Each cell In SearchRange
If UCase(cell.Value) = "STOP" Then
cell.Interior.ColorIndex = 3
End If
Next cell


" wrote:

I'm running 2000 and yes I tried running it a couple of times and
still nothing. Do you know if there is any other way to do this
without using the Find command?

That appears to be helping it. I've ran it about 10 times and it fixed
it. I actually just added the parameter not the whole code. So it
appears to be working. Hopefully it stays. Thanks for all the help.

Note to Carlos. If you intend to do deleting don't use this code as it will
not work properly. If all you intend to do is to colour cells it will be
fine. It will be slower than find (not a big deal with only 500 cells) and it
will not find partial matches.
--
HTH...

Jim Thomlinson


"Joel" wrote:

his method is much simplier than find

Set SearchRange = Worksheets(1).Range("a1:a500")
For Each cell In SearchRange
If UCase(cell.Value) = "STOP" Then
cell.Interior.ColorIndex = 3
End If
Next cell


" wrote:

I'm running 2000 and yes I tried running it a couple of times and
still nothing. Do you know if there is any other way to do this
without using the Find command?

The code I posted is overkill if all you want to do is colour the cells. If
you want to delete the cells however it is more efficient as it only does 1
delete of a large range as opposed to many small deletes...
--
HTH...

Jim Thomlinson


" wrote:

That appears to be helping it. I've ran it about 10 times and it fixed
it. I actually just added the parameter not the whole code. So it
appears to be working. Hopefully it stays. Thanks for all the help.