Function to search different worksheet

  • Thread starter Thread starter bradsalmon
  • Start date Start date
B

bradsalmon

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
 
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
 
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
 
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 said:
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
Click to expand...
 
Tom that is brilliant and works perfectly

Thank you very much, I would have been looking into why that wasn't
working for hours.
 
Back
Top