Discussion :

[titi83]

Bonjour,

Nouveau sur ce forum, je cherche depuis un moment comment faire pour avoir une feuille récapitulative qui me listerait automatiquement les dates entrées sur toutes les feuilles, ainsi que les montants et les nom des feuilles correspondants à ces dates.

Je m'explique, il s'agit de gérer des articles mis en dépôt-vente : 1 fiche = 1 mise en dépôt avec les articles et les Prix de vente déjà renseignés.

Lorsqu'un article est vendu, on rempli la date sur la fiche et ça met à jour automatiquement le récapitulatif avec la date de vente des articles, le total des prix de vente et les numéros des fiches concernées.

J'ai essayé beaucoup de choses les SOMMEPROD, les SOMME.SI.ENS, les formules matricielles, la fonction DECALER..., mais je n'y arrive pas.. J'ai également essayé des bouts de code en VBA avec un compteur (genre ActiveCell.Value = Sheets(i).Name,...) mais je ne peux récupérer que la liste des feuilles..

Bref, un fichier sera beaucoup plus parlant qu'un long discours (j'ai rempli la feuille "Recap" "à la main" avec les résultats attendus afin de mieux visualiser..)

Merci de me donner un petit coup de pouce.

[gFZT82]

Bonjour,

Voici une proposition avec VBA.
On utilise l’évènement SheetChange du classeur afin de détecter la modification d’une cellule contenant une date dans une des fiches.

Code :

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

1
2
3
4
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Sh.Name <> "Recap" And Target.Column = 2 Then Recapituler
End Sub

Ensuite, on lance la procédure "Recapituler" pour effectuer un relevé des dates, un comptage des PV et un relevé du nom des fiches.

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
 
Sub Recapituler()
Dim Ws As Worksheet
Dim MonDico, k
Dim n As Long, LigneC As Long
Dim C As Range
Dim F As String, FC As String
Dim Cptr As Double
    Application.ScreenUpdating = False
    Set MonDico = CreateObject("Scripting.dictionary")
    With Worksheets("Recap")
        'On balaye toutes les feuilles, exepté "Recap", afin de relever toutes les dates (sans doublon)
        For Each Ws In Worksheets
            If Ws.Name <> "Recap" Then
                For Each C In Ws.Range("B4:B" & Ws.Range("B" & Rows.Count).End(xlUp).Row)
                    If Not MonDico.Exists(C.Value) And C.Value <> "" Then
                        MonDico.Add C.Value, C.Value
                    End If
                Next C
            End If
        Next Ws
        'On efface les données dans la feuille "Recap"
        .Range("A2:C" & .Range("A" & Rows.Count).End(xlUp).Offset(1).Row).ClearContents
        'On balaye chaque date du dictionnaire
        k = MonDico.keys
        For n = 0 To MonDico.Count - 1
            LigneC = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
            .Range("A" & LigneC) = k(n)
            'On balaye toutes les feuilles, exepté "Recap"
            For Each Ws In Worksheets
                If Ws.Name <> "Recap" Then
                    'A chaque date relevée, on associe le nom de la fiche et on cumule les PV
                    For Each C In Ws.Range("B4:B" & Ws.Range("B" & Rows.Count).End(xlUp).Row)
                        If C.Value = k(n) Then
                            F = Ws.Name
                            Cptr = Cptr + C.Offset(0, 1)
                        End If
                    Next C
                    If F <> "" Then FC = FC & " - " & F
                    F = ""
                End If
            Next Ws
            'On écrit les résultats dans la feuille "Récap"
            .Range("B" & LigneC) = Cptr
            .Range("C" & LigneC) = Right(FC, Len(FC) - 3)
            Cptr = 0
            FC = ""
        Next n
    End With
    Set MonDico = Nothing
End Sub

Cordialement.

[titi83]

Merci gFZT82 pour ce code, c'est bien ce que je voulais et c'est sympa d'avoir mis les commentaires, tous ne le font pas hélas, mais c'est primordial car ça m'a permis de comprendre beaucoup de choses…
Afin d'avoir le résultat directement, j'ai lancé la macro à l'ouverture de la feuille "Recap".

Afin de peaufiner mon tableau, j'ai rajouté des colonnes dans la feuille "Recap" et affecté les résultats là où je le voulais.

Dans chaque Fiche, j'ai rajouté 2 commissions (qui peuvent être différentes sur chaque fiche), et qui sont déduites du PV pour avoir le PV final.
J'ai bien créé les variables, et leur affichage dans le tableau, mais je n'arrive pas à me positionner sur la cellule F1 et F2 de chaque fiche pour récupérer la valeur de Commission1 et Commission2.
J'ai essayé avec Offset, mais ça ne fonctionne que pour la première ligne de la date trouvée dans la fiche...

J'ai essayé d'obtenir cette valeur avec :
Range ("F1").Select
cells(1,6).Select


J'ai aussi essayé avec une variable :

Dim TauxCom1 is Double
.
.
.

TauxCom1 = Range ("F1").Value
msgbox TauxCom1 pour tester la valeur


J'ai essayé d'insérer ce TauxCom1 partout dans le code, y compris dans "For Each Ws In Worksheets", afin que le calcul se fasse une fois dans la feuille et dans chaque feuille pour récupérer les bonnes commissions, mais la valeur retournée est toujours la cellule "F1" de la feuille "Recap" ou alors "-1"
Du coup, j'ai abandonné cette variable (à tort peut-être) car je veux que cela calcule à la suite de "PV = PV + C.Offset(0, 1)", donc je me suis dit que j'était au bon endroit pour faire le calcul directement, mais ça coince...


Serait-il possible, également, d'afficher les quantités vendues à telle date ? J'ai déjà créé la variable "Qte", mais je ne sais pas comment compter les occurrences de dates.
Sur certains forums, il y a des solutions, mais le code que j'ai trouvé me semble très (trop) long pour faire juste cela, sachant qu'il y a dèja, sans doute dans votre code, de quoi calculer ça facilement…

Je joins le fichier retravaillé.

En tout cas, merci pour votre aide, je découvre le VBA dans Excel, le code permet de faire tellement de choses, et j'ai hâte de comprendre mes erreurs..

Bonne journée.

[gFZT82]

Bonjour,


Dans le code ci-dessous, j'ai rajouté Ws. aux instructions Range("F1").Value et Range("F2").Value
afin d'indiquer à quelle feuille ces instructions s'appliquent.

Pour la quantité, il a suffi d'ajouter la ligne Qte = Qte + 1

Code :

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

1
2
3
4
5
6
7
8
9
10
                    For Each C In Ws.Range("B4:B" & Ws.Range("B" & Rows.Count).End(xlUp).Row)
                        If C.Value = k(n) Then
                            F = Ws.Name
                            PV = PV + C.Offset(0, 1) 'Affichage Cumul des PV
                            Com1 = Com1 + C.Offset(0, 1) * Ws.Range("F1").Value 'Affichage Cumul des Commissions 1
                            Com2 = Com2 + C.Offset(0, 1) * Ws.Range("F2").Value 'Affichage Cumul des Commissions 2
                            PVfin = PV - Com1 - Com2   'Affichage Cumul des PV - Cumuls des Commissions
                            Qte = Qte + 1 'Comptage des quantités
                        End If
                    Next C

Cordialement.

[titi83]

Super pour la modif, ça tenait à pas grand chose et tout fonctionne, merci encore.



Sur la même base, je l'ai adaptée sur un modèle de factures :

Les articles sont facturés, mais je veux pouvoir lister les ventes correspondantes à chaque facture, en fonction des dates de vente.

Donc exactement la même chose que précédemment avec les fiches de dépôt-vente.

Tout fonctionne mais mon problème vient du cumul des frais de port.
Le port est calculé de la manière suivante sur les factures : PORT = (PVHT+TVA) x T% de port -> cellule "O6"

Certaines factures peuvent ne pas avoir de port du tout, et il peut y avoir un port différent sur chaque facture.

Tout va bien lorsque le taux de port est le même sur toutes les factures, mais quand le taux est différent ou qu'il n'y a pas de port sur une facture, le cumul n'est pas bon.
En fait, je pense qu'il garde en mémoire la valeur de la cellule "O6".

Le plus simple pour s'en rendre compte, est de mettre par exemple du port sur une seule facture et une seule même date de vente.

J'ai essayé d'affecter une variable à "O6" et de la remettre à 0 ensuite, afin de la réinitialiser, mais ça ne fonctionne pas.

Je pense que l'erreur vient de mon calcul mais j'ai essayé dans tous les sens (parenthèses, intégrer les formules de PVHT et TVA directement dans le calcul...) mais je n'ai jamais le bon cumul dans la colonne PORT.
A moins que le pointeur ne se trouve pas au bon endroit ? ...

Merci pour votre aide précieuse.

[gFZT82]

Bonjour,

En toute logique, j'aurais codé

Code :

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

PORT = PORT + (PVHT + TVA) * Ws.Range("O6").Value

Cordialement.

[titi83]

Bonjour,

J'avais aussi essayé cette formule, mais le cumul n'est pas bon.

Si : :

FA01 = 0%
FA02 = 1%
FA03 = 0%
Alors qu'il n'y a pas de port dans FA01, il le prend en compte quand même au taux de FA02 qui elle a du port.
Donc, il cumule FA01 à 1%, FA02 à 1% et FA03 à 0%.

Si :

FA01 = 1%
FA02 = 1%
FA03 = 1%
Le cumul est supérieur au résultat attendu.

La seule fois où le cumul est bon, c'est lorsqu'il n'y a pas de port sur aucune facture.

A un moment donné, il semble garder en mémoire une valeur et il ne réinitialise pas, mais où ?

Merci.

Bonne journée.

[gFZT82]

Effectivement, le calcul ne doit pas se faire avec PVHT et TVA qui représentent un cumul

Code :

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

PORT = PORT + C.Offset(0, 8) * (1 + Ws.Range("O4").Value) * Ws.Range("O6").Value 'Affichage Cumul des PORT

A+

[titi83]

Ok, effectivement je comprends mieux où ça coinçait...

Super en tout cas, merci pour tout..

Bonne soirée.

[titi83]

Bonjour,

Tout fonctionne comme prévu, mais j'ai juste un problème de positionnement d'affichage des données.

Afin de faire une mise en page différente de la feuille "VENTES", je voudrais commencer à afficher les données à partir d'une autre cellule que "A2".

Je suppose qu'il faut jouer avec les OFFSET, je suis arrivé à déplacer les données mais cela faut sauter des lignes également.

J'ai essayé de me positionner sur une cellule dans le code, avant l'affichage, mais rien à faire..

Merci de votre aide.

Bonne soirée.

[gFZT82]

Bonjour,

On détermine le numéro de la ligne où doit s'effectuer l'ajout avec l'instruction

Code :

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

LigneC = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row

En clair, on se positionne sur la derniere cellule de la colonne A, soit Range("A" & Rows.Count) puis on remonte la colonne jusqu'à trouver la première cellule renseignée, la ligne d'ajout étant la ligne suivante obtenue avec Offset(1).

Si ton tableau est déplacé en colonne I, pour déterminer le numéro de la ligne où doit s'effectuer l'ajout, il suffit d'écrire

Code :

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

LigneC = .Range("I" & Rows.Count).End(xlUp).Offset(1).Row

Ensuite, il faut indiquer les changements de colonne dans les autres instructions

Code :

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

1
2
3
4
 .Range("I" & LigneC) = k(n)
 .Range("J" & LigneC) = QTE
 .Range("K" & LigneC) = PVHT
etc.

Pense également à corriger l'instruction d'effacement du tableau

Code :

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

.Range("I7:O" & .Range("I" & Rows.Count).End(xlUp).Offset(1).Row).ClearContents

Cordialement.

[titi83]

Bonjour gFZT82,

Merci encore, avec tes explications détaillées, ça va mieux, je vais tester tout ça...


Bonne journée

Cordialement