Second search within primary "Find" gives error ?
 

The following code extracts text to a listbox based on a find routine.
If I try to extract further information off another sheet based on the
"find" of the the first search it complains with an error code 91. The
additional code is between the '===========
What am I doing wrong?

Sub Fill_Task_TextBox()
GroupNumber = Cells(ActiveCell.Row, 10).Value
With Sheets("Tasks").Range("A3:A" & Sheets("Tasks").Range
_("A65530").EndxlUp).Row)
Set Task_Group = .Find(GroupNumber, lookin:=xlValues)
If Not Task_Group Is Nothing Then
Tasks.ListBox1.Clear
FirstAddress = Task_Group.Address
Tasks.ListBox1.AddItem Task_Group.Value & " __ " & _
ActiveCell(0, -1).Value & " __ " & ActiveCell.Value
Tasks.ListBox1.AddItem
Do
Tasks.ListBox1.AddItem Task_Group.Offset(0, 1).Value & _
"-- " & Task_Group.Offset(0, 6).Value

' =============================================== Extra
search Start
If Len(Task_Group.Offset(0, 8).Value) 4 Then
Std_txt_Number = Left(Task_Group.Offset(0, 8).Value, 6)
With Sheets("Std_txt").Range("A3:A" & _
Sheets("Std_txt").Range("A65530").End(xlUp).Row)
Set Stdtxt = .Find(Std_txt_Number, lookin:=xlValues)
If Not Stdtxt Is Nothing Then
Tasks.ListBox1.AddItem Stdtxt.Offset(0,
2).Value
End If
End With
End If
' =============================================== Extra
Search Stop

Set Task_Group = .FindNext(Task_Group)
Loop While Not Task_Group Is Nothing And _
Task_Group.Address < FirstAddress ' get error 91 on
this line?
End If
Tasks.ListBox1.AddItem ""
Tasks.Show
End With

End Sub

Second search within primary "Find" gives error ?
 

Mike

You can't nest Finds like that. FindNext will always use the last Find that
you did. So this line

Set Task_Group = .FindNext(Task_Group)

is acutally looking for Std_txt_Number on the Tasks sheet, which it doesn't
find. That makes Task_Group Nothing and you get the error accessing the
Address property of Nothing. For you internal find, you'll have to loop
through the cells on Std_txt instead of using a Find. Althernatively,
instead of using FindNext at the end of Do Loop, you could just redo the
Find specifying the After argument. Instead of

Set Task_Group = .FindNext(Task_Group)

use

Set Task_Group = .Find(GroupNumber,Task_Group,xlValues)

You lose the ease of FindNext, but it may be better than looping through
Std_txt.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

MikeR wrote:
The following code extracts text to a listbox based on a find routine.
If I try to extract further information off another sheet based on the
"find" of the the first search it complains with an error code 91. The
additional code is between the '===========
What am I doing wrong?

Sub Fill_Task_TextBox()
GroupNumber = Cells(ActiveCell.Row, 10).Value
With Sheets("Tasks").Range("A3:A" & Sheets("Tasks").Range
_("A65530").EndxlUp).Row)
Set Task_Group = .Find(GroupNumber, lookin:=xlValues)
If Not Task_Group Is Nothing Then
Tasks.ListBox1.Clear
FirstAddress = Task_Group.Address
Tasks.ListBox1.AddItem Task_Group.Value & " __ " & _
ActiveCell(0, -1).Value & " __ " & ActiveCell.Value
Tasks.ListBox1.AddItem
Do
Tasks.ListBox1.AddItem Task_Group.Offset(0, 1).Value & _
"-- " & Task_Group.Offset(0, 6).Value

' =============================================== Extra
search Start
If Len(Task_Group.Offset(0, 8).Value) 4 Then
Std_txt_Number = Left(Task_Group.Offset(0, 8).Value, 6)
With Sheets("Std_txt").Range("A3:A" & _
Sheets("Std_txt").Range("A65530").End(xlUp).Row)
Set Stdtxt = .Find(Std_txt_Number, lookin:=xlValues)
If Not Stdtxt Is Nothing Then
Tasks.ListBox1.AddItem Stdtxt.Offset(0,
2).Value
End If
End With
End If
' =============================================== Extra
Search Stop

Set Task_Group = .FindNext(Task_Group)
Loop While Not Task_Group Is Nothing And _
Task_Group.Address < FirstAddress ' get error 91 on
this line?
End If
Tasks.ListBox1.AddItem ""
Tasks.Show
End With

End Sub

Second search within primary "Find" gives error ?
 

"Dick Kusleika" wrote in message ...
Mike

You can't nest Finds like that. FindNext will always use the last Find that
you did. So this line

Set Task_Group = .FindNext(Task_Group)

is acutally looking for Std_txt_Number on the Tasks sheet, which it doesn't
find. That makes Task_Group Nothing and you get the error accessing the
Address property of Nothing. For you internal find, you'll have to loop
through the cells on Std_txt instead of using a Find. Althernatively,
instead of using FindNext at the end of Do Loop, you could just redo the
Find specifying the After argument. Instead of

Set Task_Group = .FindNext(Task_Group)

use

Set Task_Group = .Find(GroupNumber,Task_Group,xlValues)

You lose the ease of FindNext, but it may be better than looping through
Std_txt.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Dick, thanks for the prompt reply.... the "Set Task_Group =
..Find(GroupNumber,Task_Group,xlValues)" << worked first time.... will
remember this for future use ..
many thanks Mike