Need help on error msg for "find()"
 

I have a sub simplifed as following to search for "Activity ID" on a
worksheet as shown below

0 sheets(5).activate
1 range("A3").select
2 i = 3
3 while i < 200
4 Activity_ID = Sheets(3).range("P" & cstr(i))
5 On Error GoTo Err_ACT_NOT_FOUND
6 ' Performs a search on a sorted list in the active worksheet
(i.e. sheets(5)
7 Cells.Find(What:=Activity_ID, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt :=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext,MatchCase:=False, _
SearchFormat:=False).Select
8 On Error GoTo 0
9 ' process the data once the desired Activity ID is found
10 i = i + 1
11 Goto SKIPOVER
12 Err_ACT_NOT_FOUND:
13 Msgbox "Activity ID '" & Activity_ID & "' not found.", VbInformation
14 SKIPOVER:
15 Wend

When it first encounters an Activity ID does not exist in "Sheets(5)", it
displays the message 'Activit ID xxxx not found ok. However, when it cannot
find another Activity ID in sheets(5), Excel prompts me the following error
msg:
Run-time error '91:'
Object variable or With block variable not set.
When I click debug, it points at line # 7 which is the "Cells.Find (...."
command.

Can someone please tell me what is the problem with this find stmt resulting
in this error msg ?

Thanks in advance for any help.

Need help on error msg for "find()"
 

Maybe you can check for that found cell easier than having the code blow up:

Dim FoundCell as range

sheets(5).activate
range("A3").select
i = 3
while i < 200
Activity_ID = Sheets(3).range("P" & cstr(i))
set foundcell = Cells.Find(What:=Activity_ID, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt :=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext,MatchCase:=False, _
SearchFormat:=False)
if foundcell is nothing then
'not found
else
'do what you want
end if
Wend



Wellie wrote:

I have a sub simplifed as following to search for "Activity ID" on a
worksheet as shown below

0 sheets(5).activate
1 range("A3").select
2 i = 3
3 while i < 200
4 Activity_ID = Sheets(3).range("P" & cstr(i))
5 On Error GoTo Err_ACT_NOT_FOUND
6 ' Performs a search on a sorted list in the active worksheet
(i.e. sheets(5)
7 Cells.Find(What:=Activity_ID, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt :=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext,MatchCase:=False, _
SearchFormat:=False).Select
8 On Error GoTo 0
9 ' process the data once the desired Activity ID is found
10 i = i + 1
11 Goto SKIPOVER
12 Err_ACT_NOT_FOUND:
13 Msgbox "Activity ID '" & Activity_ID & "' not found.", VbInformation
14 SKIPOVER:
15 Wend

When it first encounters an Activity ID does not exist in "Sheets(5)", it
displays the message 'Activit ID xxxx not found ok. However, when it cannot
find another Activity ID in sheets(5), Excel prompts me the following error
msg:
Run-time error '91:'
Object variable or With block variable not set.
When I click debug, it points at line # 7 which is the "Cells.Find (...."
command.

Can someone please tell me what is the problem with this find stmt resulting
in this error msg ?

Thanks in advance for any help.

--

Dave Peterson