Jim,

I tried this and It will not go past

Set rngFound = rngSearch.FindNext(rngFound)

Thanks,



"Jim Thomlinson" wrote:

The resize was not working for some reason... Try this...

Sub DoStuff()
Dim rngSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wks As Worksheet

Set wks = ActiveSheet
Set rngSearch = wks.Columns("R")
Set rngFound = rngSearch.Find(What:="0", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Set rngFoundAll = rngFound
Do
Set rngFoundAll = Union(rngFoundAll, rngFound)
Set rngFound = rngSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
wks.Range(rngFoundAll.Offset(0, -3), rngFoundAll.offset(0,
-1)).ClearContents
End If
End Sub

--
HTH...

Jim Thomlinson


"Lost in Alabama" wrote:

Thanks for the responses. I have tried this and it does not do anything to
my spread sheet. The Do Loop I had would actuall do the task I wanted, but
it would get stuck in the loop. Alls the suggesed code you have given me do
not even complete the task once.

Thanks,

Lost


"Jim Thomlinson" wrote:

And I read the question wrong... This is just not my thread...

Sub DoStuff()
Dim rngSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wks As Worksheet

Set wks = ActiveSheet
Set rngSearch = wks.Columns("R")
Set rngFound = rngSearch.Find(What:="0", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Set rngFoundAll = rngFound
Do
Set rngFoundAll = Union(rngFoundAll, rngFound)
Set rngFound = rngSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Offset(0, -3).Resize(1, 3).ClearContents
End If
End Sub

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Sorry lost my connection and posted bad code... Give this a try...

Sub DoStuff()
Dim rngSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wks As Worksheet

Set wks = ActiveSheet
Set rngSearch = wks.Columns("R")
Set rngFound = rngSearch.Find(What:="0", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Set rngFoundAll = rngFound
Do
Set rngFoundAll = Union(rngFoundAll, rngFound)
Set rngFound = rngSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Offset(0, 3).ClearContents
End If
End Sub

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Your active cell is going to move around on you and neve find the "END" cell.
Try this...

Sub DoStuff()
Dim rngSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wks As Worksheet

Set wks = ActiveSheet
Set rngSearch = wks.Columns("R")
Set rngFound = rngSearch.Find(What:="0", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Set rngFoundAll = rngFound
Do
Set rngFoundAll = Union(rngFoundAll, rngFound)
rngSearch.FindNext (rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Offset(0, 3).ClearContents
End If
End Sub

--
HTH...

Jim Thomlinson


"Lost in Alabama" wrote:

I am using the following code to find "0"in Column R, then select three cells
to the left and clear the contents in those cells. This code performs the
operation, however, it gets stuck in the loop...Can anyone help.

Thanks

Do Until ActiveCell.Value = "END"
Columns("R:R").Select
Selection.Find(What:="0", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -3).Range("A1:D1").Select
Selection.ClearContents
Loop