Exiting a Do Loop on error

[Cloudfall]

How do I exit a Do Loop when I get an error? I have a vba macro with a
do loop which searches a spreadsheet for the string "liquidat", selects
the row, cuts and pastes the row to a different worksheet, then goes
back to the original worksheet and searches for the string again.
Naturally, when it no longer finds the string, it generates the error
"Run-time error '91': Object variable or With block variable not set".
I tried "On Error Exit Do" but the compiler didn't like this. I tried
an ErrorHandler: Exit Do, but the compiler told me the Exit Do was
outside the loop. I tried "If Err.Number <> 0 Then Exit Do" before and
after the "Cells.Find(What:="liquidat"..." but of course this didn't
work because before the Find, Err.Number = 0, and once the error is
generated it never gets to the "If Err.Number <> 0 Then Exit Do" that
follows the error. (Incidently, how can "If Err.Number <> 0 Then Exit
Do" ever work?). So, I have been very busy but to no avail. Thanking
you now for any help.

 

[Bob Phillips]

Here is an example

Sub findit()
Dim cell As Range
Dim sFirst As String

Set cell = Cells.Find("A")
If Not cell Is Nothing Then
sFirst = cell.Address
Do
Set cell = Cells.FindNext(cell)
If Not cell Is Nothing And cell.Address <> sFirst Then
MsgBox cell.Address
End If
Loop Until cell Is Nothing Or sFirst = cell.Address
End If
End Sub

 

[Cloudfall]

Bob, thank you, your program works. I have modified it very slightly
(adding just one line "MsgBox cell.Address") for my purposes and I will
attach this code at the end of this posting. However, I do not
understand why you have "Set cell = Cells.FindNext(cell)" instead of
"Set cell = Cells.FindNext("A")". My knowledge of Excel VBA is
extremely limited and is advancing at a snail's pace.

Here's the code:

Sub findit()
Dim cell As Range
Dim sFirst As String

Set cell = Cells.Find("A")
If Not cell Is Nothing Then
MsgBox cell.Address
sFirst = cell.Address
Do
Set cell = Cells.FindNext(cell)
If Not cell Is Nothing And cell.Address <> sFirst Then
MsgBox cell.Address
End If
Loop Until cell Is Nothing Or sFirst = cell.Address
End If
End Sub

Thank you for the trouble you've taken.

 

[Cloudfall]

My implementation of this example code did not work. The example code
seems to use some sort of technique of being able to "remotely" address
ranges of cells and do stuff with and to them without them having to be
activated. I do not know how to do this. All my code requires me to
work with active cells (I'm a beginner), and when I'm not working with
them I have cell A1 on the worksheet activated. So, when I first
implemented the example, instead of the "found" cell being manipulated,
cell A1 was. But I was "clever". I realised what the problem was. I
added the following line of code:

Set cell = Cells.Find("A")
cell.Activate

Yo! Problem solved! Until you get to the last item, whereupon our old
friend "Run-time error '91':..." reappears.

Houston, I have a problem. I am a beginner who is not yet comfortable
in the ".activate" world, and yet now to solve this problem I have to
trek to parts unknown. Gaaahh!!

 

[Cloudfall]

OK, I know why it's "Set cell = Cells.FindNext(cell)" (because the cell
in FindNext(cell) specifies the beginning of the search, which is where
the last item was found).

 

[Bob Phillips]

But if you don't learn these 'advanced' techniques, you will always be a
beginner, and not move on.

Did you try the code and get a problem, if so, explain the problem and post
your implemented code.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Cloudfall]

You are right of course, so here I go. Sorry about the whinge. It was
Friday afternoon and it wasn't working. The following is my implemented
code which closely follows your suggestion:

Sub subProcessSuppliersInLiquidation()
Dim lRowNumberSource As Long
Dim cell As Range
Dim sFirst As String

'Copy column headers to "StatusInLiquidation" sheet
Sheets("StatusInLiquidation").Select
subClearSheet ("StatusInLiquidation") 'my subprocedure to clear the
sheet
Sheets("StatusUpdatedActive").Select
Rows("1:3").Select
Selection.Copy
Sheets("StatusInLiquidation").Select
Range("A1").Select
ActiveSheet.Paste
Range("A4").Activate

Sheets("StatusUpdatedActive").Select
Range("A1").Activate

Set cell = Cells.Find("liquidat")
If Not cell Is Nothing Then
lRowNumberSource = cell.Row

Rows(lRowNumberSource).Select
Selection.Cut
Sheets("StatusInLiquidation").Select

ActiveSheet.Paste
Selection.Offset(1, 0).Activate
Sheets("StatusUpdatedActive").Select

sFirst = cell.Address

Do
Set cell = Cells.FindNext(cell)
'At next statement "Run-time error '91'" when cell is
"Nothing"
If Not cell Is Nothing And cell.Address <> sFirst Then
cell.Activate
lRowNumberSource = ActiveCell.Row

Rows(lRowNumberSource).Select
Selection.Cut
Sheets("StatusInLiquidation").Select

ActiveSheet.Paste
Selection.Offset(1, 0).Activate
Sheets("StatusUpdatedActive").Select
End If
Loop Until cell Is Nothing Or sFirst = cell.Address
End If
Sheets("StatusUpdatedActive").Select
Range("A4").Activate
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

My analysis is if cell is "Nothing" then the statements within the
"If..End If" should not execute and we shoud exit the loop. Instead you
get a "Run-time error '91'".

 

[Cloudfall]

I finally found out what my problem was. Embarassingly, I have
obviously had this problem before because the textbook I use has only
two yellow post-it notes in it, and one of them was on the page with
the solution on it. The solution had pencilled arrows and asterisks
pointing to it. I am posting this so that some other beginner doesn't
have to go through the frustrations that I did over the past few weeks.

The reference work I am quoting from is "Using Excel Visual Basic for
Applications" 2nd Ed. by Jeff Webb from Que. On page 277 it says:

Finding and Replacing Text in Cells.
The Find and Replace methods act on the text in the cells of a single
sheet. If you are accustomed to finding and replacing text using word
processing software, Excel's behavior might seem confusing. Here are
some points to remember when using these methods:
1. Find and Replace are limited to a single sheet at a time.
2. Find never reaches the 'end' of a range, it simply restarts its
search at the top or bottom of the range. See the 'Finding All
Occurrences of Text' section for an example of how to work around this.
3. If the text is not found, Find returns Nothing, which causes an
error if you try to activate the returned value. You must always test
the result of Find before doing anything with the result.
4. Replace replaces all instances in a range; you can't selectively
search and replace using Replace.

Point 3 above was relevant for me. The author gives the following code
for testing the result of Find before doing anything with the result:

If TypeName(ws.Cells.Find(SearchItem)) = "Range" Then
:
End If

This is what worked for me. Not using this test caused my confusion and
made me to ask all the stupid questions in this forum. I hope this
helps someone else save days of frustration. I myself have received so
much help from this group that I feel I must give something back.