Insérer une formule dans les cellules d'un tableau en VBA

If cell.Text = "T1" Then
'---
If cell.Text = "T2" Then

Sub Formule()
Dim cell As Range, DerLigne As Byte
For Each cell In Sheets("Sheet1").Range("I4:I50")
    If cell.Value Like "T?" Then
        
        DerLigne = cell.Offset(0, -6).End(xlDown).Row
        
        cell.Offset(1, 0).Resize(DerLigne - cell.Row).FormulaR1C1 = _
            "=INDEX(R" & cell.Row + 1 & "C3:R" & DerLigne & _
            "C4,MATCH(ROWS(R" & cell.Row + 1 & "C:RC)-1,R" & _
            cell.Row + 1 & "C[-7]:R" & DerLigne & "C[-7],0),1)"
            
        cell.Offset(1, 1).Resize(DerLigne - cell.Row).FormulaR1C1 = _
            "=INDEX(R" & cell.Row + 1 & "C3:R" & DerLigne & _
            "C4,MATCH(ROWS(R" & cell.Row + 1 & "C[-1]:RC[-1])-1,R" _
            & cell.Row + 1 & "C[-8]:R" & DerLigne & "C[-8],0),2)"
    End If
Next
End Sub

Sub Formule()
Dim a As Range, ad$, F$
For Each a In [B:B].SpecialCells(xlCellTypeFormulas, 1).Areas
  ad = a.Resize(, 3).Address(ReferenceStyle:=xlR1C1)
  F = "=VLOOKUP(ROWS(R1:R[" & 1 - a.Row & "])-1," & ad & ",COLUMNS(C9:C[1]),0)"
  Intersect(a.EntireRow, [I:J]).FormulaR1C1 = F
Next
End Sub

Sub Classement()
Dim a As Range, plage As Range
For Each a In [D:D].SpecialCells(xlCellTypeFormulas, 1).Areas
  Set plage = a.Offset(, -1).Resize(, 2)
  With plage.Offset(, 6)
    .Value = plage.Value 'copie les valeurs
    .Sort [J1] 'tri
  End With
Next
End Sub

Sub Classement()
Dim a As Range, plage As Range
Application.ScreenUpdating = False
[I:J].Clear 'efface tout
For Each a In [D:D].SpecialCells(xlCellTypeFormulas, 1).Areas
  Set plage = a.Offset(, -1).Resize(, 2)
  With plage.Offset(, 6)
    .Value = plage.Value 'copie les valeurs
    .Sort [J1] 'tri
    '---mises en formes---
    .HorizontalAlignment = xlCenter
    .Columns(1).Interior.ColorIndex = 37
    .Borders.LineStyle = 1
    With .Offset(-2).Resize(2)
      .HorizontalAlignment = xlCenter
      .Interior.ColorIndex = 15
      .Borders.LineStyle = 1
      .Rows(1).Merge
      .Cells(1, 1) = "Classement"
      .Cells(2, 1) = plage(0, 1)
      .Cells(2, 2) = "Qte"
    End With
  End With
Next
End Sub