why doesn't on error work?

[John Keith]

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
(e-mail address removed)

 

[OssieMac]

Hi John,

The following is the usual method of using find. You don't need on error.
You set a range variable to the found cell and if it is nothing then it is
not found but if it is Not nothing then it is found.

Also include the sheet name with ranges either using With or as I have doen
in the example. That way if your code ever makes another worksheet the active
sheet, the code still works.

You can discard the msgboxes and also the else in the If/Then/Else. they are
only there for testing purposes.

Dim search_term
Dim rngFound As Range
Dim search_column As Long
Dim i As Long
Dim zz As Long

zz = 5 'Used for testing

For i = 1 To zz
search_term = Worksheets("Search Terms").Cells(i, 1)

'Edit "Sheet2" in following line
'to match your worksheet name
Set rngFound = Sheets("Sheet2") _
.Cells.Find(What:=search_term, _
After:=[A1], _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngFound Is Nothing Then
search_column = rngFound.Column
MsgBox "found " & search_term & " at column " & search_column
'[Do Stuff if search_term is found on worksheet]
Else
MsgBox "not found"
End If

Next i

 

[OssieMac]

forgot to say re-insert your workbook name in the following line of your
code. I took it out because I tested in a single workbook.

search_term = Workbooks("search.xls").Worksheets("Search Terms").Cells(i, 1)

 

[Ryan H]

I would highly recommend you not use On Error Resume Next in your code.
Usually 99.99% of the time you can develop some code in order to not us this
statement. You are certainly asking for trouble with that statement. Now
that I am done punishing you I would alter your code just a bit, like below.
Let me know if this helps! If so, let me know, click "YES" below.

Sub OnError()

Dim i As Long
Dim Search_Range As Range
Dim myColumn As Long

For i = 1 To zz

search_term = Workbooks("search.xls").Worksheets("SearchTerms
").Cells(i, 1)

Set Search_Range = Cells.Find(What:=search_term, _
After:=[A1], _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Search_Range Is Nothing Then
myColumn = Search_Range.Column

'Do Stuff if search_term is found on worksheet
End If

Next i

End Sub

 

[Chip Pearson]

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
ResumeHere:
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]

 

[John Keith]

The following is the usual method of using find. You don't need on error.
You set a range variable to the found cell and if it is nothing then it is
not found but if it is Not nothing then it is found.

OssieMac,

Thank for the more usual method! When I first ran into the need to
handle the situation I tried to implement this method but I'm still
enough of a beginner that I couldn't do it right so I treid the "on
error" method and still failed with that! I look forward to the day
when I'll be more capable like you and many of the other experts
lurking in this group!


John Keith
(e-mail address removed)

 

[John Keith]

I would highly recommend you not use On Error Resume Next in your code.
Usually 99.99% of the time you can develop some code in order to not us this
statement. You are certainly asking for trouble with that statement. Now
that I am done punishing you I would alter your code just a bit, like below.

Ryan,

The punishment was not too harsh, I'm still learning, thank you for
the suggestion.


John Keith
(e-mail address removed)

 

[John Keith]

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.

Chip,

Thank you for the quick tutorial, I haven't been to your website on
error handling but will do so after the holiday.

But from what I read I guess that "On Error GoTo 0" is not one of the
methods to reset the mode.


John Keith
(e-mail address removed)