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

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

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