Hi all,

Just wondering if I've missed something obvious or if this can't be
done.

Basically I'm trying to write a function that will search for similar
names on a different sheet and return a comma separated string of the
matching names. Here's what I've got as a module within the workbook
(by the way this is Excel 2003 SP2 on XP Pro) -

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:=ActiveCell, 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

However this fails on the 4th line set c = .find and gives the classic
type mismatch. What I've found is that it works perfectly if I select
the sheet and then run the function, but obviously when using the
function on a different sheet you can't select the sheet first -
besides it makes the user experience a bit bad.

Any ideas? Either to fix or a new approach to solve the same thing.

Only thing I can think of at the moment is to put the list of clients
on the same worksheet, but this isn't the nice solution I was looking
for.

Thanks in advance,

Brad