End a loop if text is not found

[Ernesto]

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

[Dnereb]

depending on the kind of loop:

Please Login or Register  to view this content.

[Jim Thomlinson]

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
>
>