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