Somme mensuelle de nombres à retrouver dans différentes feuilles

**bolide7** · 09/04/2014, 00h26

Bonjour à tous,
Mes diverses tentatives n’ayant rien données, je reviens vers le forum pour ce problème.
Dans un classeur, le nom des onglets de certaines feuilles (400 environ) est un nombre compris entre de 14001 à 76999.
D’autres feuilles avec des noms comme « Menu », « Liste », « Tableau » sont noyées au milieu de ces 400 feuilles.
Autrement dit, à part ces 400 feuilles, aucun autre onglet ne correspond à un nombre.
Sur ces 400 feuilles ayant la même présentation, on trouve sur la plage P3

56 une date formatée avec l’étoile « *14/03/12 » et sur la plage S3:S56 un nombre.
Sur une feuille de ce même classeur, je souhaiterai obtenir le cumul mensuel des nombres se trouvant dans la plage S3:S56.
Dans la première colonne (colonne A) de cette feuille, on pourrait placer les mois formulés de la façon suivante : « Janvier 2014 », « Février 2014 », « Mars 2014 », « Avril 2014 », etc.
Dans la deuxième colonne (colonne B) de cette feuille, on retrouverait le cumul mensuel, qui correspond à l’addition d’une vingtaine de lignes réparties sur ces 400 feuilles.
A noter qu’il peut y avoir plusieurs lignes (plusieurs dates) pour un même mois sur une feuille.
Autrement dit, quelle est la fonction permettant de retrouver puis d’additionner tous les nombres d’un mois particulier ?
D’avance, merci beaucoup pour votre aide.
Bonne journée.
bolide7

**Daniel.C** · 09/04/2014, 11h06

Bonjour,

Essaie :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub test()
    Dim Ligne As Long, Sh As Worksheet, C As Range, Lig2 As Variant
    With Sheets("Résultat")
        For Each Sh In Worksheets
            If IsNumeric(Sh.Name) Then
                For Each C In Sh.[P3:P56]
                    If C.Value <> "" Then
                        Lig2 = Application.Match(Format(C.Value, "mmmm yyyy"), .[A:A], 0)
                        If Not IsNumeric(Lig2) Then
                            Ligne = Ligne + 1
                            .Cells(Ligne, 1) = Format(C.Value, "mmmm yyyy")
                            .Cells(Ligne, 2) = C.Offset(, 3)
                        Else
                            .Cells(Lig2, 2) = .Cells(Lig2, 2) + C.Offset(, 3)
                        End If
                    End If
                Next C
            End If
        Next Sh
    End With
End Sub

**bolide7** · 09/04/2014, 19h24

Bonjour Daniel.C,
C'est super sympa de me répondre de nouveau.
Je pensais que ce problème pouvais se régler par une fonction classique.
Ai-je tort ?
J'aurai donc préféré une fonction, qui est plus à mon niveau de compréhension.
Etant plus que nul en VBA, j'ai cependant essayé avec ta jolie proposition.
Hélas, j'ignore ce qu'il faut faire avant de placer ta proposition, où il faut placer ta fonction et ce qu'il faut faire après la fonction recopiée.
Ce qui fait beaucoup de choses que j'ignore totalement, même pour un simple recopiage.
Tout ce que je sais faire c'est cliquer sur "Visual Basic" dans l'onglet "Développeur".
Puis double cliquer sur "Feuil1" où se trouve ma colonne A et les mois.
Mais ensuite pour moi, c'est l'inconnu.
Que faut-il choisir dans les 2 menus déroulants proposés ?
Et ensuite que faut-il faire ?
D'avance merci beaucoup.
Bonne soirée.
bolide7

**Daniel.C** · 09/04/2014, 19h37

Bonjour,

Je ne vois pas comment utiliser un formule avec un nombre important de feuilles.

Regarde le classeur joint, ça te donnera peut-être une idée.

**bolide7** · 10/04/2014, 00h30

Bonjour Daniel.C,

Merci beaucoup pour avoir pris le temps de faire un classeur pour modèle.
C’est exactement cela que je recherche, si ce n’est que dans le classeur réel, la colonne des résultats est en C et non en B, comme je l’avais indiqué.
J’ignore si cela change quelque chose dans ta proposition.
OK pour ce qui est d’une éventuelle fonction classique (impossible car trop de feuilles).

Pour ta proposition, n’ayant pas de module dans mon classeur, dans Visual Basic, j’ai placé ta fonction dans la fenêtre après un double clic sur la « Feui1 » (la feuille des résultats).
Ca ne marche pas.
J’ai fini par trouver comment insérer un module, dans lequel j’ai recopié ta fonction avant de refermer la fenêtre VBA.
J’ai placé mon curseur sur la cellule où j’attends le résultat pour « Janvier 2014 » et j’ai cliqué sur « Exécuter » dans « Macros ».
Avec une fenêtre, Monsieur Bill GATES m’annonce alors : « Erreur d’exécution 9. L’indice n’appartient pas à la sélection. ».
Que faut-il faire ?
Que dois-je rectifier dans ma manipulation ?
En effet, je voudrais bien arriver à mettre en œuvre une fonction VBA.

Par ailleurs sur ce même forum, j’ai appris que je devais enregistrer mon classeur avec l’extension .xlsm, au lieu de .xlsx.
Pourquoi ton classeur qui fonctionne très bien est avec une simple extension .xls ?

D’avance merci beaucoup pour tes futures réponses qui me permettront de comprendre un peu la mise en place d'une fonction avec le langage VBA.
Bonne journée.
bolide7

**Daniel.C** · 10/04/2014, 10h33

C’est exactement cela que je recherche, si ce n’est que dans le classeur réel, la colonne des résultats est en C et non en B, comme je l’avais indiqué.

Il faut trouver les deux lignes commençant par ".Cells(Ligne, 2)" et remplacer ce texte par ".Cells(Ligne, 3)".

Pour insérer un module :

Quand la fenêtre VBE est affichée, fais un clic droit dans la partie gauche sur le projet portant le nom de ton classeur et clique sur "Insertion" et "Module". Colle le code dans la partie droite de la fenêtre.

Pourquoi ton classeur qui fonctionne très bien est avec une simple extension .xls ?

parce que :

1. le format .xlsx n'admet pas les macros;
2. le format .xlsm n'est pas autorisé pour les pièces jointes;
3. je ne sais pas si tu peux décompresser un fichier zip.
Tu peux l'enregistrer au format .xlsm.

**bolide7** · 11/04/2014, 00h35

Bonjour Daniel.C,

OK pour le changement de colonne.
Grâce à tes informations, j'ai pu facilement changé les coordonnées de la colonne où figureront les résultats, quand cela marchera.

Comme indiqué dans mon précédent message, j'avais fini par trouver comment insérer un module.
Le problème est après l'insertion de ta formule : Que doit on faire pour que cela marche ?
Lorsque je me positionne sur une cellule C1, où j’attends le résultat pour « Janvier 2014 » et que je clique sur « Exécuter » dans « Macros », j'ai, comme hier, le message d'erreur de Monsieur Bill GATES qui m’annonce : « Erreur d’exécution 9. L’indice n’appartient pas à la sélection. ».
Pourquoi ce message ?
A quoi correspond il ?
Que faut-il faire après avoir placé ta fonction ?
J'ai pourtant mis l'extension .xlsm à mon classeur.
Je voudrais bien finir par y arriver.
Si suite à ton prochain message, je n'arrive toujours pas à débloquer la situation, je t'enverrai mon fichier d'essai.

Précision : Je ne sais pas décompressé.

Bonne journée.
bolide7

**Daniel.C** · 11/04/2014, 10h00

Lorsque je me positionne sur une cellule C1, où j’attends le résultat pour « Janvier 2014 » et que je clique sur « Exécuter » dans « Macros », j'ai, comme hier, le message d'erreur de Monsieur Bill GATES qui m’annonce : « Erreur d’exécution 9. L’indice n’appartient pas à la sélection. ».

Tu n'as pas à te positionner sur aucune cellule. La feuille "Résultat" doit être vide avant l'exécution de la macro, ce que je ne t'ai pas dit. Pour l'erreur, est-ce qu'elle se produit dans le classeur fourni ou dans ton classeur ? Précise sur quelle ligne.

**bolide7** · 12/04/2014, 00h41

Bonjour Daniel.C,

Ca veut dire quoi vide ?
Dans la feuille de résultat, il n'y a que les mois en colonne A.

Ton fichier marche.
L'erreur se produit dans mon classeur test.
Il ne doit pas y avoir grand chose, mais je ne sais pas quoi.
C'est pourquoi je joins à ce message ce fameux classeur.

D'avance merci beaucoup.
Bon week-end.
bolide7

**casefayere** · 12/04/2014, 12h58

Bonjour,
C 'est normal, tes feuilles ne sont pas renommées donc :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

    With Sheets("Résultat")

ne peut fonctionner vu que ta feuille dans le fichier "essai", se nomme "Feuil1", tu peux utiliser le Code.Name

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

With Feuil1

**bolide7** · 13/04/2014, 01h23

Bonjour casefayere,

Oups !
Ce n'est pourtant pas faute d'avoir chercher l'origine du problème.
Ce n'était pourtant pas compliqué.
Merci beaucoup pour ton dépannage.

Par contre, nouveau problème, dans la feuille que j'ai maintenant renommée "Résultat" n'apparaît que les chiffres de la feuille "14003".
Pourquoi les chiffres de la feuille "14002" ne sont ils pas additionnée avec ceux de la feuilles "14003"?

Par ailleurs, je découvre que contrairement à une fonction classique, les résultats de la fonction VBA ne se mettent pas à jour automatiquement.
En effet, lorsque je change les 2 chiffres de la feuille "14003", ils n'apparaissent pas aussitôt dans la feuille résultat.
Il faut de nouveau exécuter la macro.
Pourquoi ?
Peut on contourner ce problème ?

D'avance, merci beaucoup.
Bon dimanche.
bolide7

**Daniel.C** · 13/04/2014, 15h43

bonjour,

Oui, la macro devrait être :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub test()
    Dim Ligne As Long, Sh As Worksheet, C As Range, Lig2 As Variant
    With Feuil1
        For Each Sh In Worksheets
            If IsNumeric(Sh.Name) Then
                For Each C In Sh.[P3:P56]
                    If C.Value <> "" Then
                        Lig2 = Application.Match(Format(C.Value, "mmmm yyyy"), .[A:A], 0)
                        If Not IsNumeric(Lig2) Then
                            Ligne = Ligne + 1
                            .Cells(Ligne, 1) = Format(C.Value, "mmmm yyyy")
                            .Cells(Ligne, 3) = C.Offset(, 3)
                        Else
                            .Cells(Lig2, 3) = .Cells(Lig2, 3) + C.Offset(, 3)
                        End If
                    End If
                Next C
            End If
        Next Sh
    End With
End Sub

Par ailleurs, je découvre que contrairement à une fonction classique, les résultats de la fonction VBA ne se mettent pas à jour automatiquement.
En effet, lorsque je change les 2 chiffres de la feuille "14003", ils n'apparaissent pas aussitôt dans la feuille résultat.
Il faut de nouveau exécuter la macro.
Pourquoi ?

C'est comme ça, c'est la différence avec une fonction. Tu peux utiliser la macro suivante. A la différence d'une fonction, si tu remplaces un nombre par un autre, le nouveau nombre est ajouté, il ne remplace pas l'ancien. Par exemple, si tu mets 6, puis 8 dans la même cellule, tu auras 14 comme résultat. Dans cette exemple, si tu veux remplacer 6 par 8, commence par mettre -6 puis 8.

Dans le module "ThisWorkbook", mets :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Ligne As Long, C As Range, Lig2 As Variant
    If Not IsNumeric(Sh.Name) Then Exit Sub
    If Intersect(Target, Sh.[P3:P56]) Is Nothing And _
    Intersect(Target, Sh.[S3:S56]) Is Nothing Then Exit Sub
    If Sh.Cells(Target.Row, "P") = "" Then Exit Sub
    With Feuil1
        If Target.Value <> "" Then
            Lig2 = Application.Match(Format(Sh.Cells(Target.Row, "P"), "mmmm yyyy"), .[A:A], 0)
            Application.EnableEvents = False
            If Not IsNumeric(Lig2) Then
                Ligne = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
                .Cells(Ligne, 1) = Format(Target.Value, "mmmm yyyy")
                .Cells(Ligne, 3) = Sh.Cells(Target.Row, "S")
            Else
                .Cells(Lig2, 3) = .Cells(Lig2, 3) + Sh.Cells(Target.Row, "S")
            End If
            Application.EnableEvents = True
        End If
    End With
End Sub

**bolide7** · 13/04/2014, 19h00

Bonjour Daniel,

Même si cela a été un peu long, on a fini par y arriver.
CA MARCHE !
J’ai même découvert qu’en effaçant (avec la touche "Suppr") le précédent résultat de ta macro, l’exécution de ta fonction trouve un résultat juste (sans additionner le précédent résultat).
Merci beaucoup pour ta précieuse aide.
Sans toi, mon classeur serait totalement bloqué.

Dans le même classeur et dans le même ordre d’idée, j’ai un problème presque identique.
Il ne s’agit plus d’une addition, mais du comptage d’un nombre de valeurs.
Dans ce même classeur, j’ai donc toujours le nom des onglets de certaines feuilles (400 environ) qui est un nombre compris entre de 14001 à 76999.
D’autres feuilles avec des noms comme « Menu », « Liste », « Tableau » sont noyées au milieu de ces 400 feuilles.
Autrement dit, à part ces 400 feuilles, aucun autre onglet ne correspond à un nombre.
Sur ces 400 feuilles ayant la même présentation, on trouve toujours sur la plage P3

56 une date formatée avec l’étoile « *14/03/12 ».
Sur une feuille de ce même classeur (la même que pour le résultat de l’addition), je souhaiterai obtenir le nombre de valeur par mois (combien de fois apparaît l’un des 30 ou 31 jours du mois) dans la plage P3

56 des 400 feuilles.
Dans la première colonne (colonne A) de cette feuille, j'ai les les mois formulés de la façon suivante : « Janvier 2014 », « Février 2014 », « Mars 2014 », « Avril 2014 », etc.
Dans la quatrième colonne (colonne D) de cette feuille, on retrouverait le nombre de fois où les dates du mois de la colonne A apparaît dans la plage P3

56 des 400 feuilles.
A noter qu’il peut y avoir plusieurs dates identiques sur l’une feuille de ces 400 feuilles.
Autrement dit, quelle est la fonction permettant de compter le nombre de fois où une date du mois de la colonne A apparaît dans la plage P3

56 des 400 feuilles ?

Pourrais-tu me préciser où je dois placer cette seconde fonction VBA ?
Je suppose sur un autre module, à moins que ça ne soit sur le même.

D’avance, merci beaucoup pour ta nouvelle aide.
Bonne soirée.
bolide7

**Daniel.C** · 14/04/2014, 10h34

Bonjour,

Merci beaucoup pour ta précieuse aide.
Sans toi, mon classeur serait totalement bloqué.

et aussi sans casefayere.

Modifie la macro :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub test()
    Dim Ligne As Long, Sh As Worksheet, C As Range, Lig2 As Variant
    With Feuil1
        For Each Sh In Worksheets
            If IsNumeric(Sh.Name) Then
                For Each C In Sh.[P3:P56]
                    If C.Value <> "" Then
                        Lig2 = Application.Match(Format(C.Value, "mmmm yyyy"), .[A:A], 0)
                        If Not IsNumeric(Lig2) Then
                            Ligne = Ligne + 1
                            .Cells(Ligne, 1) = Format(C.Value, "mmmm yyyy")
                            .Cells(Ligne, 3) = C.Offset(, 3)
                            .Cells(Ligne, 4) = 1
                        Else
                            .Cells(Lig2, 3) = .Cells(Lig2, 3) + C.Offset(, 3)
                            .Cells(Lig2, 4) = .Cells(Lig2, 4) + 1
                        End If
                    End If
                Next C
            End If
        Next Sh
    End With
End Sub

**casefayere** · 14/04/2014, 10h41

Merci Daniel mais mon intervention n'était qu'une modeste constatation. Je n'ai pas voulu m'impliquer plus dans cette discussion mais quand je vois "400 feuilles", je crains pour le temps d'exécution, je serais passé par un tableau (peut-être à 3 dimensions) mais je ne maitrise pas encore et n'ai pas trop le temps

Bonne journée

PS : A l'attention de bolide7, tardivement je rappelle une des recommandations de Pierre Fauconnier

Les questions qui ont trait à l'automatisation, les fonctions personnelles, le code VBA, ..., doivent être posées sur le forum Macros et VBA EXCEL

**Daniel.C** · 14/04/2014, 11h48

@ casefayere :

Tu as peut-être raison bien que la plage à traiter sur chaque feuille ne fasse qu'une cinquantaine de lignes (et deux colonnes utiles).

**bolide7** · 15/04/2014, 00h46

Bonjour Daniel,

J'ai placé ta fonction dans mon petit fichier test et le résultat est splendide.
Merveilleux, cela correspond exactement à mes attentes.
Problème : Il s'agit d'un petit fichier test et non du fichier réel.

En effet, j'ai été obligé de faire quelques modifications dans mon fichier réel.
Hélas, ces modifications m'ont contraint à adapter ta fonction ou plus exactement à essayer d'adapter ta fonction.
Malgré plusieurs essais dans tous les sens, je n'ai pas réussi ce que je pensais être facile.
Dans mon fichier réel, ma feuille où se trouve les résultats ne s'appelle pas "Feuil1", mais possède un nom avec des espaces : "Tableau récapitulatif mensuel".
Dans cette feuille de résultats, le résultat de l'addition apparaît en colonne G (au lieu de C).
Le nombre de jours par mois reste en colonne D
D'après ce que j'ai pu constater lors de mes différents essais, le langage VBA n'apprécie pas les noms de feuille avec des espaces.

Bien évidemment, suite au propos de casefayere, je vous tiens au courant de la rapidité de l'exécution de ta fonction lorsqu'elle sera mise en place dans le classeur avec les 400 feuilles.

D'avance, merci.
Bonne journée.
bolide7

**Marc-L** · 15/04/2014, 00h49

Bonjour, bonjour,

d'où l'évidence de joindre un classeur exemple reflétant le réel !

Normalement un nom de feuille avec espace n'a jamais posé de problème,
à moins que ce soit particulier depuis la version 2013 … Évidemment l'accentuation doit être respectée.

Sinon cela a l'avantage d'être formateur, bonne continuation !

**bolide7** · 03/02/2016, 13h25

Bonjour Daniel.C et casefayere,
Bonjour à tous,

Comme annoncé dans mon dernier post, je reviens vers vous pour faire le point.
Ne possédant pas le fichier original avec les 400 feuilles, c’’est seulement aujourd’hui, que j’ai pu tester la formule de Daniel.C, qui fonctionne dans mon petit fichier test, mais seulement avec 2 feuilles.
Comme le fichier original a légèrement évolué, j’ai fait quelques modifications :
- La plage de recherche dans les 400 feuilles va maintenant de la ligne 3 à la ligne 112 (au lieu de 3 à 56).
- Le nombre de fois où les dates du mois de la colonne A apparaît dans la plage P3

112 des 400 feuilles doit apparaître en colonne D (colonne numéro 4)
- Le cumul mensuel des nombres se trouvant dans la plage S3:S112 des 400 feuilles doit apparaître en colonne G (colonne numéro 7).
A noter qu’il peut y avoir plusieurs dates identiques sur l’une de ces 400 feuilles, mais que chaque date doit être considérée comme une occurrence dans le résultat, qui doit figurer en colonne D.
Etant nulle en VBA, j’espère que les modifications, que j’ai faites, sont autorisées en VBA et ne perturberont pas les calculs.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub test()
    Dim Ligne As Long, Sh As Worksheet, C As Range, Lig2 As Variant
    With Sheets("Résultat mensuel")
        For Each Sh In Worksheets
            If IsNumeric(Sh.Name) Then
                For Each C In Sh.[P3:P112]
                    If C.Value <> "" Then
                        Lig2 = Application.Match(Format(C.Value, "mmmm yyyy"), .[A:A], 0)
                        If Not IsNumeric(Lig2) Then
                            Ligne = Ligne + 1
                            .Cells(Ligne, 1) = Format(C.Value, "mmmm yyyy")
                            .Cells(Ligne, 7) = C.Offset(, 3)
                            .Cells(Ligne, 4) = 1
                        Else
                            .Cells(Lig2, 7) = .Cells(Lig2, 7) + C.Offset(, 3)
                            .Cells(Lig2, 4) = .Cells(Lig2, 4) + 1
                        End If
                    End If
                Next C
            End If
        Next Sh
    End With
End Sub

Pour terminer, suite à une première tentative faite dans le fichier original, j’ai constaté que comme le pressentait casefayere, le nombre important de feuilles (400) est une réelle difficulté pour EXCEL, qui patine réellement.
En réalité, si le fichier contient plus de 400 feuilles, il n’y a qu’une petite centaine de feuilles (96 exactement à ce jour, mais ce nombre peut évoluer dans l’avenir), où les informations recherchées sont renseignées.
Serait-il donc possible de modifier la proposition de Daniel.C pour demander à EXCEL de faire la recherche uniquement sur les 100 feuilles renseignées, dont les noms sont à ce jour:
- De 14000 à 14075.
- De 28000 à 27025.
- De 76000 à 76011.
- De 94000 à 94017 ?
Comme le nombre de ces feuilles est évolutif, il serait peut-être judicieux de demander à EXCEL de travailler uniquement sur les feuilles, dont le nom :
- Commence par 14, 28, 76 et 94.
- Ou qui est compris entre 14000 et 14200 pour être large, 28000 et 28100 pour être large, 76000 et 76100 pour être large, et pour finir entre 94000 et 94100 pour être large.

D’avance, merci pour votre aide, qui devrait faire gagner du temps à EXCEL.
En effet, il n’y a aucun intérêt à faire des recherches dans des feuilles non renseignées.

Bon après-midi.
bolide7