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


--
Regards,

OssieMac


"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

.