Discussion :

[bolide7]

Bonjour à tous,
Dans un classeur, j’ai 400 feuilles identiques, qui portent un nom sous forme d’un nombre de 5 chiffres, comme par exemple 14001 ou 28099.
Sur chacune de ces feuilles :
- En colonne P3:P112, j’ai des dates JJ/MM/AA.
- En colonne Q3:Q112, j’ai des intitulés de thème.
Sur une feuille récapitulative placée dans ce même classeur, j’ai :
- En colonne A, les différents intitulés de thème utilisés dans mon classeur.
- Sur la ligne 4, les exercices, comme par exemple 2012-2013 en C4 ou 2013-2014 en E4 ou encore 2014-2015 en G4.
Mes exercices vont du 01/09 au 31/08.
Sur cette feuille récapitulative, pour chaque thème, je souhaite connaitre le nombre de fois où le thème de la colonne A est utilisé dans les 400 feuilles pour un exercice donné.
Autrement dit et par exemple, dans la cellule C9 (première cellule à renseigner de mon tableau récapitulatif) je souhaite avoir le nombre de fois, que le thème de la cellule A9 se retrouve dans les différentes feuilles (Q3 :Q112), mais uniquement durant des jours de l’exercice 2012-2013 (cellule C4 de la feuille récapitulative).
Pourriez-vous me communiquer la formule VBA, qui pourrait répondre à mon exigence ?
Petite précision qui a son importance, je suis nulle en VBA.
J’espère avoir été le plus clair possible.
D’avance, merci beaucoup pour vos précieuses informations.
bolide7

[antonysansh]

Bonjour bolide7,

Pour un début de réponse tu peux mettre le code ci-dessous dans un module :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Option Explicit
 
Public Const NomFeuilleRecap = "Recap"
 
Function MaFonction(ByVal rg As Range) As Long
    Dim sh As Worksheet, Plage$, res&
    Plage = "Q3:Q112"
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> NomFeuilleRecap Then _
            res = res + Application.WorksheetFunction.CountIf(sh.Range(Plage), rg)
    Next
    MaFonction = res
End Function

Pour prendre en compte les dates, tu peux remplacer .CountIf par .CountIfs et adapter le code.

[Dormeur74]

C'est franchement incompréhensible.
D'abord non ne sait pas si la feuille récapitulative est en première ou dernière feuille, ensuite comment peut-on imaginer la feuille de récapitulation si dans la cellule A4 on a à la fois un intitulé de thème et la désignation d'un exercice.
Le mieux est de nous proposer le classeur, histoire d'y voir clair.

[antonysansh]

Pour prendre les dates en compte :

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
 
Public Const NomFeuilleRecap = "Recap"
 
Function MaFonction2(ByVal Theme As Range, ByVal Exercice As Range) As Long
    Dim sh As Worksheet, PlageTheme$, PlageExercice$, res&, t$()
    PlageTheme = "Q3:Q112"
    PlageExercice = "P3:P112"
    t = Split(Exercice, "-")
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> NomFeuilleRecap Then _
            res = res + Application.WorksheetFunction.CountIfs( _
                sh.Range(PlageExercice), ">=" & CDbl(DateSerial(t(0), 1, 1)), _
                sh.Range(PlageExercice), "<=" & CDbl(DateSerial(t(1), 12, 31)), _
                sh.Range(PlageTheme), Theme)
    Next
    MaFonction2 = res
End Function

En C4 : deux dates séparées d'un '-' exemple 2012-2013
En A9 : Un thème
En C9 : La formule =MaFonction2(A9;$C$4)

Il faut juste que tu change la ligne :
Public Const NomFeuilleRecap = "Recap" pour y mettre le nom de ta feuille récapitulatif

[bolide7]

Bonjour antonysansh et Dormeur74,
Merci beaucoup pour vos très rapides réponses.


Pour Dormeur74 :
La feuille Récap est située au début du classeur, mais n’est pas la première, c’est la quatrième feuille.
Si le classeur original contient plus de 400 feuilles identiques, dans lesquelles les recherches doivent s’effectuer, il contient une vingtaine d’autres feuilles, dont le nom n’est pas un nombre.

Comme indiqué dans mon post de 2h18, la première cellule à renseigner du tableau récapitulatif est la cellule C9. En conséquence, on a :
- En A9, un thème.
- En C4, un exercice, 2012-2013.


Pour antonysansh :
Merci beaucoup de prendre le temps de me communiquer une fonction VBA prête à l’emploi.
En effet, si je maîtrise un peu les fonctions EXCEL, comme indiqué précédemment, l’écriture en VBA est pour moi, quasiment incompréhensible.
J’aurai déjà bien du mal à l’insérer correctement dans le classeur.

Pour prendre en compte les dates, tu peux remplacer .CountIf par .CountIfs et adapter le code.

Pour moi cette phrase est donc incomprésible.

Il ne s’agit seulement que de ma deuxième fonction VBA.
Ma première fonction VBA, qui concerne ce même classeur et qui ressemble énormément à celle-ci, est évoquée en ce moment même dans cette discussion sur ce même forum, mais à la première page du forum Excel et non dans le sous-forum « Macros et VBA Excel ».
http://www.developpez.net/forums/d14...ntes-feuilles/
Et comme évoqué dans cette autre discussion, à la fin de mon post de 13h25 de ce jour, cette première fonction VBA rencontre quelque problème due au nombre important de feuilles.

Je te précise que si le même intitulé apparaît 2 fois pour 2 dates identiques (donc sur 2 lignes différentes) dans une des 400 feuilles, je souhaite qu’EXCEL compte 2 occurrences. J’ignore si tu avais intégré ce détail dans mes explications de cette nuit.

Je vais devoir m’absenter, mais reviendrais vers toi durant la soirée pour te communiquer le résultat obtenu après avoir inséré ta fonction VBA dans un deuxième module.

Encore merci beaucoup.

Très bon après-midi.
bolide7

[bolide7]

Bonsoir antonysansh,
Malgré la lourdeur de mon classeur avec ses 400 feuilles, je t’informe que les résultats s’affichent plutôt très rapidement en comparaison de la première fonction VBA. Quelques secondes, c’est nettement mieux que les 2 heures 30 minutes de calcul pour la première fonction VBA. C’est super !
J’ignore pourquoi, mais contrairement à la première fonction VBA, ta proposition se met à jour automatiquement, comme une fonction EXCEL. C’est super !
Par contre, les résultats obtenus me paraissent aberrants. Je crois avoir isolé l’origine de ce petit problème. Sauf erreur de ma part et après quelques tests et vérifications, il me semble que tu n’as pas intégré dans ta fonction VBA, qui pour moi est incompréhensible, que mes exercices ne sont pas des années civiles, mais débutent le 01/09 pour se terminer le 30/08. En conséquence, si dans une des 400 feuilles en colonne P3:P112, j’ai la date par exemple du 15/09/13, l’intitulé du thème (Q3:Q112) doit être comptabilisé avec l’exercice 2013-2014 (cellule E4 de « Recap » et non avec 2012-2013 (cellule C4 de « Recap »).
D’avance, merci beaucoup.
Bonne soirée.
bolide7

[antonysansh]

Bonjour bolide7,

Petites explications :
Public Const NomFeuilleRecap = "Recap" est une constante qui est une chaine de caractères représentant le nom de ton onglet recap.
Ensuite je parcours toute les feuilles sauf la feuille recap :

Code :

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

1
2
For Each sh In ThisWorkbook.Sheets
        If sh.Name <> NomFeuilleRecap Then

Donc si d'autres feuilles ne doivent pas être a parcourir, il va falloir adapter.

Ensuite je n'avais pas vu

Mes exercices vont du 01/09 au 31/08

Donc par exemple avec 2012-2013 en C4 je comptais toutes les lignes de 2012 ET de 2013 (années complètes)

Apres t = Split(Exercice, "-"), t(0) vaut 2012 et t(1) vaut 2013.
Donc DateSerial(t(0), 1, 1) représente le 01/01/2012 (DateSerial(aaaa, mm, jj) construit la date jj/mm/aaaa)



En résumer voila une adaptation de la fonction :

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
Public Const NomFeuilleRecap = "Recap"
 
Function MaFonction2(ByVal Theme As Range, ByVal Exercice As Range) As Long
    Dim sh As Worksheet, PlageTheme$, PlageExercice$, res&, t$()
    PlageTheme = "Q3:Q112"
    PlageExercice = "P3:P112"
    t = Split(Exercice, "-")
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> NomFeuilleRecap Then _
            res = res + Application.WorksheetFunction.CountIfs( _
                sh.Range(PlageExercice), ">=" & CDbl(DateSerial(t(0), 9, 1)), _
                sh.Range(PlageExercice), "<=" & CDbl(DateSerial(t(1), 8, 31)), _
                sh.Range(PlageTheme), Theme)
    Next
    MaFonction2 = res
End Function

[bolide7]

Bonjour antonysansh,

SENSATIONNEL !
Une réponse rapide et en plus avec des explications claires, que j’ai compris.
Que dire de plus, même si quelques ajustements doivent encore être faits.


En effet, il me semble qu’il y a encore quelques résultats aberrants.
Tu me dis :

Code :

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

Ensuite je parcours toute les feuilles sauf la feuille recap.

Si je l’avais précisé sur la discussion du forum Excel, je ne l’ai pas précisé sur cette discussion du sous-forum « Macros et VBA Excel ».
En effet, mon classeur contient plus de 400 feuilles identiques, dont les noms sont des nombres compris :
- Entre 14000 et 14999.
- Entre 28000 et 28999.
- Entre 76000 et 76999.
- Entre 94000 et 94999.
Mais, il contient aussi une vingtaine d’autres feuilles, dont le nom n’est pas un simple numéro de 5 chiffres, comme par exemple :
- « Chiffre d’affaires ».
- « Onglet 1 ».
- « Modèle100 ».
Pour éviter que ta fonction VBA ne perde son temps à collecter des informations dans ces feuilles, qui ne font pas partie des 400 feuilles identiques, il serait peut-être judicieux de limiter la recherche aux feuilles :
- Dont le nom est un nombre de 5 caractères.
Ou
- Dont le nom est compris dans les 4 espaces précédents (de 14000 à 14999, de 28000 à 28999, etc.).
Je pense que cette seconde possibilité est beaucoup plus compliquée à mettre en œuvre.
Je t’invite à aller au plus simple.
Mille excuses pour cet oubli, qui manifestement est important.


Il y a également un autre petit problème, qui n’est même peut-être pas lié à la fonction VBA.
Je vais tenter de t’expliquer le plus clairement possible ce que je constate dans le tableau récapitulatif :
- Dans la colonne A, celle des thèmes, les intitulés des thèmes vont aujourd’hui de A9:A23 et de A25:A39.
En prévision d’autre thèmes, les cellules A24 et A40:A77 sont pour l’instant vide.
Comme dans ces cellules vides, il n’y a pas de thème, les cellules des différents exercices affichent à juste titre la valeur « 0 », sauf pour la colonne de l’exercice 2014-2015 (colonne G) :
- La cellule G24 affiche la valeur « 3 ».
- Les cellules G40:G77 affichent également la valeur « 3 ».
J’ignore totalement l’origine de ce petit problème, qui concerne uniquement :
- Les cellules vides de la colonne A (cellules sans thème).
- Les cellules de la colonne G (affichage d’un « 3 » en en G24 et G40:G77).

Je ne sais pas si cela peut avoir son importance, mais cet exercice (2014-2015) est le dernier renseigné pour l’instant dans les 400 feuilles.

Après plus d’une heure de recherche, j’ai fini par trouver l’origine de ce petit problème concernant l’affichage d’un « 3 », au lieu d'un « 0 ».
La liste des thèmes, qui sont en colonne A :
- Est évolutive.
- Se met à jour automatiquement avec une fonction, à partir d’une autre liste, qui se trouve dans une autre feuille de mon classeur et qui alimente à un menu déroulant.
Voilà cette fonction pour la première cellule de la colonne A, la cellule A9 :

Code :

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

=SI(INDIRECT("'Menu déroulant 2'!"&ADRESSE(LIGNE(G6);COLONNE(G6)))=0;"";INDIRECT("'Menu déroulant 2'!"&ADRESSE(LIGNE(G6);COLONNE(G6))))

Serait-il possible d’intégrer dans ta fonction VBA la présence de cette fonction en colonne A :
- Qui perturbe ta fonction VBA.
- Mais que je souhaite conserver pour la mise à jour automatique des thèmes de la colonne A ?


Mille excuses pour la longueur de ce post.
D’avance, merci beaucoup pour ces 2 ajustements.
Bon après-midi.
bolide7

[antonysansh]

Bonjour bolide7,

Plus besoin Public Const NomFeuilleRecap = "Recap"

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Function MaFonction3(ByVal Theme As Range, ByVal Exercice As Range) As Long
    Dim sh As Worksheet, PlageTheme$, PlageExercice$, res&, t$()
    PlageTheme = "Q3:Q112"
    PlageExercice = "P3:P112"
    t = Split(Exercice, "-")
    For Each sh In ThisWorkbook.Sheets
        If sh.Name Like "#####" Then _
            res = res + Application.WorksheetFunction.CountIfs( _
                sh.Range(PlageExercice), ">=" & CDbl(DateSerial(t(0), 9, 1)), _
                sh.Range(PlageExercice), "<=" & CDbl(DateSerial(t(1), 8, 31)), _
                sh.Range(PlageTheme), Theme)
    Next
    MaFonction2 = res
End Function

Pour l'histoire du 3 pas de solution, je me perds dans toutes tes explications.
C'est quoi la formule en colonne G ? Elle est bien complètement indépendante da ma formule car je dans la formule c'est les colonne P et Q qui sont utilisées et dans la formule tu ne mets que la cellule C4 et la colonne A.

S'il faut afficher 0 pour les lignes ou la colonne A est a 0 (résultat de ta formule), ajoute

Code :

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

If Theme = 0 then Exit Function

juste après la ligne qui commence par Dim

[bolide7]

Bonjour antonysansh,

Manifestement, je n’ai pas été clair.
Je pense que tu confonds la colonne G du tableau récapitulatif et la colonne G des 400 feuilles, qui n’est absolument pas concernée par le sujet.
Reprenons, dans le tableau récapitulatif, j’ai :
- En colonne A les thèmes.
- En colonne C le nombre de thème pour l’exercice 2012 - 2013.
- En colonne E le nombre de thème pour l’exercice 2013 - 2014.
- En colonne G le nombre de thème pour l’exercice 2014 - 2015.
C’est cette colonne, qui pose problème avec des « 3 », lorsque les cellules de la colonne A sont vides, mais contiennent en réalité une fonction EXCEL.

Malgré 2 heures de tests dans tous les sens je ne suis pas arrivé à intégrer correctement ta proposition dans mon classeur.
Que faut-il faire ?
Supprimer ou pas MaFonction2 dans le module 2 ?
Mettre MaFonction3 dans le second ou dans un troisième module ?
Remplacer ou pas dans la cellule C9 du tableau récapitulatif la formule =MaFonction2(A9;$C$4) par =MaFonction3(A9;$C$4) ?

Cela devrait peut-être aller mieux quand j’aurai ces 3 réponses.

D’avance, merci beaucoup.
bolide7