It worked fine for me when I called it from a Subroutine - but not in the
worksheet. I know Find did not work in a UDF until xl2002 as I recall.
Perhaps find next still doesn't work. I would replace your findnext with
just FIND. So this worked for me (in the worksheet)

Public Function clients(mystr As String) As String
Dim wksht As Worksheet, c As Range, firstaddress As String

Set wksht = Sheets("Clients")
With wksht.UsedRange
Set c = .Find(What:=mystr, After:=wksht.UsedRange(1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
If Len(clients) = 0 Then
clients = c.Text
Else
If InStr(1, clients, c.Text) = 0 Then
clients = clients & ", " & c.Text
End If
End If
Set c = .Find(What:=mystr, After:=c, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With

End Function

--
Regards,
Tom Ogilvy


"bradsalmon" wrote:


Thanks Tom

I should have spotted that myself, but couldn't see the wood for the
trees.

That's got the function working but it's not performing the findnext
part properly. Basically it just finds the first entry and returns
that as the findnext is setting c to nothing each time.

Any idea on what I need to change on the line set c = .FindNext(c)

c.Address just gives me an error and I can't figure out how to
incorporate the worksheet name

Thanks

On 5 Jun, 15:02, Tom Ogilvy
wrote:
the problem is that ActiveCell isn't within the range being searched. I have
made an adjustment which should work.

Public Function clients(mystr As String) As String
Dim wksht As Worksheet, c As Range, firstaddress As String

Set wksht = Sheets("Clients")
With wksht.UsedRange
Set c = .Find(What:=mystr, _
After:=wksht.UsedRange(1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
If Len(clients) = 0 Then
clients = c.Text
Else
If InStr(1, clients, c.Text) = 0 Then
clients = clients & ", " & c.Text
End If
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With

End Function

--
Regards,
Tom Ogilvy