application.sum en VBAFermé

Question

Bonjour
je veux faire des calcules somme avec macro, j'ai pu faire des calcules comme ça:
Sub cal()

        With ActiveSheet
        .Range("A35").Value = "TOTAL:"
        End With
        
        With ActiveSheet
           .Range("G35").Value = Application.Sum([G6:G34])
           .Range("H35").Value = Application.Sum([H6:H34])
           .Range("I35").Value = Application.Sum([I6:I34])
           .Range("K35").Value = Application.Sum([K6:K34])
           .Range("L35").Value = Application.Sum([L6:L34])
           .Range("M35").Value = Application.Sum([M6:M34])
           .Range("N35").Value = Application.Sum([N6:N34])
           .Range("O35").Value = Application.Sum([O6:O34])
           .Range("P35").Value = Application.Sum([P6:P34])
           .Range("Q35").Value = Application.Sum([Q6:Q34])
           .Range("R35").Value = Application.Sum([R6:R34])
        End With
      
        With ActiveSheet
           .Range("L6").Value = Application.Sum([G6:K6])
           .Range("L7").Value = Application.Sum([G7:K7])
           .Range("L8").Value = Application.Sum([G8:K8])
           .Range("L9").Value = Application.Sum([G9:K9])
           .Range("L10").Value = Application.Sum([G10:K10])
           .Range("L11").Value = Application.Sum([G11:K11])
           .Range("L12").Value = Application.Sum([G12:K12])
      'jusqu'à ("L34) etc.... pour M7:M34  ET N O P Q R.....
        
        End With
     
End Sub

mon probléme que cela est très long,si je veux le réduire comme on peux faire!
voilà un fichier en pièce jointe:

 https://www.cjoint.com/c/ILdsNMkUyvh

M-12 · Answer

Bonsoir,
Au plus simple
Sub cal()
        With ActiveSheet
          .Range("A35").Value = "TOTAL:"
          .Range("G35:R35").Select
          Selection.FormulaR1C1 = "=SUM(R[-28]C:R[-1]C)"
          .Range("L6:L34").Select
          Selection.FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
        End With
     Range("C5").Select
End Sub

Application.sum en VBA

1 réponse

Newsletters