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