Hi Group,
I am doing several Find procedures in a module. I do not always expect to
Find the text so I get a 91 error. The first instance of the error is handled
by an error procedure "On Error GoTo ErrorHandler" where I clear the error
ErrorHandler:
Err.Clear
This is fine, but I am repeating the Find procedure several more times and
can not predict where and when it might fail again. Subsequent failures do
not allow me to use another "On Error GoTo ErrorHandlerTwo", note the
different name. I can not use a Resume Next, becasue on a failure I need to
skip certain code steps, so I want to continue the code after the
ErrorHandler or ErrorHandlerTwo or Three, etc.

Suggestions would be appreciated. It maybe that I have to Dim and Set, but I
am not sure how to do this for each possible instance of a Find failure.

Thanks
--
David

On Error GoTo 0 ' Have you tried this in place of "Err.Clear"?
--
Best wishes,

Jim


"David" wrote:

Hi Group,
I am doing several Find procedures in a module. I do not always expect to
Find the text so I get a 91 error. The first instance of the error is handled
by an error procedure "On Error GoTo ErrorHandler" where I clear the error
ErrorHandler:
Err.Clear
This is fine, but I am repeating the Find procedure several more times and
can not predict where and when it might fail again. Subsequent failures do
not allow me to use another "On Error GoTo ErrorHandlerTwo", note the
different name. I can not use a Resume Next, becasue on a failure I need to
skip certain code steps, so I want to continue the code after the
ErrorHandler or ErrorHandlerTwo or Three, etc.

Suggestions would be appreciated. It maybe that I have to Dim and Set, but I
am not sure how to do this for each possible instance of a Find failure.

Thanks
--
David

Would it not be easy to create a function or a procedure, where you
supply the desired searching range and return TRUE or FALSE if found?
If you do that then you'd have to worry about taking care of error
generation only once.

Something like this may be:

Code:
--------------------
Function WasFound(rng As Range, sFindWhat) As Boolean
On Error GoTo errorhandler
Dim tmp
tmp = rng.Find(what:=sFindWhat, LookIn:=xlValues)
WasFound = True
errorhandler:
WasFound = False
End Function
--------------------


--
a7n9


------------------------------------------------------------------------
a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149
View this thread: http://www.excelforum.com/showthread...hreadid=562936

Unless I'm missing something, does this not work:
Dim FoundCell As Range

For I =1 To 42
On Error Resume Next
Set FoundCell=Cells.Find(..Whatever...)
'return to normal error handling if necessary
On Error GoTo 0
If Not FoundCell Is Nothing
'Do some processing
End If
Next

NickHK

"David" ...
Hi Group,
I am doing several Find procedures in a module. I do not always expect to
Find the text so I get a 91 error. The first instance of the error is
handled
by an error procedure "On Error GoTo ErrorHandler" where I clear the error
ErrorHandler:
Err.Clear
This is fine, but I am repeating the Find procedure several more times and
can not predict where and when it might fail again. Subsequent failures do
not allow me to use another "On Error GoTo ErrorHandlerTwo", note the
different name. I can not use a Resume Next, becasue on a failure I need
to
skip certain code steps, so I want to continue the code after the
ErrorHandler or ErrorHandlerTwo or Three, etc.

Suggestions would be appreciated. It maybe that I have to Dim and Set, but
I
am not sure how to do this for each possible instance of a Find failure.

Thanks
--
David

Rather than trap the error, why not intelligently avoid it. See this pseudo
code:

Dim rng as Range, arr as Variant
Dim i as long
arr = Array("John","Bob","Sue","Phil")
for i = lbound(arr) to ubound(arr)
set rng = Nothing
' no activate - assign to a range variable
set rng = columns(1).Find(arr(i), . . . other arguments . . . )
if not rng is nothing then
rng.select
msgbox "found at " & rng.address
else
msgbox arr(i) & " not found"
end if
Next

--
Regards,
Tom Ogilvy


"David" wrote:

Hi Group,
I am doing several Find procedures in a module. I do not always expect to
Find the text so I get a 91 error. The first instance of the error is handled
by an error procedure "On Error GoTo ErrorHandler" where I clear the error
ErrorHandler:
Err.Clear
This is fine, but I am repeating the Find procedure several more times and
can not predict where and when it might fail again. Subsequent failures do
not allow me to use another "On Error GoTo ErrorHandlerTwo", note the
different name. I can not use a Resume Next, becasue on a failure I need to
skip certain code steps, so I want to continue the code after the
ErrorHandler or ErrorHandlerTwo or Three, etc.

Suggestions would be appreciated. It maybe that I have to Dim and Set, but I
am not sure how to do this for each possible instance of a Find failure.

Thanks
--
David

Maybe you could just check the results in line and drop the "on error" stuff
completely:

dim FoundCell as range
set foundcell = something.find(what:=....)
if foundcell is nothing then
'not found
else
'found it, do the work code goes here
end if



David wrote:

Hi Group,
I am doing several Find procedures in a module. I do not always expect to
Find the text so I get a 91 error. The first instance of the error is handled
by an error procedure "On Error GoTo ErrorHandler" where I clear the error
ErrorHandler:
Err.Clear
This is fine, but I am repeating the Find procedure several more times and
can not predict where and when it might fail again. Subsequent failures do
not allow me to use another "On Error GoTo ErrorHandlerTwo", note the
different name. I can not use a Resume Next, becasue on a failure I need to
skip certain code steps, so I want to continue the code after the
ErrorHandler or ErrorHandlerTwo or Three, etc.

Suggestions would be appreciated. It maybe that I have to Dim and Set, but I
am not sure how to do this for each possible instance of a Find failure.

Thanks
--
David

--

Dave Peterson

Thank you group, several of these solutions look good. I will try them.

Thanks,
--
David


"Dave Peterson" wrote:

Maybe you could just check the results in line and drop the "on error" stuff
completely:

dim FoundCell as range
set foundcell = something.find(what:=....)
if foundcell is nothing then
'not found
else
'found it, do the work code goes here
end if



David wrote:

Hi Group,
I am doing several Find procedures in a module. I do not always expect to
Find the text so I get a 91 error. The first instance of the error is handled
by an error procedure "On Error GoTo ErrorHandler" where I clear the error
ErrorHandler:
Err.Clear
This is fine, but I am repeating the Find procedure several more times and
can not predict where and when it might fail again. Subsequent failures do
not allow me to use another "On Error GoTo ErrorHandlerTwo", note the
different name. I can not use a Resume Next, becasue on a failure I need to
skip certain code steps, so I want to continue the code after the
ErrorHandler or ErrorHandlerTwo or Three, etc.

Suggestions would be appreciated. It maybe that I have to Dim and Set, but I
am not sure how to do this for each possible instance of a Find failure.

Thanks
--
David

--

Dave Peterson

Hi Dave,
Your solution looked the easiest to me, so I tried it out, but I am getting
a "run-time error 424 object required".
The code I used is as follows:

Dim FoundCell As Range
Workbooks.OpenText Filename:="A:\AGLR110", Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(3, 1),
Array(9, 1), _
Array(18, 1), Array(72, 1), Array(90, 1)), TrailingMinusNumbers:=True
Range("A1").Select
Set FoundCell = Cells.Find(What:="2500", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
If FoundCell Is Nothing Then
Else
Acct2500100 = ActiveCell.Offset(0, 4).Value
End If

I thought the Dim and Set statement would be sufficient, but maybe the Cells
is not a range object, although I figured the activate, must be a range
object. The "2500" is in the worksheet. If I am able to get this to work,
will have to Redim FoundCell to use it in another Cells.Find or can I
continue to the same variable and Set statement?

Thank you for your help,
--
David


"Dave Peterson" wrote:

Maybe you could just check the results in line and drop the "on error" stuff
completely:

dim FoundCell as range
set foundcell = something.find(what:=....)
if foundcell is nothing then
'not found
else
'found it, do the work code goes here
end if



David wrote:

Hi Group,
I am doing several Find procedures in a module. I do not always expect to
Find the text so I get a 91 error. The first instance of the error is handled
by an error procedure "On Error GoTo ErrorHandler" where I clear the error
ErrorHandler:
Err.Clear
This is fine, but I am repeating the Find procedure several more times and
can not predict where and when it might fail again. Subsequent failures do
not allow me to use another "On Error GoTo ErrorHandlerTwo", note the
different name. I can not use a Resume Next, becasue on a failure I need to
skip certain code steps, so I want to continue the code after the
ErrorHandler or ErrorHandlerTwo or Three, etc.

Suggestions would be appreciated. It maybe that I have to Dim and Set, but I
am not sure how to do this for each possible instance of a Find failure.

Thanks
--
David

--

Dave Peterson

You should have removed the activate

Dim FoundCell As Range
Workbooks.OpenText Filename:="A:\AGLR110", _
Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(9, 1), _
Array(18, 1), Array(72, 1), Array(90, 1)), _
TrailingMinusNumbers:=True
Range("A1").Select
Set FoundCell = Cells.Find( _
What:="2500", _
After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
' give yourself some feedback
msgbox "2500 wasn't found"
Else
Acct2500100 = ActiveCell.Offset(0, 4).Value
End If

This is basically what I gave you originally.

--
Regards,
Tom Ogilvy


"David" wrote:

Hi Dave,
Your solution looked the easiest to me, so I tried it out, but I am getting
a "run-time error 424 object required".
The code I used is as follows:

Dim FoundCell As Range
Workbooks.OpenText Filename:="A:\AGLR110", Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(3, 1),
Array(9, 1), _
Array(18, 1), Array(72, 1), Array(90, 1)), TrailingMinusNumbers:=True
Range("A1").Select
Set FoundCell = Cells.Find(What:="2500", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
If FoundCell Is Nothing Then
Else
Acct2500100 = ActiveCell.Offset(0, 4).Value
End If

I thought the Dim and Set statement would be sufficient, but maybe the Cells
is not a range object, although I figured the activate, must be a range
object. The "2500" is in the worksheet. If I am able to get this to work,
will have to Redim FoundCell to use it in another Cells.Find or can I
continue to the same variable and Set statement?

Thank you for your help,
--
David


"Dave Peterson" wrote:

Maybe you could just check the results in line and drop the "on error" stuff
completely:

dim FoundCell as range
set foundcell = something.find(what:=....)
if foundcell is nothing then
'not found
else
'found it, do the work code goes here
end if



David wrote:

Hi Group,
I am doing several Find procedures in a module. I do not always expect to
Find the text so I get a 91 error. The first instance of the error is handled
by an error procedure "On Error GoTo ErrorHandler" where I clear the error
ErrorHandler:
Err.Clear
This is fine, but I am repeating the Find procedure several more times and
can not predict where and when it might fail again. Subsequent failures do
not allow me to use another "On Error GoTo ErrorHandlerTwo", note the
different name. I can not use a Resume Next, becasue on a failure I need to
skip certain code steps, so I want to continue the code after the
ErrorHandler or ErrorHandlerTwo or Three, etc.

Suggestions would be appreciated. It maybe that I have to Dim and Set, but I
am not sure how to do this for each possible instance of a Find failure.

Thanks
--
David

--

Dave Peterson

You should have removed the activate

Dim FoundCell As Range
Workbooks.OpenText Filename:="A:\AGLR110", _
Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(9, 1), _
Array(18, 1), Array(72, 1), Array(90, 1)), _
TrailingMinusNumbers:=True
Range("A1").Select
Set FoundCell = Cells.Find( _
What:="2500", _
After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
' give yourself some feedback
msgbox "2500 wasn't found"
Else
Acct2500100 = ActiveCell.Offset(0, 4).Value
End If

This is basically what I gave you originally.

--
Regards,
Tom Ogilvy



"David" wrote:

Hi Dave,
Your solution looked the easiest to me, so I tried it out, but I am getting
a "run-time error 424 object required".
The code I used is as follows:

Dim FoundCell As Range
Workbooks.OpenText Filename:="A:\AGLR110", Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(3, 1),
Array(9, 1), _
Array(18, 1), Array(72, 1), Array(90, 1)), TrailingMinusNumbers:=True
Range("A1").Select
Set FoundCell = Cells.Find(What:="2500", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
If FoundCell Is Nothing Then
Else
Acct2500100 = ActiveCell.Offset(0, 4).Value
End If

I thought the Dim and Set statement would be sufficient, but maybe the Cells
is not a range object, although I figured the activate, must be a range
object. The "2500" is in the worksheet. If I am able to get this to work,
will have to Redim FoundCell to use it in another Cells.Find or can I
continue to the same variable and Set statement?

Thank you for your help,
--
David


"Dave Peterson" wrote:

Maybe you could just check the results in line and drop the "on error" stuff
completely:

dim FoundCell as range
set foundcell = something.find(what:=....)
if foundcell is nothing then
'not found
else
'found it, do the work code goes here
end if



David wrote:

Hi Group,
I am doing several Find procedures in a module. I do not always expect to
Find the text so I get a 91 error. The first instance of the error is handled
by an error procedure "On Error GoTo ErrorHandler" where I clear the error
ErrorHandler:
Err.Clear
This is fine, but I am repeating the Find procedure several more times and
can not predict where and when it might fail again. Subsequent failures do
not allow me to use another "On Error GoTo ErrorHandlerTwo", note the
different name. I can not use a Resume Next, becasue on a failure I need to
skip certain code steps, so I want to continue the code after the
ErrorHandler or ErrorHandlerTwo or Three, etc.

Suggestions would be appreciated. It maybe that I have to Dim and Set, but I
am not sure how to do this for each possible instance of a Find failure.

Thanks
--
David

--

Dave Peterson

Drop the .activate

Set FoundCell = Cells.Find(What:="2500", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

(You recorded it, but didn't delete it.)

David wrote:

Hi Dave,
Your solution looked the easiest to me, so I tried it out, but I am getting
a "run-time error 424 object required".
The code I used is as follows:

Dim FoundCell As Range
Workbooks.OpenText Filename:="A:\AGLR110", Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(3, 1),
Array(9, 1), _
Array(18, 1), Array(72, 1), Array(90, 1)), TrailingMinusNumbers:=True
Range("A1").Select
Set FoundCell = Cells.Find(What:="2500", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
If FoundCell Is Nothing Then
Else
Acct2500100 = ActiveCell.Offset(0, 4).Value
End If

I thought the Dim and Set statement would be sufficient, but maybe the Cells
is not a range object, although I figured the activate, must be a range
object. The "2500" is in the worksheet. If I am able to get this to work,
will have to Redim FoundCell to use it in another Cells.Find or can I
continue to the same variable and Set statement?

Thank you for your help,
--
David

"Dave Peterson" wrote:

Maybe you could just check the results in line and drop the "on error" stuff
completely:

dim FoundCell as range
set foundcell = something.find(what:=....)
if foundcell is nothing then
'not found
else
'found it, do the work code goes here
end if



David wrote:

Hi Group,
I am doing several Find procedures in a module. I do not always expect to
Find the text so I get a 91 error. The first instance of the error is handled
by an error procedure "On Error GoTo ErrorHandler" where I clear the error
ErrorHandler:
Err.Clear
This is fine, but I am repeating the Find procedure several more times and
can not predict where and when it might fail again. Subsequent failures do
not allow me to use another "On Error GoTo ErrorHandlerTwo", note the
different name. I can not use a Resume Next, becasue on a failure I need to
skip certain code steps, so I want to continue the code after the
ErrorHandler or ErrorHandlerTwo or Three, etc.

Suggestions would be appreciated. It maybe that I have to Dim and Set, but I
am not sure how to do this for each possible instance of a Find failure.

Thanks
--
David

--

Dave Peterson


--

Dave Peterson

Thank you this works well.

Have a good day,
--
David


"Dave Peterson" wrote:

Drop the .activate

Set FoundCell = Cells.Find(What:="2500", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

(You recorded it, but didn't delete it.)

David wrote:

Hi Dave,
Your solution looked the easiest to me, so I tried it out, but I am getting
a "run-time error 424 object required".
The code I used is as follows:

Dim FoundCell As Range
Workbooks.OpenText Filename:="A:\AGLR110", Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(3, 1),
Array(9, 1), _
Array(18, 1), Array(72, 1), Array(90, 1)), TrailingMinusNumbers:=True
Range("A1").Select
Set FoundCell = Cells.Find(What:="2500", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
If FoundCell Is Nothing Then
Else
Acct2500100 = ActiveCell.Offset(0, 4).Value
End If

I thought the Dim and Set statement would be sufficient, but maybe the Cells
is not a range object, although I figured the activate, must be a range
object. The "2500" is in the worksheet. If I am able to get this to work,
will have to Redim FoundCell to use it in another Cells.Find or can I
continue to the same variable and Set statement?

Thank you for your help,
--
David

"Dave Peterson" wrote:

Maybe you could just check the results in line and drop the "on error" stuff
completely:

dim FoundCell as range
set foundcell = something.find(what:=....)
if foundcell is nothing then
'not found
else
'found it, do the work code goes here
end if



David wrote:

Hi Group,
I am doing several Find procedures in a module. I do not always expect to
Find the text so I get a 91 error. The first instance of the error is handled
by an error procedure "On Error GoTo ErrorHandler" where I clear the error
ErrorHandler:
Err.Clear
This is fine, but I am repeating the Find procedure several more times and
can not predict where and when it might fail again. Subsequent failures do
not allow me to use another "On Error GoTo ErrorHandlerTwo", note the
different name. I can not use a Resume Next, becasue on a failure I need to
skip certain code steps, so I want to continue the code after the
ErrorHandler or ErrorHandlerTwo or Three, etc.

Suggestions would be appreciated. It maybe that I have to Dim and Set, but I
am not sure how to do this for each possible instance of a Find failure.

Thanks
--
David

--

Dave Peterson


--

Dave Peterson