I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

CountStringCombi
20090809_PB_02_CountStringCombi

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

Sulprobil   Get it done   Contact   Disclaimer   Download