Calcul somme
a
Bonjour
j'aimerais calculer la somme par mois des montant indemnité principale et l'afficher dans la feuille 2 mais je n'y arrive pas
j'ai fais un code mais sa ne marche pas trop (sa ne me renvoie pas ceux que je veux)
j'aimerais avoir les valeur du TCD (feuille 3, nov, dec , jan, fev)
Sub MONTANT_INDEMNITE_PRINCIPALE()
Dim Date_Souscription_Adhésion As Range, Statut_Technique_Sinistre As Range
Dim Montant_Ind_Principale As Range
Dim dernligne As Long
Dim i As Integer
Dim s As String
Dim montant As String
Dim tt11, tt12, tt1, tt2 As Double
With Worksheets("Feuil1")
dernligne = ThisWorkbook.Sheets("Feuil1").Range("A" & .Rows.Count).End(xlUp).Row
Set Date_Souscription_Adhésion = ThisWorkbook.Sheets("Feuil1").Range("B2:B" & dernligne)
Set Montant_Ind_Principale = ThisWorkbook.Sheets("Feuil1").Range("E2:E" & dernligne)
Set Statut_Technique_Sinistre = ThisWorkbook.Sheets("Feuil1").Range("D2:D" & dernligne)
End With
For i = 2 To dernligne
s = Cells(i, 4).Value
montant = Cells(i, 5).Value
If s <> "Terminé - Refusé après instruction" Then
If Month(Cells(i, 2).Value) = 11 And Year(Cells(i, 2).Value) = 2015 Then
tt11 = tt11 + CInt(montant)
Sheets("Feuil2").Cells(i, 2).Value = tt11
End If
If Month(Cells(i, 2).Value) = 12 And Year(Cells(i, 2).Value) = 2015 Then
tt12 = tt12 + CInt(montant)
Sheets("Feuil2").Cells(i, 2).Value = tt12
End If
If Month(Cells(i, 2).Value) = 1 And Year(Cells(i, 2).Value) = 2016 Then
tt1 = tt1 + CInt(montant)
Sheets("Feuil2").Cells(i, 2).Value = tt1
End If
If Month(Cells(i, 2).Value) = 2 And Year(Cells(i, 2).Value) = 2016 Then
tt2 = tt2 + CInt(montant)
Sheets("Feuil2").Cells(i, 2).Value = tt2
End If
End If
Next i
End Submerci pour votre aide
Bonjour le forum,
aude21 a écrit :j'aimerais calculer la somme par mois des montant indemnité principale et l'afficher dans la feuille 2
Avec une simple formule ?
E1 :
=SOMMEPROD((Feuil1!B2:B1000>=D1)*((Feuil1!B2:B1000<=FIN.MOIS(D1;0)))*(Feuil1!E2:E1000))
a
mercii pour ton aide
je dois faire une macro pour obtenir ces valeur la
nov 652,3
déc 1111,2
janv 3681,64
févr 985,6
Avec la même présentation ;
=SOMMEPROD((Feuil1!B2:B1000>=D1)*((Feuil1!B2:B1000<=FIN.MOIS(D1;0)))*(Feuil1!E2:E1000))-SOMMEPROD((Feuil1!B2:B1000>=D1)*((Feuil1!B2:B1000<=FIN.MOIS(D1;0)))*(Feuil1!D2:D1000="Terminé - Refusé après instruction")*(Feuil1!E2:E1000))a
mais comment l’écrire en vba
a
on ne peut pas le faire en vba?
t
bonjour
pourquoi ? ce n'est pas assez chic ?
cordialement
a
mdrrrrr non c'est juste que mon tuteur veut que je le fasse en vba
voila ce code marche pour ceux qui en ont besoin
Sub MONTANT_INDEMNITE_PRINCIPALE()
Dim Date_Souscription_Adhésion As Range, Statut_Technique_Sinistre As Range
Dim Montant_Ind_Principale As Range
Dim dernligne As Long
Dim i As Integer
Dim sa As String
Dim montant As String
Dim tt1, tt2, tt3, tt4, tt5, tt6, tt7, tt8, tt9, tt10, tt11, tt12 As Double
Dim tt13, tt14, tt15, tt16, tt17, tt18, tt19, tt20, tt21, tt22, tt23, tt24 As Double
Dim tt25, tt26, tt27, tt28, tt29, tt30, tt31, tt32, tt33, tt34, tt35, tt36 As Double
Dim s As Double
Dim Mois As Integer
With Worksheets("Feuil1")
dernligne = .Range("A" & Rows.Count).End(xlUp).Row
Set Date_Souscription_Adhésion = .Range("B2:B" & dernligne)
Set Montant_Ind_Principale = .Range("E2:E" & dernligne)
Set Statut_Technique_Sinistre = .Range("D2:D" & dernligne)
End With
i = 2
For i = 2 To dernligne
sa = Cells(i, 4)
montant = Cells(i, 5)
Mois = Month(Cells(i, 2))
If sa <> "Terminé - Refusé après instruction" Then
If Year(Cells(i, 2).Value = 2015) Then
Select Case Mois
Case Is = 1
tt1 = tt1 + CInt(montant)
Case Is = 2
tt2 = tt2 + CInt(montant)
Case Is = 3
tt3 = tt3 + CInt(montant)
Case Is = 4
tt4 = tt4 + CInt(montant)
Case Is = 5
tt5 = tt5 + CInt(montant)
Case Is = 6
tt6 = tt6 + CInt(montant)
Case Is = 7
tt7 = tt7 + CInt(montant)
Case Is = 8
tt8 = tt8 + CInt(montant)
Case Is = 9
tt9 = tt9 + CInt(montant)
Case Is = 10
tt10 = tt10 + CInt(montant)
Case Is = 11
tt11 = tt11 + CInt(montant)
Case Is = 12
tt12 = tt12 + CInt(montant)
End Select
End If
If Year(Cells(i, 2).Value) = 2016 Then
Select Case Mois
Case Is = 13
tt13 = tt13 + CInt(montant)
Case Is = 14
tt14 = tt14 + CInt(montant)
Case Is = 15
tt15 = tt15 + CInt(montant)
Case Is = 16
tt16 = tt16 + CInt(montant)
Case Is = 17
tt17 = tt17 + CInt(montant)
Case Is = 18
tt18 = tt18 + CInt(montant)
Case Is = 19
tt19 = tt19 + CInt(montant)
Case Is = 20
tt20 = tt20 + CInt(montant)
Case Is = 21
tt21 = tt21 + CInt(montant)
Case Is = 22
tt22 = tt22 + CInt(montant)
Case Is = 23
tt23 = tt23 + CInt(montant)
Case Is = 24
tt24 = tt24 + CInt(montant)
End Select
End If
If Year(Cells(i, 2).Value) = 2017 Then
Select Case Mois
Case Is = 25
tt25 = tt25 + CInt(montant)
Case Is = 26
tt26 = tt26 + CInt(montant)
Case Is = 27
tt27 = tt27 + CInt(montant)
Case Is = 28
tt28 = tt28 + CInt(montant)
Case Is = 29
tt29 = tt29 + CInt(montant)
Case Is = 30
tt30 = tt30 + CInt(montant)
Case Is = 31
tt31 = tt31 + CInt(montant)
Case Is = 32
tt32 = tt32 + CInt(montant)
Case Is = 33
tt33 = tt33 + CInt(montant)
Case Is = 34
tt34 = tt34 + CInt(montant)
Case Is = 35
tt35 = tt35 + CInt(montant)
Case Is = 36
tt36 = tt36 + CInt(montant)
End Select
End If
End If
Sheets("Feuil2").Cells(1, 2).Value = "janvier 2015"
Sheets("Feuil2").Cells(1, 3).Value = tt1
Sheets("Feuil2").Cells(2, 2).Value = "fevrier 2015"
Sheets("Feuil2").Cells(2, 3).Value = tt2
Sheets("Feuil2").Cells(3, 2).Value = "mars 2015"
Sheets("Feuil2").Cells(3, 3).Value = tt3
Sheets("Feuil2").Cells(4, 2).Value = "avril 2015"
Sheets("Feuil2").Cells(4, 3).Value = tt4
Sheets("Feuil2").Cells(5, 2).Value = "mai 2015"
Sheets("Feuil2").Cells(5, 3).Value = tt5
Sheets("Feuil2").Cells(6, 2).Value = "juin 2015"
Sheets("Feuil2").Cells(6, 3).Value = tt6
Sheets("Feuil2").Cells(7, 2).Value = "juil 2015"
Sheets("Feuil2").Cells(7, 3).Value = tt7
Sheets("Feuil2").Cells(8, 2).Value = "aout 2015"
Sheets("Feuil2").Cells(8, 3).Value = tt8
Sheets("Feuil2").Cells(9, 2).Value = "sept 2015"
Sheets("Feuil2").Cells(9, 3).Value = tt9
Sheets("Feuil2").Cells(10, 2).Value = "oct 2015"
Sheets("Feuil2").Cells(10, 3).Value = tt10
Sheets("Feuil2").Cells(11, 2).Value = "nov 2015"
Sheets("Feuil2").Cells(11, 3).Value = tt11
Sheets("Feuil2").Cells(12, 2).Value = "dec 2015"
Sheets("Feuil2").Cells(12, 3).Value = tt12
Sheets("Feuil2").Cells(13, 2).Value = "janvier 2016"
Sheets("Feuil2").Cells(13, 3).Value = tt13
Sheets("Feuil2").Cells(14, 2).Value = "fevrier 2016"
Sheets("Feuil2").Cells(14, 3).Value = tt14
Sheets("Feuil2").Cells(15, 2).Value = "mars 2016"
Sheets("Feuil2").Cells(15, 3).Value = tt15
Sheets("Feuil2").Cells(16, 2).Value = "avril 2016"
Sheets("Feuil2").Cells(16, 3).Value = tt16
Sheets("Feuil2").Cells(17, 2).Value = "mai 2016"
Sheets("Feuil2").Cells(17, 3).Value = tt17
Sheets("Feuil2").Cells(18, 2).Value = "juin 2016"
Sheets("Feuil2").Cells(18, 3).Value = tt18
Sheets("Feuil2").Cells(19, 2).Value = "juil 2016"
Sheets("Feuil2").Cells(19, 3).Value = tt19
Sheets("Feuil2").Cells(20, 2).Value = "aout 2016"
Sheets("Feuil2").Cells(20, 3).Value = tt20
Sheets("Feuil2").Cells(21, 2).Value = "sept 2016"
Sheets("Feuil2").Cells(21, 3).Value = tt21
Sheets("Feuil2").Cells(22, 2).Value = "oct 2016"
Sheets("Feuil2").Cells(22, 3).Value = tt22
Sheets("Feuil2").Cells(23, 2).Value = "nov 2016"
Sheets("Feuil2").Cells(23, 3).Value = tt23
Sheets("Feuil2").Cells(24, 2).Value = "dec 2016"
Sheets("Feuil2").Cells(24, 3).Value = tt24
'refaire la mm chose pour 2017
Next i
End Sub