Formule de "suites"

Function Suite(xWhat As Range, xIn As Range, xTail As Long) As Long
Dim tWhat, tIn, i As Long, j As Long, k As Long

tWhat = xWhat.Value
tIn = xIn.Value
Suite = 0

For i = 1 To UBound(tWhat, 2) - xTail + 1
  For j = 1 To UBound(tIn, 2) - xTail + 1
    For k = 1 To xTail
      If tWhat(1, i + k - 1) <> tIn(1, j + k - 1) Then Exit For
    Next k
    If k = xTail + 1 Then
      Suite = Suite + 1
      j = j + xTail - 1
      i = i + xTail - 1
    End If
  Next j
Next i
End Function

=SI(suite($Y2:$AG2;$AV$1:$BZ$1;4)>=1;0;SI(suite($Y2:$AG2;$AV$1:$BZ$1;3)=2;0;1))

Function Suite_bis(xWhat As Range, xIn As Range, xTail As Long) As Long
' Nombre de suites de xtail après avoir ôté toutes les suites
' de taille supérieure

Dim tWhat, tIn, i As Long, j As Long, k As Long, n As Long, m As Long

tWhat = xWhat.Value
tIn = xIn.Value
If xTail < 1 Then Exit Function
Suite_bis = 0

' on ôte les suites de taille supérieure ou égale à xtail+1
For n = UBound(tWhat, 2) To xTail + 1 Step -1
  For i = 1 To UBound(tWhat, 2) - n + 1
    For j = 1 To UBound(tIn, 2) - n + 1
      For k = 1 To n
        If tWhat(1, i + k - 1) <> tIn(1, j + k - 1) Then Exit For
      Next k
      If k = n + 1 Then
        For m = i To i + n - 1
          tWhat(1, m) = Chr(135)
        Next m
        j = j + n - 1
        i = i + n - 1
      End If
    Next j
  Next i
Next n

' on calcule le nombre de suite de xTail
Suite_bis = 0
For i = 1 To UBound(tWhat, 2) - xTail + 1
  For j = 1 To UBound(tIn, 2) - xTail + 1
    For k = 1 To xTail
      If tWhat(1, i + k - 1) <> tIn(1, j + k - 1) Then Exit For
    Next k
    If k = xTail + 1 Then
      Suite_bis = Suite_bis + 1
      j = j + xTail - 1
      i = i + xTail - 1
    End If
  Next j
Next i
End Function