XL 2010 Fonction imbriquée à mettre en code VBA

Romain31 · 13 Février 2023

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 · 13 Février 2023

Bonjour,
Essayez ça :

VB:

= 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 · 13 Février 2023

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 · 13 Février 2023

Romain31 à dit:
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 · 13 Février 2023

Bonjour,

Essaye ceci :

VB:

"=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 · 13 Février 2023

Gégé-45550 à dit:
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 · 13 Février 2023

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 · 13 Février 2023

TooFatBoy à dit:
- des "lignes verticales", sous Excel ça s'appelle des "colonnes"

Gégé-45550 · 13 Février 2023

Romain31 à dit:
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 · 13 Février 2023

TooFatBoy à dit:
- des "lignes verticales", sous Excel ça s'appelle des "colonnes"

Gégé-45550 à dit:

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é ?

Gégé-45550 à dit:
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 :

Code:

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 · 13 Février 2023

TooFatBoy à dit:
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 !

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

Tout juste ... Auguste

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

Code:

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 · 13 Février 2023

Je crois que moi aussi

Romain31 · 14 Février 2023

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 · 14 Février 2023

Romain31 à dit:
Votre code fonctionne bien

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

Romain31 · 14 Février 2023

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

XL 2010 Fonction imbriquée à mettre en code VBA

Romain31

XLDnaute Occasionnel

Pièces jointes

Gégé-45550

XLDnaute Accro

Romain31

XLDnaute Occasionnel

Gégé-45550

XLDnaute Accro

TooFatBoy

XLDnaute Barbatruc

Romain31

XLDnaute Occasionnel

TooFatBoy

XLDnaute Barbatruc

Gégé-45550

XLDnaute Accro

Gégé-45550

XLDnaute Accro

TooFatBoy

XLDnaute Barbatruc

Gégé-45550

XLDnaute Accro

TooFatBoy

XLDnaute Barbatruc

Romain31

XLDnaute Occasionnel

TooFatBoy

XLDnaute Barbatruc

Romain31

XLDnaute Occasionnel

Discussions similaires