Why doesn't the FindNext work inside a function ?

[scott56hannah]

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

 

[Jim Cone]

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

 

[scott56hannah]

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

 

[JMB]

I worked fine for me when the item matching all of the criteria was last in
the list with the following tweak:

If UCase(R.Offset(0, 1).Value) = "BILLING" Then

 

[JMB]

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

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

 

[scott56hannah]

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]

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'!D555,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).

 

[kounoike]

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]

Thanks....will try this...

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]

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

 

[kounoike]

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