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

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

"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