Find error

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
>