When an error occurs, VBA code execution goes into "error mode" an no
subsequent error handling can occur until the code exits error mode
and resumes normal mode. You can exit error mode by one of (1) exiting
the procedure, (2) using Resume to continue code execution at the line
that cause the error, or (3) using Resume Next to continue execution
at the line following the line that caused the error, or (4) Resume
<Label to continue execution at a code label. If your error handling
doesn't do one of the above, it remains in error mode and any error
will break the code, regardless of the On Error setting. Note that
using Resume when the code is not running in error mode will cause an
error. The following code illustrates various aspects error handling:

Sub AAA()
Dim X As Long
Dim Y As Long
Dim N As Long

On Error GoTo EndLoop:
For N = 1 To 10
X = N
If X = 4 Or X = 5 Then
X = 0
End If
ResumeHe
Debug.Print N, X, N / X
EndLoop:
If Err.Number < 0 Then
X = 1
Resume ResumeHere
End If
Next N
End Sub

For an extensive discussion of error hanlding, see
http://www.cpearson.com/Excel/ErrorHandling.htm.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Sat, 19 Dec 2009 14:53:57 -0700, John Keith wrote:

I have the following code with an on error statement:

For i = 1 To zz
search_term = Workbooks("search.xls").Worksheets("Search
Terms").Cells(i, 1)
On Error GoTo not_found
search_column = Cells.Find(What:=search_term, After:=[A1],
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Column

[Do Stuff if search_term is found on worksheet]

not_found:
On Error GoTo 0
Next i

The for loop should go through 155 terms. The first time the
search_term is not found the on error statement works as it should and
the code is bypassed. But the second time the search_term is not found
I get an error message on the code line with the Find function.

What is going on with my error handling?

Is there a better way to control my flow?


John Keith