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
|