loop and findnext
 

Can someone tell me why this won't find the next value in the loop... it
stays on the first one and crashes my excel...

heets("c").Select
Set wks = ActiveSheet
Set rngToSearch = wks.Columns(1)
Set RngFound = rngToSearch.Find(what:="new", LookIn:=xlValues,
lookat:=xlWhole)
If RngFound Is Nothing Then
MsgBox "Nothing"
Else
Do
r = RngFound.Row
Range("a" & r, "z" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("a" & r).Select
Set RngFound = rngToSearch.FindNext
Loop Until RngFound Is Nothing
End If
End Sub

loop and findnext
 

sheets("c").Select
Set wks = ActiveSheet
Set rngToSearch = wks.Columns(1)
Set RngFound = rngToSearch.Find(what:="new", LookIn:=xlValues,
lookat:=xlWhole)
If RngFound Is Nothing Then
MsgBox "Nothing"
Else
sAddr = RngFound.Address
Do
r = RngFound.Row
Range("a" & r, "z" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("a" & r).Select
Set RngFound = rngToSearch.FindNext(rngFound)
Loop Until RngFound Is Nothing or RngFound.Address = _
sAddr
End If
End Sub

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Can someone tell me why this won't find the next value in the loop... it
stays on the first one and crashes my excel...

heets("c").Select
Set wks = ActiveSheet
Set rngToSearch = wks.Columns(1)
Set RngFound = rngToSearch.Find(what:="new", LookIn:=xlValues,
lookat:=xlWhole)
If RngFound Is Nothing Then
MsgBox "Nothing"
Else
Do
r = RngFound.Row
Range("a" & r, "z" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("a" & r).Select
Set RngFound = rngToSearch.FindNext
Loop Until RngFound Is Nothing
End If
End Sub

loop and findnext
 

Thank you Tom, I appreciate the help.

"Tom Ogilvy" wrote:

sheets("c").Select
Set wks = ActiveSheet
Set rngToSearch = wks.Columns(1)
Set RngFound = rngToSearch.Find(what:="new", LookIn:=xlValues,
lookat:=xlWhole)
If RngFound Is Nothing Then
MsgBox "Nothing"
Else
sAddr = RngFound.Address
Do
r = RngFound.Row
Range("a" & r, "z" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("a" & r).Select
Set RngFound = rngToSearch.FindNext(rngFound)
Loop Until RngFound Is Nothing or RngFound.Address = _
sAddr
End If
End Sub

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Can someone tell me why this won't find the next value in the loop... it
stays on the first one and crashes my excel...

heets("c").Select
Set wks = ActiveSheet
Set rngToSearch = wks.Columns(1)
Set RngFound = rngToSearch.Find(what:="new", LookIn:=xlValues,
lookat:=xlWhole)
If RngFound Is Nothing Then
MsgBox "Nothing"
Else
Do
r = RngFound.Row
Range("a" & r, "z" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("a" & r).Select
Set RngFound = rngToSearch.FindNext
Loop Until RngFound Is Nothing
End If
End Sub

loop and findnext
 

hi,
i copied your code and tried to run it. got caught in an endless loop. so i
re-wrote i a little. your main problem was in the do loop.

Sub macfindit()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim r As Long
Dim addr As String

'Sheets("Sheet1").Select
Set wks = ActiveSheet
Set rngToSearch = wks.Columns(1)
Set rngFound = rngToSearch.Find(what:="new", _
After:=Range("A1"), _
LookIn:=xlValues, _
SearchOrder:=xlColumns, _
SearchDirection:=xlNext, _
lookat:=xlWhole)

If rngFound Is Nothing Then
MsgBox "Nothing"
Exit Sub
Else
addr = rngFound.Address
Do
r = rngFound.Row
Range("a" & r, "z" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("a" & r).Select
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = addr
End If

End Sub

regards
FSt1
"John" wrote:

Can someone tell me why this won't find the next value in the loop... it
stays on the first one and crashes my excel...

heets("c").Select
Set wks = ActiveSheet
Set rngToSearch = wks.Columns(1)
Set RngFound = rngToSearch.Find(what:="new", LookIn:=xlValues,
lookat:=xlWhole)
If RngFound Is Nothing Then
MsgBox "Nothing"
Else
Do
r = RngFound.Row
Range("a" & r, "z" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("a" & r).Select
Set RngFound = rngToSearch.FindNext
Loop Until RngFound Is Nothing
End If
End Sub

loop and findnext
 

Thanks! That works as well.

"FSt1" wrote:

hi,
i copied your code and tried to run it. got caught in an endless loop. so i
re-wrote i a little. your main problem was in the do loop.

Sub macfindit()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim r As Long
Dim addr As String

'Sheets("Sheet1").Select
Set wks = ActiveSheet
Set rngToSearch = wks.Columns(1)
Set rngFound = rngToSearch.Find(what:="new", _
After:=Range("A1"), _
LookIn:=xlValues, _
SearchOrder:=xlColumns, _
SearchDirection:=xlNext, _
lookat:=xlWhole)

If rngFound Is Nothing Then
MsgBox "Nothing"
Exit Sub
Else
addr = rngFound.Address
Do
r = rngFound.Row
Range("a" & r, "z" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("a" & r).Select
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = addr
End If

End Sub

regards
FSt1
"John" wrote:

Can someone tell me why this won't find the next value in the loop... it
stays on the first one and crashes my excel...

heets("c").Select
Set wks = ActiveSheet
Set rngToSearch = wks.Columns(1)
Set RngFound = rngToSearch.Find(what:="new", LookIn:=xlValues,
lookat:=xlWhole)
If RngFound Is Nothing Then
MsgBox "Nothing"
Else
Do
r = RngFound.Row
Range("a" & r, "z" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("a" & r).Select
Set RngFound = rngToSearch.FindNext
Loop Until RngFound Is Nothing
End If
End Sub