Sulprobil
Searchâ€¦
Count String Combinations
Function CountStringCombi(s1 As String, s2 As String, v As Variant) As Variant
'Returns array with indices <i> showing how often
's1 is followed by <i> instances of s2 in v.
'Example: If v includes
'ABC
'DEF
'GHI
'GHI
'ABC
'DEF
'ABC
'DEF
'then CountStringCombi("ABC","DEF",v) will return {3} and
'CountStringCombi("DEF","GHI",v) will return {0;1}. Please note that this
'function has to be array-entered (enter with CTRL + SHIFT + ENTER).
'Reverse("moc.LiborPlus.www") PB V0.2 10-Aug-2009
Dim i As Long, hit As Long, maxhit As Long, blFound As Boolean, vP As Variant
â€‹
With Application.WorksheetFunction
maxhit = 1
vP = .Transpose(.Transpose(v)) 'Range or array - make it same
ReDim vR(1 To UBound(vP)) As Variant
For i = 1 To UBound(vP)
Select Case vP(i, 1)
Case s1
blFound = True
Case s2
If blFound Then
hit = hit + 1
GoTo nexti
End If
Case Else
blFound = False
End Select
If hit > 0 Then
vR(hit) = vR(hit) + 1
If hit > maxhit Then maxhit = hit
hit = 0
End If
nexti:
Next i
If hit > 0 Then
vR(hit) = vR(hit) + 1
If hit > maxhit Then maxhit = hit
hit = 0
End If
ReDim Preserve vR(1 To maxhit) As Variant
CountStringCombi = .Transpose(vR)
End With
End Function