L'utilisation d'une macro dans une autre
M
Bonjour le forum,
J'ai utilisé une macro pour me faire une tâche.
Ensuite, j'ai utilisé cette macro dans une boucle for pour me faire la même chose pour tous les clients.
Ps: la boucle for est faite sur les codes de mes clients, à chaque fois, macro s'exécute sur un code et m'affiche le résultat relatif.
L'exécution est devenue très très lente d'ailleurs Il me s'affiche "Ne réponds pas"
est-il possible juste de faire appel à la macro initiale sans rédiger son code ?
Si oui comment tenir en compte ma boucle?
Est-ce que cela aide à l'optimisation et à l'exécution rapide de mon code ?
Merci.
o
Pour cela il faudrait que tu mettes le code ou mieux, le fichier
M
Bonjour ouisansdoute
le fichier est énorme même le code aussi car étant débutant, j'ai utilisé l'enregistreur de macro pour tout faire
Donc je veux réduire un peu la charge de cette manière:
Au lieu d'exécuter tout un code à chaque fois, je le mets dans une macro à part dans un module,et je fais juste appel à ma macro à chaque fois dans ma boucle dans un autre module.
Je sais pas, si ça optimise ou bien ça revient au même
o
Ce serait quand même mieux avec le fichier. Nous pourrions t'aider à optimiser.
Tu peux dans une macro faire appel à une autre macro avec la commande
Run Nom_macroM
ça va vous géner je sais
au fait je veux remplir les deux tableaux attachés à partir de ma base
Sheets("Arriérés").Range("A7").Value cette cellule contient le code de mon client qui varie à chaque fois (Arriérés!R7C1)
Option Explicit
Sub Etat_Global()
Application.ScreenUpdating = False
Dim I As Integer
Dim e As Integer
Dim K As Integer
For e = 2 To 120
Application.Calculation = xlCalculationManual
Sheets("Arriérés").Range("A7").Value = Sheets("Liste").Range("M" & e).Value
'Calcul des impayés
'Instantané FA
'Calcul N°1
'a
Sheets("Arriérés").Select
Range("C10").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Select
Range("C11").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'Calcul N°2
'a
Sheets("Arriérés").Range("D10").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("D11").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'Calcul N°3
'a
Sheets("Arriérés").Range("E10").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("E11").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'Calcul N°4
'a
Sheets("Arriérés").Range("F10").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("F11").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'Calcul N°5
'a
Sheets("Arriérés").Range("G10").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("G11").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'Calcul N°6
'a
Sheets("Arriérés").Range("H10").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&12,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&12,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("H11").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&12,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&12,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'instantané general
'Calcul N°1
Sheets("Arriérés").Range("C8").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))"
'Calcul N°2
Sheets("Arriérés").Range("D8").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))"
'Calcul N°3
Sheets("Arriérés").Range("E8").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))"
'Calcul N°4
Sheets("Arriérés").Range("F8").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))"
'Calcul N°5
Sheets("Arriérés").Range("G8").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))"
'Calcul N°6
Sheets("Arriérés").Range("H8").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&12,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&12,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))"
'Calcul du prolongé autres
'Calcul N°1
Sheets("Arriérés").Range("C15").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))"
'Calcul N°2
Sheets("Arriérés").Range("D15").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))"
'Calcul N°3
Sheets("Arriérés").Range("E15").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))"
'Calcul N°4
Sheets("Arriérés").Range("F15").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))"
'Calcul N°5
Sheets("Arriérés").Range("G15").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))"
'Calcul N°6
Sheets("Arriérés").Range("H15").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&12,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&12,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))"
'Calcul du prolongé amb
'Calcul N°1
Sheets("Arriérés").Range("C14").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))"
'Calcul N°2
Sheets("Arriérés").Range("D14").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))"
'Calcul N°3
Sheets("Arriérés").Range("E14").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))"
'Calcul N°4
Sheets("Arriérés").Range("F14").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))"
'Calcul N°5
Sheets("Arriérés").Range("G14").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))"
'Calcul N°6
Sheets("Arriérés").Range("H14").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&12,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&12,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))"
'Calcul du prolongé FA
'Calcul N°1
'a
Sheets("Arriérés").Range("C17").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("C18").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'Calcul N°2
'a
Sheets("Arriérés").Range("D17").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("D18").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'Calcul N°3
'a
Sheets("Arriérés").Range("E17").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("E18").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'Calcul N°4
'a
Sheets("Arriérés").Range("F17").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("F18").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'Calcul N°5
'a
Sheets("Arriérés").Range("G17").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("G18").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'Calcul N°6
'a
Sheets("Arriérés").Range("H17").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&12,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&12,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("H18").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS(BASE!C33,BASE!C32,"">""&12,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))-SUM(SUMIFS(BASE!C34,BASE!C32,"">""&12,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'Calcul de revenu total
Sheets("Arriérés").Range("J8").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">=""&0,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))"
Sheets("Arriérés").Range("J10").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">=""&0,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
Sheets("Arriérés").Range("J11").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">=""&0,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
Sheets("Arriérés").Range("J14").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">=""&0,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))"
Sheets("Arriérés").Range("J15").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">=""&0,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))"
Sheets("Arriérés").Range("J17").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">=""&0,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
Sheets("Arriérés").Range("J18").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">=""&0,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'Calcul des totaux par GROUPE et sous-GROUPE
Sheets("Arriérés").Range("I8").Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Sum(Sheets("Arriérés").Range("C8:H8"))
Sheets("Arriérés").Range("I10").Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Sum(Sheets("Arriérés").Range("C10:H10"))
Sheets("Arriérés").Range("I11").Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Sum(Sheets("Arriérés").Range("C11:H11"))
Sheets("Arriérés").Range("I14").Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Sum(Sheets("Arriérés").Range("C14:H14"))
Sheets("Arriérés").Range("I15").Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Sum(Sheets("Arriérés").Range("C15:H15"))
Sheets("Arriérés").Range("I17").Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Sum(Sheets("Arriérés").Range("C17:H17"))
Sheets("Arriérés").Range("I18").Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Sum(Sheets("Arriérés").Range("C18:H18"))
'Calcul des totaux general/FA
For I = 3 To 10
Sheets("Arriérés").Cells(16, I) = Application.WorksheetFunction.Sum(Sheets("Arriérés").Cells(17, I), Sheets("Arriérés").Cells(18, I))
Sheets("Arriérés").Cells(13, I) = Application.WorksheetFunction.Sum(Sheets("Arriérés").Cells(14, I), Sheets("Arriérés").Cells(15, I))
Sheets("Arriérés").Cells(12, I) = Application.WorksheetFunction.Sum(Sheets("Arriérés").Cells(13, I), Sheets("Arriérés").Cells(16, I))
Sheets("Arriérés").Cells(9, I) = Application.WorksheetFunction.Sum(Sheets("Arriérés").Cells(10, I), Sheets("Arriérés").Cells(11, I))
Sheets("Arriérés").Cells(7, I) = Application.WorksheetFunction.Sum(Sheets("Arriérés").Cells(8, I), Sheets("Arriérés").Cells(9, I))
Sheets("Arriérés").Cells(19, I) = Application.WorksheetFunction.Sum(Sheets("Arriérés").Cells(7, I), Sheets("Arriérés").Cells(12, I))
Next I
'Calcul de revenu total
'revenu général
'1
Sheets("Arriérés").Range("C27").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))"
'2
Sheets("Arriérés").Range("D27").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))"
'3
Sheets("Arriérés").Range("E27").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))"
'4
Sheets("Arriérés").Range("F27").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))"
'5
Sheets("Arriérés").Range("G27").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))"
'6
Sheets("Arriérés").Range("H27").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&12,BASE!C3,""C"",BASE!C16,{100;200;300;400;500},BASE!C29,Arriérés!R7C1))"
'revenu FA (pré/autres)
'1
'a
Sheets("Arriérés").Range("C29").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("C30").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'2
'a
Sheets("Arriérés").Range("D29").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("D30").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'3
'a
Sheets("Arriérés").Range("E29").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("E30").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'4
'a
Sheets("Arriérés").Range("F29").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("F30").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'5
'a
Sheets("Arriérés").Range("G29").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("G30").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'6
'a
Sheets("Arriérés").Range("H29").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&12,BASE!C3,""C"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("H30").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&12,BASE!C3,""C"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'prolongé autres
'1
Sheets("Arriérés").Range("C34").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))"
'2
Sheets("Arriérés").Range("D34").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))"
'3
Sheets("Arriérés").Range("E34").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))"
'4
Sheets("Arriérés").Range("F34").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))"
'5
Sheets("Arriérés").Range("G34").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))"
'6
Sheets("Arriérés").Range("H34").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&12,BASE!C3,""T"",BASE!C16,{200;300;400;500},BASE!C29,Arriérés!R7C1))"
'Prolongé Amb
'1
Sheets("Arriérés").Range("C33").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))"
'2
Sheets("Arriérés").Range("D33").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))"
'3
Sheets("Arriérés").Range("E33").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))"
'4
Sheets("Arriérés").Range("F33").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))"
'5
Sheets("Arriérés").Range("G33").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))"
'6
Sheets("Arriérés").Range("H33").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&12,BASE!C3,""T"",BASE!C16,100,BASE!C29,Arriérés!R7C1))"
'Prolongé FA pré/autres
'1
'a
Sheets("Arriérés").Range("C36").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("C37").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">=""&0,BASE!C32,""<=""&1,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'2
'a
Sheets("Arriérés").Range("D36").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("D37").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&1,BASE!C32,""<=""&2,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'3
'a
Sheets("Arriérés").Range("E36").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("E37").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&2,BASE!C32,""<=""&3,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'4
'a
Sheets("Arriérés").Range("F36").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("F37").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&3,BASE!C32,""<=""&6,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'5
'a
Sheets("Arriérés").Range("G36").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
'b
Sheets("Arriérés").Range("G37").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&6,BASE!C32,""<=""&12,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'6
Sheets("Arriérés").Range("H36").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&12,BASE!C3,""T"",BASE!C16,900,BASE!C17,{901904;906904;906905;904912},BASE!C29,Arriérés!R7C1))"
Sheets("Arriérés").Range("H37").Select
ActiveCell.FormulaR1C1 = _
"=Sum(SumIfs(BASE!C33,BASE!C32,"">""&12,BASE!C3,""T"",BASE!C16,900,BASE!C17,{903906;905908;905909},BASE!C29,Arriérés!R7C1))"
'Calcul des totaux par groupe et sous-groupe
Sheets("Arriérés").Range("I27").Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Sum(Sheets("Arriérés").Range("C27:H27"))
Sheets("Arriérés").Range("I29").Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Sum(Sheets("Arriérés").Range("C29:H29"))
Sheets("Arriérés").Range("I30").Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Sum(Sheets("Arriérés").Range("C30:H30"))
Sheets("Arriérés").Range("I33").Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Sum(Sheets("Arriérés").Range("C33:H33"))
Sheets("Arriérés").Range("I34").Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Sum(Sheets("Arriérés").Range("C34:H34"))
Sheets("Arriérés").Range("I36").Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Sum(Sheets("Arriérés").Range("C36:H36"))
Sheets("Arriérés").Range("I37").Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Sum(Sheets("Arriérés").Range("C37:H37"))
For I = 3 To 9
Sheets("Arriérés").Cells(28, I) = Application.WorksheetFunction.Sum(Sheets("Arriérés").Cells(29, I), Sheets("Arriérés").Cells(30, I))
Sheets("Arriérés").Cells(26, I) = Application.WorksheetFunction.Sum(Sheets("Arriérés").Cells(27, I), Sheets("Arriérés").Cells(28, I))
Sheets("Arriérés").Cells(32, I) = Application.WorksheetFunction.Sum(Sheets("Arriérés").Cells(33, I), Sheets("Arriérés").Cells(34, I))
Sheets("Arriérés").Cells(35, I) = Application.WorksheetFunction.Sum(Sheets("Arriérés").Cells(36, I), Sheets("Arriérés").Cells(37, I))
Sheets("Arriérés").Cells(31, I) = Application.WorksheetFunction.Sum(Sheets("Arriérés").Cells(32, I), Sheets("Arriérés").Cells(35, I))
Sheets("Arriérés").Cells(38, I) = Application.WorksheetFunction.Sum(Sheets("Arriérés").Cells(26, I), Sheets("Arriérés").Cells(31, I))
Next I
Application.Calculation = xlCalculationAutomatic
K = e + 2
'Copier note comptant
Sheets("Arriérés").Activate
Sheets("Arriérés").Range("K7:M7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Etat_global").Activate
Sheets("Etat_global").Range("D" & K).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Copier note terme
Sheets("Arriérés").Activate
Sheets("Arriérés").Range("K12:M12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Etat_global").Activate
Sheets("Etat_global").Range("G" & K).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Copier note globale
Sheets("Arriérés").Activate
Sheets("Arriérés").Range("K19:M19").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Etat_global").Activate
Sheets("Etat_global").Range("J" & K).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next e
Application.ScreenUpdating = True
Sheets("Etat_global").Activate
End Sub