I've revised my code a little. if you like, try this one.

Function FindContactName(sR As Range, strClientNumber As String, _
strContactType As String) As String
Dim R As Range
Application.Volatile
On Error GoTo ex:
With Worksheets("Contact Details").Columns(1) 'Change here to appropriate
Set R = .Find(strClientNumber, after:=sR, LookIn:=xlValues)
If R Is Nothing Then
FindContactName = strClientNumber & " Not found"
ElseIf Not R Is Nothing Then
If R.Row <= sR.Row Then
FindContactName = strContactType & " Not found"
Exit Function
ElseIf R.Offset(0, 1).Value = strContactType Then
FindContactName = R.Offset(0, 2).Value & " " _
& R.Offset(0, 3).Value
Exit Function
Else
FindContactName = FindContactName(R, _
strClientNumber, strContactType)
End If
End If
End With
Exit Function
ex:
If Err.Number = 9 Then
FindContactName = "Worksheet ""Contact Details""" _
& "Not found"
Else
FindContactName = "Unknown Error"
End If
End Function

keiji

"scott56hannah" wrote in message
...
Thanks....will try this...

"kounoike" wrote:

I don't know why but it's seems like not to be able to use the find
method
twice in the same function. so, i made your function recursive and tested
a
little. it seems to work for me but not sure for you.

i put it in a cell like this in worksheet other than 'Contact Details'
=FindContactName('Contact Details'!A1,"C-100650","Billing")
if you put it in worksheets('Contact Details'),
=FindContactName(A1,"C-100650","Billing") would do.

Function FindContactName(sR As Range, strClientNumber As String,
strContactType As String) As String
Dim R As Range
With Worksheets("Contact Details").Columns(1) 'Change here to
appropriate
range
Set R = .Find(strClientNumber, after:=sR, LookIn:=xlValues)
If R Is Nothing Then
FindContactName = "No Contact Details found"
ElseIf Not R Is Nothing Then
If R.Offset(0, 1).Value = strContactType Then
FindContactName = R.Offset(0, 2).Value & " " & R.Offset(0,
3).Value
Exit Function
Else
FindContactName = FindContactName(R, strClientNumber,
strContactType)
End If
End If
End With
End Function

keiji

"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