Sulprobil
Search…
Count String Combinations
Please read my Disclaimer.
1
Function CountStringCombi(s1 As String, s2 As String, v As Variant) As Variant
2
'Returns array with indices <i> showing how often
3
's1 is followed by <i> instances of s2 in v.
4
'Example: If v includes
5
'ABC
6
'DEF
7
'GHI
8
'GHI
9
'ABC
10
'DEF
11
'ABC
12
'DEF
13
'then CountStringCombi("ABC","DEF",v) will return {3} and
14
'CountStringCombi("DEF","GHI",v) will return {0;1}. Please note that this
15
'function has to be array-entered (enter with CTRL + SHIFT + ENTER).
16
'Reverse("moc.LiborPlus.www") PB V0.2 10-Aug-2009
17
Dim i As Long, hit As Long, maxhit As Long, blFound As Boolean, vP As Variant
18
19
With Application.WorksheetFunction
20
maxhit = 1
21
vP = .Transpose(.Transpose(v)) 'Range or array - make it same
22
ReDim vR(1 To UBound(vP)) As Variant
23
For i = 1 To UBound(vP)
24
Select Case vP(i, 1)
25
Case s1
26
blFound = True
27
Case s2
28
If blFound Then
29
hit = hit + 1
30
GoTo nexti
31
End If
32
Case Else
33
blFound = False
34
End Select
35
If hit > 0 Then
36
vR(hit) = vR(hit) + 1
37
If hit > maxhit Then maxhit = hit
38
hit = 0
39
End If
40
nexti:
41
Next i
42
If hit > 0 Then
43
vR(hit) = vR(hit) + 1
44
If hit > maxhit Then maxhit = hit
45
hit = 0
46
End If
47
ReDim Preserve vR(1 To maxhit) As Variant
48
CountStringCombi = .Transpose(vR)
49
End With
50
End Function
Copied!
Last modified 1yr ago
Copy link