Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
End a loop if text is not found
hello all,
question...i have a loop going in VB. it basically looks for #N/A and inserts a row above that. But i cant seem to make it stop. What code should i use if i want to tell the loop to stop when it has run out of #N/As in the column? Thanks...appreciate it! ernesto |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
End a loop if text is not found
depending on the kind of loop: Code: -------------------- exit for exit do exit while -------------------- -- Dnereb ------------------------------------------------------------------------ Dnereb's Profile: http://www.excelforum.com/member.php...o&userid=26182 View this thread: http://www.excelforum.com/showthread...hreadid=397384 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
End a loop if text is not found
Find is a circular loop (as you now know). You need to store the first
instance of having found #N/A (I do this with rngfirst) and then check to see when you get back to there (my loop until condition). The code will run faster if you create a range object of all of the found items and then just insert rows once at the very end. Here is my code (it check Column A of Sheet1). Sub FindNA() Dim wks As Worksheet Dim rngFirst As Range Dim rngCurrent As Range Dim rngToSearch As Range Dim rngFound As Range Set wks = Sheets("Sheet1") 'Change the sheet Set rngToSearch = wks.Columns(1) 'Change the column Set rngCurrent = rngToSearch.Find("#N/A") If rngCurrent Is Nothing Then MsgBox "#N/A Not Found" Else Set rngFirst = rngCurrent Set rngFound = rngCurrent Do Set rngFound = Union(rngCurrent, rngFound) Set rngCurrent = rngToSearch.FindNext(rngCurrent) Loop Until rngFirst.Address = rngCurrent.Address rngFound.EntireRow.Insert xlShiftDown End If End Sub -- HTH... Jim Thomlinson "Ernesto" wrote: hello all, question...i have a loop going in VB. it basically looks for #N/A and inserts a row above that. But i cant seem to make it stop. What code should i use if i want to tell the loop to stop when it has run out of #N/As in the column? Thanks...appreciate it! ernesto |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND function does not return ZERO when text is not found | Excel Worksheet Functions | |||
Stop a loop when certain data is found | Excel Discussion (Misc queries) | |||
Highlighting Found Text in Excel | Excel Discussion (Misc queries) | |||
Return text found in a search | Excel Worksheet Functions | |||
Loop to change cell color based on found value? | Excel Programming |