Solved: Problem in Find & FindNext for a Function call

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

[VBA]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
[/VBA]

Code for the function that is not working

[VBA]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[/VBA]

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

Scott

Have you tried

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

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

ACK! dbl post, sorry.

Quote:

---

Originally Posted by scott56

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/Writin...ionsInVBA.aspx

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


Mark

Quote:

---

Originally Posted by scott56

... 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.

I'll give those a go and see if they can acheive what I am after...

Scott

How about
[VBA]
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

[/VBA]

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

Hi Scott,
Can you repost your sample showing expected results?

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

[VBA]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[/VBA]