Calcul d'une valeur sous Conditions dans plusieurs onglet

**Gillou55** · 17/05/2019, 21h56

Bonjour,
je travail sur un Fichier comportant de nombreux onglets
52 pour les semaines (S01 a S52)
5 Récapitulatifs et graphique
3 onglets de données et liste-up

Dans un l'onglet Recap je cherche a calculer le nombre de fois qu'apparait un type de séance pour un nom donné dans les 52 semaine (S01 a S52).

Et la je suis en galère complète toutes les formules que j'essaie me renvoie du #Ref ou #Valeur (sommeprod somme.si etc...)

J'ai essayer d'aller sur divers Forum d'aide et a chaque fois ne n'arrive pas a trouver la solution.

Javais déjà réalisé ce fichier mais trop lourd et trop lent a cause des redondances de calcul d’où le faire évoluer , je suis loin d’être un expert, j'ai quelques notions, Mais je n'arrive pas a trouver de solution a ce problème.

Ci dessous le fichier joint de mom projet .

Un bon coup de main serait le bienvenu

Merci d'avance

**ARTURO83** · 19/05/2019, 01h56

Bonjour,

Voici une proposition avec:
-Création de 53 semaines
-Ajout d'un tableau à partir de la colonne "DS" de chaque semaine et reprenant toutes les séances et avec les totaux (par formules) par semaine et par séances.
-Ajout d'un bouton dans la feuille "Recap" qui synthèse tous les résultats (en VBA).

Le code

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
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
Option Compare Text
 
Sub Recap()
    Dim DerLig_f1 As Long, DerCol_f1 As Long
    Dim i As Long, f As Long, s As Long, e As Long
    Dim NbEq As Long, NbSe As Long
    Dim Seance As String, Equide As String, Sem As String
    Dim f1
 
    Application.ScreenUpdating = False
    Set f1 = Sheets("Recap")
    f1.Range("C6:N100").ClearContents
 
    DerLig_f1 = f1.[B1000].End(xlUp).Row
    DerCol_f1 = f1.[B5].End(xlToRight).Column
 
    NbEq = 100 'DerLig_f1 - 5 'Nombre d'équidés
    NbSe = DerCol_f1 + 122 'Nombre de séances augmenté du nombre de colonnes qui les précèdent
 
    ReDim Equid(NbEq, NbSe) As String
    ReDim Seanc(NbEq, NbSe) As String
    ReDim Tot(NbEq, NbSe) As Long
    For e = 6 To NbEq 'les équidés
        If f1.Cells(e, "B") = "" Then Exit For
        Equide = f1.Cells(e, "B")
        For s = 3 To DerCol_f1 'les séances
            Seance = f1.Cells(5, s)
            Tot(e, s) = 0
            For f = 1 To Sheets.Count 'les feuilles
                Sem = Sheets(f).Name 'les semaines
                Select Case Sem
                    Case Is = "S01", "S02", "S03", "S04", "S05", "S06", "S07", "S08", "S09", _
                             "S10", "S11", "S12", "S13", "S14", "S15", "S16", "S17", "S18", "S19", _
                             "S20", "S21", "S22", "S23", "S24", "S25", "S26", "S27", "S28", "S29", _
                             "S30", "S31", "S32", "S33", "S34", "S35", "S36", "S37", "S38", "S39", _
                             "S40", "S41", "S42", "S43", "S44", "S45", "S46", "S47", "S48", "S49", _
                             "S50", "S51", "S52", "S53"
 
                        Set Eq = Sheets(Sem).Columns("B").Find(Equide, LookIn:=xlValues, Lookat:=xlWhole) 'Recherche equidé
                        Set Se = Sheets(Sem).Rows(5).Find(Seance, LookIn:=xlValues, Lookat:=xlWhole)  'Recherche séance
                        If Not Eq Is Nothing And Not Se Is Nothing Then
                            Equid(e, s) = Equide
                            Tot(e, s) = Tot(e, s) + Sheets(Sem).Cells(Eq.Row, Se.Column)
                        End If
                End Select
            Next f
        Next s
    Next e
 
    'Restitution dans tableau "Recap"
    For e = 6 To NbEq
        For s = 3 To DerCol_f1
            If f1.Cells(e, "B") = Equid(e, s) Then f1.Cells(e, s) = Tot(e, s)
        Next s
    Next e
 
    Set f1 = Nothing
End Sub

Avec le fichier
Pièce jointe 476187

Cdlt

**Philippe Tulliez** · 19/05/2019, 09h21

Bonjour ARTURO83,
Pour le Select Case, plutôt que d'énumérer les noms de S01 à S53 je proposerais Case "S01" To "S53"

Code :

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

1
2
3
4
5
6
7
8
9
Sub t()
  Dim sht As Worksheet
  For Each sht In ThisWorkbook.Worksheets
    Select Case UCase(sht.Name)
       Case "S01" To "S53"
         sht.Cells(1, 1).Value = "Yes"
    End Select
  Next
End Sub

J'en profite pour signaler à Gillou55 que s'il modifiait l'organisation de ses données des 53 semaines en ne seule feuille ce serait plus simple à gérer. C'est une des règles de base d'une bonne organisation des données

**Gillou55** · 20/05/2019, 17h35

Merci a vous
j'ai pris connaissance du code que vous me proposez et je l'ai testé sur mon fichier actuel, pour l'instant tout a l’air de fonctionner.
Je vous remercie encore pour toutes les aides précieuses que vous fournissez sur ce site .
Même si je décroche souvent dans les explications

et j'en aurais surement pas fini de vous ennuyer avec des questions...
Vus ce que l'on me demande d’améliorer sur le fichier que l'on utilise dans notre asso :p

A bientôt.

**Gillou55** · 15/07/2019, 20h11

Bonjour,

Me revoilà, pour 2 petites questions concernant ce code, qui fonctionne correctement a un détail prés.

- Donc mon fichier est quasiment finalisé (enfin je crois

... ) mais lorsque je lance le code il apparaît que parfois il se met à pédaler dans le vide"
Il donne l'impression de tourner en boucle , sans pouvoir plus rien faire sur l'appli sauf Ctrl Alt Supr ...
Cela aurait tendance a ce produire quand le mode de calcul du classeur se trouve sur Automatique.
Avec de temps en temps ce message incompatibilité de type a cet endroit du code

Code :

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

1
2
3
4
5
 ReDim Tot(NbEq, NbSe) As Long
    For e = 6 To NbEq 'les équidés
        If f1.Cells(e, "B") = "" Then Exit For
        Equide = f1.Cells(e, "B")
        For s = 3 To DerCol_f1 'les séances

- D’où ma seconde question : y a t’il moyen d’intégrer, soit dans un coin de la feuille de calcul du code ou dans un pop-up un genre de compteur qui ferait dérouler les feuilles (S01 puis S02 ......) qu'il analyse, pour montrer que le code se déroule correctement ?
Et si oui comment faire ?

Merci d'avance pour votre aide

**ARTURO83** · 16/07/2019, 06h32

Bonjour

Quelle est la valeur de f1.Cells(e, "B") au moment du plantage?

Quelle est la valeur de e au moment du plantage?

Pourriez-vous déposer le fichier (sans données confidentielles) afin que je puisse regarder?

Cdlt

**Gillou55** · 16/05/2020, 19h41

Bonjour,

Je reviens sur ce Post et le code fournit par Arturo83.
cela fonctionne correctement,on l'utilise depuis pas mal de temps maintenant. "Le seul problème est la lenteur du fichier mais au vu du poids qu'il fait, ca parait logique (faudrait que je l'optimise un jour)"
On a juste un soucis ponctuellement et je n'arrive pas a trouver l'origine du problème, la seule solution consiste a remettre la sauvegarde journalière précédente.

Une erreur s'affiche parfois toujours sur cette ligne 43:

Tot(e, s) = Tot(e, s) + Sheets(Sem).Cells(Eq.Row, Se.Column)

d’où ma question, quel pourrait être l'origine de cette erreur sachant que théoriquement toutes mes feuille semaine sont identique
et sinon quel sont les moyens pour suivre le code VBA quand il s’exécute pour voir sur quel onglet il plante et a quel endroit car franchement j'ai beau regarder je ne trouve rien de choquant.

Merci pour 'laide

**ARTURO83** · 17/05/2020, 08h27

Bonjour,

10 mois se sont écoulés et vous n'avez pas répondu à ma question, y avez-vous répondu vous même?

Quant au problème de lenteur, c'est un peu normal, vu la quantité de formules qui s'y trouve sur chaque feuille, c'est sûr que de ce côté là, vous pouvez essayer de l'optimiser.

Pour rechercher l'erreur, mettez un point d'arrêt sur le "Next"
Pièce jointe 566123

Cdlt

**Philippe Tulliez** · 17/05/2020, 10h38

Bonjour,
Je suis entièrement d'accord avec la remarque d'ARTURO83, ce n'est pas très encourageant de parler dans le vide.

Une erreur s'affiche parfois toujours sur cette ligne 43:

Code :

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

Tot(e, s) = Tot(e, s) + Sheets(Sem).Cells(Eq.Row, Se.Column)

Je ne sais pas si le problème que vous rencontrez provient de là mais dans la ligne mentionnée le classeur parent de Sheets(Sem) n'est pas définit donc si le classeur actif n'est pas ThisWorkbook, cela provoquera inévitablement une erreur (erreur dont vous n'avez mentionné ni le n°, ni le texte)

**Gillou55** · 17/05/2020, 17h10

Je pensais y avoir répondu au moment , car en fait il n'y avait pas de probleme sur le code c'est juste le poids du fichier qui donnait l'impression de tourner dans le vide, en fait il falait etre juste patient...

Pour le probleme rencontré en ce moment voici le message

Erreur d'execution '13'
Incompatibilité de type

J'ai mis un arrêt sur le next comme dit et le code s’arrête sur le next F lorsque cela bug.
j’ai relancé plusieurs fois la macro sans rien modifier mais bizarrement cela ne se produit pas tout le temps.
J'ai cherché page par page (ca aussi cest tres long...) et je ne vois pas ou le probleme pourrait etre.

**ARTURO83** · 17/05/2020, 17h27

Ajoutez des espions pour chaque variable susceptible de générer l'erreur, comme sur la vidéo ci-dessous, vous finirez bien par trouver où se situe le problème.
Puis observez leurs valeurs dans la fenêtre "Espion", après chaque appui sur la touche F5 (ou clic sur le triangle en haut de la feuille, comme sur la vidéo)

Pièce jointe 566204

**Gillou55** · 17/05/2020, 22h20

Merci,

j'ai procédé comme vous m'avez dis avec les espions et je pense avoir trouvé... il y avait un petit problème caché, certaines colonnes sont masquée automatiquement si elles sont vide.
Et j'ai l'impression que cela me provoque l'erreur, je n'aurais pas pensé que le fait de masquer des colonnes puissent faire cela je croyais qu'elle étaient prise en compte quand même dans les calculs.
j'ai fait plusieurs essais et ça a l'air d’être bon.

J'ai résolu le problème en mettant une petite largeur de colonne plutôt que de masquer.

Une de mes prochaines étape sera surement d'optimiser et de faire subir une cure amincissement a mon fichier, je suivrais les conseils

J'en profite pour signaler à Gillou55 que s'il modifiait l'organisation de ses données des 53 semaines en ne seule feuille ce serait plus simple à gérer. C'est une des règles de base d'une bonne organisation des données

.
Mais la je ne sais pas trop comment faire, je vous demanderais surement conseil.

Encore Merci a l'équipe.

Calcul d'une valeur sous Conditions dans plusieurs onglet

Excel

Discussions similaires

Partager

Partager