Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Findnext | Excel Discussion (Misc queries) | |||
Using 'Find' and 'FindNext' in vba | Excel Programming | |||
Find, Findnext VBA Loop | Excel Programming | |||
FindNext | Excel Programming | |||
FindNext | Excel Programming |