Dim list As Range 'mémorise la variable
Function RechercheSuite()
Dim c As Range, col As Variant, i&, P As Range, T As Range, n&
Application.Volatile
Set c = Application.Caller.Offset(, -1) 'cellule à gauche
If list Is Nothing Then Set list = [liste] 'variable déterminée une seule fois
col = Application.Match(c, list, 0)
If IsError(col) Then RechercheSuite = "": Exit Function
'---traitement des tableaux---
On Error Resume Next
For i = 1 To 99
Set P = Nothing 'RAZ
Set P = Range("Table" & Format(i, "00"))
If Not P Is Nothing Then
If Not Intersect(c, P) Is Nothing Then Set T = P: Exit For
n = n + Application.CountIf(P, c)
End If
Next
On Error GoTo 0
'---traitement du dernier tableau étudié---
n = n + Application.CountIf(T.Resize(c.Row - T.Row + 1), c)
RechercheSuite = list(n + 1, col)
End Function