XL 2010 RESOLU : FormulaR1C1 avec une variable (valeur dans une cellule)

ActiveCell.FormulaR1C1 = "=SUM(R" & y1 & "C:R[-2]C)"

Sub Macro1()
Dim LDép As Long, Cel As Range
LDép = 3
Set Cel = ActiveSheet.Columns("C").Find(What:="MONTANT TOTAL HT", _
   LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
   SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Do While Cel.Row > LDép
    Cel.Offset(, 4).FormulaR1C1 = "=SUM(R" & LDép & "C:R[-2]C)"
    LDép = Cel.Row + 4
    Columns("C").FindNext: Loop
End Sub

Sub Macro1()
Dim LDép As Long, LRub As Long, Cel As Range
LDép = 2: LRub = 2: Set Cel = ActiveSheet.[C2]
Do
   Set Cel = ActiveSheet.Columns("C").Find(What:="MONTANT", After:=Cel, _
      LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
      SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
   If Cel.Row < LRub Then Exit Sub
   If Cel.Value Like "MONTANT TOTAL *" Then
      Cel.Offset(, 4).FormulaR1C1 = "=SUBTOTAL(9,R" & LDép & "C:R[-1]C)"
      If Cel.Value = "MONTANT TOTAL TTC" Then LDép = Cel.Row + 2: LRub = LDép
   Else
      Cel.Offset(, 4).FormulaR1C1 = "=SUBTOTAL(9,R" & LRub & "C:R[-1]C)"
      LRub = Cel.Row + 1
      End If: Loop
End Sub