IsError error on Search

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I am using the IsError function nested in an If statement to try and get
the macro to skip over some steps if the search comes up empty. I pass the
search variable (Acct) from a function based on the number of the loop
(counter). Works fine if the search has a positive result, but gets an
object error if search is negative. Here is the code:

Do Until Counter > 38
ACCTNAME (Counter) ' passes Acct name back to sub
Columns("B:B").Select
If IsError(Selection.Find(What:=Acct, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate) Then
'skip
Else.......

Any help will be appreciated.

Thanks

Marc
 
I'd use:

Dim res as variant

Do Until Counter > 38
ACCTNAME (Counter) ' passes Acct name back to sub
with activesheet.Columns("B:B")
res = .cells.find(what:=Acct, _
After:=.cells(.cells.count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)

if iserror(res) then
'not found
else
'was found
end if

....
 
Didn't quite work. Still get the object error
"Run-time error 91"

Object variable or With block variable not set
 
It's easier, instead of using IsError, just create a range object and set it
to the results of the find.

Dim c as range
Do Until Counter > 38
ACCTNAME (Counter) ' passes Acct name back to sub
Columns("B:B").Select
c=Selection.Find(What:=Acct, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=True, SearchFormat:=False
if c is nothing then
'skip
Else.......
 
I don't know what I was thinking in that other post. For some reason, I was
thinking that you were using application.match() instead of .find. And I mixed
up everything.

Sorry.



Dim FoundCell as range
Do Until Counter > 38
ACCTNAME (Counter) ' passes Acct name back to sub
with activesheet.Columns("B:B")
set foundcell = .cells.find(what:=Acct, _
After:=.cells(.cells.count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
end with

if foundcell is nothing then
'not found
else
'was found
end if
.....
 
Oops, forgot "Set". Should be Set c=Selection.Find.... Well, Dave's
working with you, so you're in good hands....
 
Thanks Dave, this works great!

Marc

Dave Peterson said:
I don't know what I was thinking in that other post. For some reason, I was
thinking that you were using application.match() instead of .find. And I mixed
up everything.

Sorry.



Dim FoundCell as range
Do Until Counter > 38
ACCTNAME (Counter) ' passes Acct name back to sub
with activesheet.Columns("B:B")
set foundcell = .cells.find(what:=Acct, _
After:=.cells(.cells.count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
end with

if foundcell is nothing then
'not found
else
'was found
end if
.....
Click to expand...
 
Back
Top