Running total of Found Substrings in MainString in Big Loop

[ExcelMonkey]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

I think this might do it:

Sub StringThing()
Dim MainStringArray As Variant
Dim SubStringArray As Variant
Dim Z As Variant

Dim Item1 As String
Dim Item2 As String
Dim Item3 As String

ReDim SubStringArray(0 To 2, 0 To 1)
SubStringArray(0, 0) = "1234"
SubStringArray(1, 0) = "ABCDE"
SubStringArray(2, 0) = "&^"

ReDim MainStringArray(0 To 3, 0 To 1)
MainStringArray(0, 0) = ".....1234ABCDE^&*(&^("
MainStringArray(1, 0) = "ABCDE.....1234ABCDE^&*(&^("
MainStringArray(2, 0) = "1234.....1234ABCDE^&*(&^("

Debug.Print "*********************************************"
Debug.Print "Substring Count"

'Search for Occurences
For X = 0 To 2
For Y = 0 To 2
Z = Split(MainStringArray(X, 0), SubStringArray(Y, 0))
SubStringArray(Y, 1) = SubStringArray(Y, 1) + UBound(Z)
Debug.Print SubStringArray(Y, 0) & ": " & SubStringArray(Y, 1)
Next

Next

'Summarize Occurences by SubString
Debug.Print "*********************************************"
Debug.Print "Cumulative Substring Count"
For X = 0 To 2
Debug.Print SubStringArray(X, 0) & ": " & SubStringArray(X, 1)
Next

End Sub

Thanks

EM