Find error

[Guest]

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

 

[Guest]

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

 

[a7n9]

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

 

[NickHK]

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

 

[Guest]

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

 

[Dave Peterson]

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

 

[Guest]

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

Thanks,

 

[Guest]

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,

 

[Guest]

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

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,

 

[Guest]

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

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,

 

[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.)

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,

 

[Guest]

Thank you this works well.

Have a good day,