G
Guest
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.
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.