Solved: Problem in Find & FindNext for a Function call

[scott56]

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

[mikerickson]

Have you tried

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

[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

[GTO]

ACK! dbl post, sorry.

[GTO]

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

[mikerickson]

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

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

Scott

[mdmackillop]

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]

[scott56]

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]

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

[scott56]

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]