XL 2010 Fonction imbriquée à mettre en code VBA

[Romain31]

Bonjour à tous,

je travaille sur un fichier de compte bancaire.
Pas mal de choses sont déjà automatisées (peut-être que ce fichier test en PJ pourra donner des idées à d'autres).

La feuille détail récapitule les différentes entrées/sortie par mois.
En cellule B2 une fonction SOMME imbriqué unique (par l'utilisation de références absolues et relatives) me permet par recopie incrémentée horizontale et verticale de remplir la feuille (macro Recopie_B2 à cet effet).

Voici cette formule

=SOMME(SOMME.SI.ENS(Banque!$C$2:$C$998;Banque!$F$2:$F$998;$A2;Banque!$G$2:$G$998;B$1);SOMME.SI.ENS(Banque!$D$2:$D$998;Banque!$F$2:$F$998;$A2;Banque!$G$2:$G$998;B$1))

Je souhaiterai intégrer cette formule assez complexe en code VBA
Worksheets("Détail").Range("B2").FormulaLocal = ???
dans mon code de Recopie_B2.

je suis conscient que ce n'est pas simple pais ce forum est tellement formidable que je ne désespère pas.
Merci à tous les courageux qui pourront m'aider et très bonne journée à tous

 

[Gégé-45550]

Bonjour,
Essayez ça :

= Application.WorksheetFunction.Sum(SumIfs(Worksheets("Banque").Range("$C$2:$C$998"), Worksheets("Banque").Range("$F$2:$F$998"), Worksheets("Banque").Range("$A2"), Worksheets("Banque").Range("$G$2:$G$998"), Worksheets("Banque").Range("B$1")), SumIfs(Worksheets("Banque").Range("$D$2:$D$998"), Worksheets("Banque").Range("$F$2:$F$998"), Worksheets("Banque").Range("$A2"), Worksheets("Banque").Range("$G$2:$G$998"), Worksheets("Banque").Range("B$1")))

Cordialement,

 

[Romain31]

Bonjour Gégé-45550,

Merci beaucoup pour votre réponse.
Je viens de tester mais j'ai hélas une erreur de syntaxe.

Mais cela me permet d'avancer car je ne voyais pas comment formuler les références absolues ou non.
Je pense que l'on ne doit pas être très loin de la solution.

Voici les deux fonction SOMME.SI.ENS que la fonction SOMME doit totaliser à écrire en VBA.

 

[Gégé-45550]

Bonjour Gégé-45550,

Merci beaucoup pour votre réponse.
Je viens de tester mais j'ai hélas une erreur de syntaxe.

Mais cela me permet d'avancer car je ne voyais pas comment formuler les références absolues ou non.
Je pense que l'on ne doit pas être très loin de la solution.

Voici les deux fonction SOMME.SI.ENS que la fonction SOMME doit totaliser à écrire en VBA.

Regarde la pièce jointe 1163313
Regarde la pièce jointe 1163314

Re-bonjour,
Je pense que l'erreur de syntaxe vient du Critères1 ($A2), j'ai présupposé, n'ayant pas l'info (je n'ai pas ouvert votre fichier), qu'il s'agissait de la cellule A2 de la feuille "Banque", mais c'est manifestement sur une autre feuille que cette cellule se situe.
il suffit de corriger dans la formule tous les Worksheets("Banque").Range("$A2") par Worksheets("Détail").Range("$A2") et ça devrait marcher.
[EDIT] Apparemment, il s'agit de la feuille "Détail" ... et pareil pour les Citères2 (B$1) qui deviennent Worksheets("Détail").Range("B$1")
Tenez-moi au courant.

 

[TooFatBoy]

Bonjour,

Essaye ceci :

"=SUM(SUMIFS(Banque!$C$2:$C$998,Banque!$F$2:$F$998,$A2,Banque!$G$2:$G$998,B$1),SUMIFS(Banque!$D$2:$D$998,Banque!$F$2:$F$998,$A2,Banque!$G$2:$G$998,B$1))"

 

[Romain31]

Re-bonjour,
Je pense que l'erreur de syntaxe vient du Critères1 ($A2), j'ai présupposé, n'ayant pas l'info (je n'ai pas ouvert votre fichier), qu'il s'agissait de la cellule A2 de la feuille "Banque", mais c'est manifestement sur une autre feuille que cette cellule se situe.
il suffit de corriger dans la formule tous les Worksheets("Banque").Range("$A2") par Worksheets("Détail").Range("$A2") et ça devrait marcher.
[EDIT] Apparemment, il s'agit de la feuille "Détail" ... et pareil pour les Citères2 (B$1) qui deviennent Worksheets("Détail").Range("B$1")
Tenez-moi au courant.

Merci pour votre réponse;

Finalement, il m'est venu l'idée d'utiliser l'enregistreur de macros et j'ai donc mis une fonction SOMME de mes deux fonctions SOMME.ENS.SI. Et cela fonctionne parfaitement.

Range("B2").FormulaR1C1 = _
"=SUM(SUMIFS(Banque!R2C3:R998C3,Banque!R2C6:R998C6,RC1,Banque!R2C7:R998C7,R1C),SUMIFS(Banque!R2C4:R998C4,Banque!R2C6:R998C6,RC1,Banque!R2C7:R998C7,R1C))"

Personnellement je préfère votre proposition que je vais regarder plus attentivement.
La Cellule A2 est le premier terme de la feuille détail. Il est écrit $A2 pour la recopie incrémentée qui récupère les autres types de dépenses de la colonne.

 

[TooFatBoy]

Je viens d'ouvrir le fichier de #1 :
- des "lignes verticales", sous Excel ça s'appelle des "colonnes"
- pourquoi ne pas utiliser un tableau structuré ?

 

[Gégé-45550]

- des "lignes verticales", sous Excel ça s'appelle des "colonnes"

 

[Gégé-45550]

Merci pour votre réponse;

Finalement, il m'est venu l'idée d'utiliser l'enregistreur de macros et j'ai donc mis une fonction SOMME de mes deux fonctions SOMME.ENS.SI. Et cela fonctionne parfaitement.

Range("B2").FormulaR1C1 = _
"=SUM(SUMIFS(Banque!R2C3:R998C3,Banque!R2C6:R998C6,RC1,Banque!R2C7:R998C7,R1C),SUMIFS(Banque!R2C4:R998C4,Banque!R2C6:R998C6,RC1,Banque!R2C7:R998C7,R1C))"

Personnellement je préfère votre proposition que je vais regarder plus attentivement.
La Cellule A2 est le premier terme de la feuille détail. Il est écrit $A2 pour la recopie incrémentée qui récupère les autres types de dépenses de la colonne.

Re-bonjour,
Je n'avais pas prêté attention au fait qu'il s'agit d'alimenter des .Formula
Dans ce cas, il convient effectivement de supprimer le Application.WorksheetFunction

 

[TooFatBoy]

- des "lignes verticales", sous Excel ça s'appelle des "colonnes"

Ben s'pas drôle, c'est vrai. Pis des "lignes horizontales", c'est des "lignes".
Mais ce qu'il faut retenir, c'est surtout la question : pourquoi ne pas utiliser un tableau structuré ?

Je n'avais pas prêté attention au fait qu'il s'agit d'alimenter des .Formula
Dans ce cas, il convient effectivement de supprimer le Application.WorksheetFunction

Et aussi remplacer Worksheets("Banque").Range("$C$2:$C$998") par un truc genre Banque!$C$2:$C$998

Perso, je remplacerais donc la macro recopie_b2 par l'instruction suivante :

Sheets("Détail").Range("B2:M50").Formula = "=SUM(SUMIFS(Banque!$C$2:$C$998,Banque!$F$2:$F$998,$A2,Banque!$G$2:$G$998,B$1),SUMIFS(Banque!$D$2:$D$998,Banque!$F$2:$F$998,$A2,Banque!$G$2:$G$998,B$1))"

 

[Gégé-45550]

Ben s'pas drôle, c'est vrai. Pis des "lignes horizontales", c'est des "lignes".
Mais ce qu'il faut retenir, c'est surtout la question : pourquoi ne pas utiliser un tableau structuré ?

Bien sûr que c'est vrai ... mais c'est drôle !

Et aussi remplacer Worksheets("Banque").Range("$C$2:$C$998") par un truc genre Banque!$C$2:$C$998

Tout juste ... Auguste

Perso, je remplacerais donc la macro recopie_b2 par l'instruction suivante :

Sheets("Détail").Range("B2:M50").Formula = "=SUM(SUMIFS(Banque!$C$2:$C$998,Banque!$F$2:$F$998,$A2,Banque!$G$2:$G$998,B$1),SUMIFS(Banque!$D$2:$D$998,Banque!$F$2:$F$998,$A2,Banque!$G$2:$G$998,B$1))"

Perso je referais tout le fichier ... mais d'accord ... encore !

 

[TooFatBoy]

Je crois que moi aussi

 

[Romain31]

Bonjour TooFatBoy

Votre code fonctionne bien
Sheets("Détail").Range("B2:M50").Formula = "=SUM(SUMIFS(Banque!$C$2:$C$998,Banque!$F$2:$F$998,$A2,Banque!$G$2:$G$998,B$1),SUMIFS(Banque!$D$2:$D$998,Banque!$F$2:$F$998,$A2,Banque!$G$2:$G$998,B$1))"

Bonne journée

 

[TooFatBoy]

Votre code fonctionne bien

OK, mais ça ne dit pas pourquoi tu n'utilises pas les tableaux structurés.

 

[Romain31]

Honnêtement, je n'y ai pensé et je trouve que ces formules écrites "en dur" dans le code associé à un bouton sont bien faciles à remettre en place.

Par contre je me suis fait ce code pour récupérer cert