PDA

View Full Version : Solved: Problem in Find & FindNext for a Function call



scott56
06-06-2010, 11:30 PM
Hi,

I am trying to implement Find and FindNext to search through a set of data and discover how many occurrences of a string exist in that set.

I can get this working via a Sub routine with no parameters and the correct number of occurrences is returned...

I cannot get it to work via a Sub routine with parameters or via a Function which is really the way I want to get it working...

Any help appreciated...

Code for the working Sub routine

Sub MatchValue()
intCountItem = 0
strFieldValue_Constant = "678934"

With Worksheets("Sheet1").Range("a3:d20")

Set c = .Find(strFieldValue_Constant, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
intCountItem = intCountItem + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If

End With

MsgBox "Count of - " & strFieldValue_Constant & " => " & intCountItem

End Sub


Code for the function that is not working

Function lngNumberFound(strFieldValue As String) As Long
lngNumberFound = 0

With Worksheets("Sheet1").Range("a3:d20")

Set c = .Find(strFieldValue, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
lngNumberFound = lngNumberFound + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If

End With

End Function

I also attach the sample file I am using to test this...

Scott

mikerickson
06-06-2010, 11:43 PM
Have you tried

MsgBox Application.WorksheetFunction.CountIf(Worksheets("Sheet1").Range("a3:d20"), "*" & strFieldValue_Constant & "*")

scott56
06-07-2010, 02:15 AM
Mike,

I really need to use something like the Find and FindNext functions as in addition to performing the counts of the occurrence for the string I am also adding up a value that is next to the string based on it being found....

So I don't think I can use the countif function for this....

Scott

GTO
06-07-2010, 03:50 AM
ACK! dbl post, sorry.

GTO
06-07-2010, 03:59 AM
Mike,

I really need to use something like the Find and FindNext functions as in addition to performing the counts of the occurrence for the string I am also adding up a value that is next to the string based on it being found....

So I don't think I can use the countif function for this....

Scott

I may not be understanding, but shouldn't you be able to make the return contingent upon COUNTIF returning >0 ?

According to what little I found Bing'ing, .Find cannot be used in a UDF until 2002.

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Also found a couple of threads indicating that .FindNext cannot be used.


Mark

mikerickson
06-07-2010, 06:23 AM
... I am also adding up a value that is next to the string based on it being found....

So I don't think I can use the countif function for this....

A SUMIF, SUMPRODUCT or SUMIFS might suffice.

As mentioned above .Find and .FindNext don't work when a UDF is called from a worksheet (neither does .SpecialCells).

A looping UDF might be one approach, but (since you are working on a fixed range), a solution using native Excel would allow the use of a Named Function.

scott56
06-07-2010, 01:21 PM
I'll give those a go and see if they can acheive what I am after...

Scott

mdmackillop
06-07-2010, 01:52 PM
How about

Function lngNumberFound(strFieldValue As String) As Long
lngNumberFound = 0

With Worksheets("Sheet1").Range("a3:d20")

Set c = .Find(strFieldValue, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
lngNumberFound = lngNumberFound + 1
Set c = .Find(c, after:=c)
Loop While c.Address <> firstaddress
End If

End With

End Function

scott56
06-07-2010, 04:22 PM
thanks for the help....I tried both SUMPRODUCT and the latest suggestion on re-using FIND.

Both of them have been able to solve this problem for me...

Scott

mdmackillop
06-07-2010, 11:48 PM
Hi Scott,
Can you repost your sample showing expected results?

scott56
06-09-2010, 02:24 AM
Here are the finished samples for the SUMPRODUCT solution and also the repeated use of FIND...thanks to those that helped with this

SUMPRODUCT

=SUMPRODUCT(-(Sheet1!$B$4:$B$20=$B5),-(Sheet1!$A$4:$A$20=D$3),Sheet1!$D$4:$D$20)

Use of FIND

Function lngNumberFound(strFieldValue As String) As Long
lngNumberFound = 0

With Worksheets("Sheet1").Range("a3:d20")

Set c = .Find(strFieldValue, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
lngNumberFound = lngNumberFound + 1
Set c = .Find(c, After:=c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If

End With

End Function