I am searching text strings. I have a loop which assesses sub strings within
a string. The search is looking for about 300 defined strings. I want the
ability to count the occurences of each substring during the entire loop.
However as there are 300 of them, I am not sure how to approach this.
Normally I would do the following below (very crude example). However as you
can see I would need 300 Counter variables for the 300 defined terms.
Furthermore, if mulitple occurences are found in each string my simple +1
logic will not work. I know I can also use regex for the string search
(could make pattern = (item1|itme2|item3 etc) and track multiple occurences,
but need some guidance on how to calc the running totals on these 300
separate items without creating 300 separate counter variables.

Is there a way to do this using class modules?

Dim Item1 as String
Dim Item2 as String
Dim MainStringArray As Variant()

Item1 = "abcd"
'Just assume I have loaded my aray with strings
'?MainStringArray(0) =" .........1234abcde@@@@@@@ABCDE"

Item1Counter = 0

For X = 1 to 1000
'Test to see if substring exists in MainString
IF substringexists(Item1, MainStringArray(X-1))
Item1Counter=Item1Counter +1
End if
Next

Sub substringexists() as Boolean
'Code to search mainstring
'If match substringexists = TRUE
'End If
End Sub

Thanks

EM

Consider using an array for your ItemCounter and dimension it to the number
of defined strings you are testing for (I think I would Dim it with a lower
bound of 1). Then change your substringexists subroutine to a function and
have it return an index value for the found defined string and let it return
0 for a failure to find any of the defined strings. That way, if the
function returns a number greater than 0, you can use that number of the
index of the ItemCounter array.

Rick


"ExcelMonkey" wrote in message
...
I am searching text strings. I have a loop which assesses sub strings
within
a string. The search is looking for about 300 defined strings. I want
the
ability to count the occurences of each substring during the entire loop.
However as there are 300 of them, I am not sure how to approach this.
Normally I would do the following below (very crude example). However as
you
can see I would need 300 Counter variables for the 300 defined terms.
Furthermore, if mulitple occurences are found in each string my simple +1
logic will not work. I know I can also use regex for the string search
(could make pattern = (item1|itme2|item3 etc) and track multiple
occurences,
but need some guidance on how to calc the running totals on these 300
separate items without creating 300 separate counter variables.

Is there a way to do this using class modules?

Dim Item1 as String
Dim Item2 as String
Dim MainStringArray As Variant()

Item1 = "abcd"
'Just assume I have loaded my aray with strings
'?MainStringArray(0) =" .........1234abcde@@@@@@@ABCDE"

Item1Counter = 0

For X = 1 to 1000
'Test to see if substring exists in MainString
IF substringexists(Item1, MainStringArray(X-1))
Item1Counter=Item1Counter +1
End if
Next

Sub substringexists() as Boolean
'Code to search mainstring
'If match substringexists = TRUE
'End If
End Sub

Thanks

EM