Thanks...tried that and it does the trick for the requirement

"JMB" wrote:

I understand. Functions are more limited when called from a worksheet vs.
called from a vba subroutine - some vba methods just cannot be done in a UDF
intended to be called from a worksheet (I've tried Find before in a udf and
never got anywhere - so I don't know how or if it can be done).

You could try (array entered w/Cntrl+Shift+Enter or you'll get #VALUE error):

=INDEX('Contact Details'!C5:C55&" "&'Contact
Details'!D5:D55,MATCH(1,('Contact Details'!A5:A55=ClientNumber)*('Contact
Details'!B5:B55="Billing"),0))

Where ClientNumber is a cell reference to the client number you want (or you
could hardcode it into the formula).


"scott56hannah" wrote:

JMB,

Yes this works when it is called directly from a Sub ...End Sub
routine....but when I try and call it from a function on a worksheet as a
function....see the code in my first post then it does not return a value
past the first value on the .Find the .FindNext does not seem to return the
next value for some reason....

I am trying to get the function call working

thanks
Scott

"JMB" wrote:

Also, double check for any leading/trailing spaces in the data. Just in
case, you could use:

If Trim(UCase(R.Value)) = "BILLING"



"scott56hannah" wrote:

Jim,

Yes....that is what I am currently testing to finalise the function.

The function appears to work if the record being searched for is the first
in the list and therefore is found by the .Find statement......
The function does not work when the .FindNext statement is needed for the
records that are second or further on the find list.....

Thanks
Scott

"Jim Cone" wrote:


How are you calling the function and are the values assigned
to "strClientNumber" and "strContactType" the same as those
used in the sub?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - Find and list multiple items from multiple sheets)




"scott56hannah"
wrote in message
Hi,
I have developed a find function to find a value and then based on other
values beside it populate a cell. The find routine does not seem to work when
implementing FindNext.....I need to use FindNext as there may be many values
in the range it is searching and only one of them will match based on the
other criteria.

I have coded it in a sub procedure and the FindNext statement cycles through
the search correctly. What am I doing wrong ?

Here are the two (2) sets of code ....the first one is the function

Function FindContactName(strClientNumber As String, strContactType As
String) As String
'This function will search the existing Client details and populate the
Client contact details
'for the type of contact requested (initially these were Billing and Business

Dim R As Range

With Worksheets("Contact Details").Range("A5:A55")

Set R = .Find(strClientNumber, LookIn:=xlValues)

If R Is Nothing Then
FindContactName = "No Contact Details found"

ElseIf Not R Is Nothing Then
FirstAddress = R.Address

Do
MsgBox "Row " & R.Cells.Row & " Column " & R.Cells.Column

'Check the Contact type matches and continue to search if it
is not found
If R.Offset(0, 1).Value = strContactType Then
FindContactName = R.Offset(0, 2).Value & " " &
R.Offset(0, 3).Value
Exit Function
Else
Set R = .FindNext(R)
End If

Loop While Not R Is Nothing And FirstAddress < R.Address

End If

End With

'Clear Memory
Set R = Nothing

End Function


Sub TestSearch()
'Function FindContactName(strClientNumber As String, strContactType As
String) As String
'This function will search the existing Client details and populate the
Client contact details
'for the type of contact requested (initially these were Billing and Business

Dim R As Range
Dim FindContactName As String

With Worksheets("Contact Details").Range("A5:A55")

Set R = .Find("C-100650", LookIn:=xlValues)

If R Is Nothing Then
FindContactName = "No Contact Details found"

ElseIf Not R Is Nothing Then
FirstAddress = R.Address

Do
MsgBox "Row " & R.Cells.Row & " Column " & R.Cells.Column

'Check the Contact type matches and continue to search if it
is not found
If R.Offset(0, 1).Value = "Billing" Then
FindContactName = R.Offset(0, 2).Value & " " &
R.Offset(0, 3).Value
Exit Sub
Else
Set R = .FindNext(R)
End If

Loop While Not R Is Nothing And FirstAddress < R.Address

End If

End With

'Clear Memory
Set R = Nothing

End Sub