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