Why doesn't the FindNext work inside a function ?
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
|