Discussion :

[Spamev]

Bonjour à tous,

J'aimerais développer un outil excel pour l'analyse des top costs au sein de mon service et je rencontre quelques problèmes, peut-être pourrez-vous m'aider ?

Description du projet :

• 2 feuilles dans le classeur :
  
  • Feuille 1 : Ma base de données qui contient plusieurs colonnes (A=acheteur ; B=fournisseur ; C=CA)
  • Feuille 2 : l'interface utilisateur
  
  
• L'interface utilisateur se dessine comme ceci :
  
  • Une cellule dans laquelle on rentre le nom de l'acheteur (on l'appellera Cx)
  • Un bouton macro pour lancer la moulinette
  • Un tableau de 10 lignes en dessous pour montrer les 10 plus gros fournisseurs

Mes problèmes :

• La macro :
  
  • Ce que j'ai fait : J'ai tenté de faire une macro simple à l'aide d'un copier/coller. C'est à dire que je lance l'enregistrement, je copie ma cellule Cx, je change de feuille, je vais dans l'onglet tri de la colonne acheteur, je colle et je valide. Ensuite je vais sur la colonne CA et je trie par ordre décroissant.
  • Problème : la macro me copie l'unique nom que j'avais écris dans la cellule Cx, dès que je change le contenu de la cellule on en revient au nom avec lequel j'ai enregistré la macro.
  
  
• Le tableau :
  
  • Je souhaite faire un tableau qui fait directement appel aux 10 premières lignes du tableau de la base de données trié après passage de la macro. Or, il m'est impossible de sélectionner une cellule dans l'absolu (je ne parle pas du signe $). Par exemple je souhaite recopier les cellules 1 à 10 de la feuille 1, tout en sachant que leur contenu change dès qu'on change le nom de l'acheteur

Quelqu'un peut-il me donner une piste de travail ? J'ai passé 4h ce matin à retourner le web pour trouver une solution cependant rien ne me permet d'avancer à l'heure actuelle...

Merci par avance !

Maxime

[Menhir]

Je pense que tu te compliques la vie. Ce que tu veux faire est possible avec un simple filtre :
Bandeau Données > Filtre
Une fois le filtre installé, il te permet en trois clics de ne faire apparaitre que les lignes d'un acheteur donné (ou de plusieurs si tu le souhaites) et deux autres clics pour trier les fournisseurs (je suppose que c'est par valeur de CA), voir même (en deux clics) de n'afficher que les 10 ayant le plus gros CA.

Et ça t'offrira une foultitude d'autres possibilités que n'apporterait pas ta macro.

[had4789]

Je ne sais pas si je te comprends bien mais je vais faire de mon mieux.

Premièrement peux-tu joindre un fichier et ton code.

Voici la première idée qui me vient en tête:

1°) tu copies ta feuille 1 en feuille 3
2°) tu fais un delete qui supprime les noms qui sont différents de celui indiquer dans la feuille 2
3°) tu fais un tri de ta colonne C
4°) tu recopie les lignes 2 à 11 avec un for i= 1 to 10 ( car la ligne 1 correspond au titre). Si tu veux que le tableau soit sous le bouton, tu mets A2 ou A3 dans ton range

[Spamev]

Bonjour Menhir,

Merci de ta réponse. En fait je ne me complique pas la vie, je veux simplement simplifier celle de notre service. Evidemment il est possible de tout faire à la main, cependant la base de données est mise à jour régulièrement et il y a beaucoup d'acheteurs dans le service.

Ainsi chaque semaine c'est au minimum 20 fois la démarche.

En revanche, si je fais une macro qui fait tout toute seule, je peux sortir des graphiques etc en un instant!



@Had4789 : Merci beaucoup de ton intervention. Je vois où tu veux en venir, cependant deux choses :
Comment supprimer les lignes qui ne contiennent pas le nom de l'acheteur demandé ?
Je n'ai pas compris ton point 4.


Voici ma macro actuelle :

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
Sub topcost()
'
' topcost Macro
'
 
'
    Range("B3").Select
    Selection.Copy
    Sheets("Feuil1").Select
    Range("B4").Select
    ActiveSheet.Range("$B$4:$E$24").AutoFilter Field:=1, Criteria1:= _
        "Acheteur 1"
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Feuil1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Feuil1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("E4:E24"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Feuil1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B4:E27").Select
    Selection.Copy
    Sheets("Feuil2").Select
    Range("A7").Select
    ActiveSheet.Paste
    Columns("D:D").EntireColumn.AutoFit
    Range("E7").Select
End Sub

Ci-joint un extrait du fichier
Forum - Analyse TOPCOSTS.xlsx

[Menhir]

Evidemment il est possible de tout faire à la main, cependant la base de données est mise à jour régulièrement et il y a beaucoup d'acheteurs dans le service.

Le filtre, une fois en place, ne sera pas plus compliqué à utiliser que ta macro.
Et il sera beaucoup plus simple à mettre en place (10s à tout casser).

En revanche, si je fais une macro qui fait tout toute seule, je peux sortir des graphiques etc en un instant!

Idem avec un filtre.
Tu sélectionnes toutes tes données pour en faire un graphique.
Si ensuite tu cliques pour ne faire apparaitre que les données de l'acheteur A, les autres données disparaitront aussi du graphique.

Prends la peine d'essayer (ça ne te prendra qu'une minute et ça peut te faire gagner plusieurs jours).


Un petit détail : ton fichier est un xlsx. J'espère que ta macro n'est pas dedans parce que sinon, tu risques de la perdre en quittant vu que les xlsx ne peuvent pas contenir de macro.

[had4789]

J'ai juste modifier ta feuil1 comme sur l'image.

Dis moi ce que tu en penses.

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
Sub Copie_feuil1()
        Dim WsName As String
 
            For Cpt = 1 To 1
 
        WsName = "Feuil1" & Cpt
         Sheets("Feuil1").Copy After:=Worksheets("Feuil2")
               ActiveSheet.Name = "Feuil3"
 
             Next Cpt
 
 
    End Sub
Sub Suppression()
Application.ScreenUpdating = False
Dim i As Integer
Sheets("Feuil3").Select
 
 
 
With ThisWorkbook.Sheets("Feuil3")
'Précisez le nom de votre feuille
            For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1
'je travaille sur la colonne c
'Rows.count permet de retourner le nombre de ligne de la plage range
                        If .Range("A" & i).Value <> Sheets("Feuil2").Range("B3").Value Then
                                   .Rows(i).Delete
 
                        End If
            Next i
End With
 
End Sub
 
Sub classement()
 
              Range("A2:R3000").Sort key1:=Range("D2"), order1:=xlDescending
End Sub
 
Sub xp()
 
   Sheets("Feuil3").Range("A2:D12").Copy Destination:=Sheets("Feuil2").Range("A8:S20")
 
End Sub

[Spamev]

@Had4789 :

Merci pour ta macro, alors j'ai quelques soucis :
Premièrement excel ne la veut pas en tant que telle, je suis obligé de faire 4 boutons avec chaque macro assignée dans l'ordre suivant : copie>sup>classement>xp

Ensuite cela ne fonctionne pas du tout, sur ma feuil2 j'ai un tableau à 5 lignes qui s'affiche et qui est indépendant du nom de l'acheteur que j'ai entré initalement. Par ailleurs, je ne trouve pas la cellule dans laquelle il faut entrer le nom de l'acheteur (j'ai utilisé celle que j'avais initialement prévue)

[had4789]

Premièrement: pour avoir un seul bouton tu affecte la macro si dessous à ton bouton:

Code :

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

1
2
3
4
5
Sub tout
Call Copie_feuil1
Call Suppression
Call classement
Call xp

La cellule pour entré le nom est B3 dans la feuille 2.

Cela fonctionne parfaitement chez moi. voici un peu d'aider sur mon 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
Sub Copie_feuil1()
        Dim WsName As String
 
            For Cpt = 1 To 1
 
        WsName = "Feuil1" & Cpt
        'ce code permet de créer la feuil3
         Sheets("Feuil1").Copy After:=Worksheets("Feuil2")
               ActiveSheet.Name = "Feuil3"
 
             Next Cpt
 
 
    End Sub
Sub Suppression()
Application.ScreenUpdating = False
Dim i As Integer
Sheets("Feuil3").Select
 
 
 
With ThisWorkbook.Sheets("Feuil3")
'Je sélectionne la feuille3
 
            For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1
            'je dis que si la valeur de la cellule A est différente de la cellule B3 de la feuille B2 je supprime la ligne
 
                        If .Range("A" & i).Value <> Sheets("Feuil2").Range("B3").Value Then
                                   .Rows(i).Delete
 
                        End If
            Next i
End With
 
End Sub
 
Sub classement()
'Je fais un tri décroissant
              Range("A2:R3000").Sort key1:=Range("D2"), order1:=xlDescending
End Sub
 
Sub xp()
'Je copie les cellules du tablea A2:D20 de la feuil3 dans le tableau A8:S20 de la feuille 2
   Sheets("Feuil3").Range("A2:D20").Copy Destination:=Sheets("Feuil2").Range("A8:S20")
 
 
End Sub

Il faut que tu fasses attention à ne pas masquer tes lignes.

Peux-tu joindre le fichier sur lequel tu appliques le code puis un fichier une fois le code appliquer pour que je vois un avant après.

Merci

[Spamev]

@Had4789

Ok ça fonctionne super !

En revanche une dernière petite question, sais-tu comment faire pour enchainer les recherches ?

En effet, lorsque je tape acheteur 1 > j'ai mon résultat en page 3
En revanche pour faire une recherche sur l'acheteur 2 je dois supprimer la page 3 puis relancer la macro.

Un moyen d'automatiser la suppression de la page 3 ?

En tout cas merci encore ! t'es un as !

[had4789]

Voila le code qu'il ne manque. Celui-ci va supprimer la feuille 3.

Il faut que tu rajoute call delete à la fin de ton sub tout.

Code :

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

1
2
3
4
 
sub delete
Sheets("Feuil3").delete
end sub

[Spamev]

Bon bah super ! Tout fonctionne parfaitement !!

J'avais simplement oublié un petit détail...

Certains numéros dans la colonne "objet de la commande" sont similaires, et lors de l'analyse des top costs je suis censé les cumuler (pour avoir un CA global sur les mêmes objets)... Or je actuellement je passe pas un tableau croisé pour cumuler les CA et vu le nombre de données mon PC plante !

Un moyen de rattraper le coup sans passer par un TCD tu penses ?

[had4789]

Pour éviter que ton pc ne plante tu peux ajouter la ligne Application.ScreenUpdating = False qui accélère le rapidité du calcul.

Une question bien formulée c’est 100% de la solution (rdurupt) !

Qu'elle est ta question clairement?

Tu veux après avoir sélectionner ton acheteur regrouper les objets de commande identique pour faire un cumul global des CA?

Est-ce que chaque objet à un fournisseur unique ou non?

[Spamev]

Bonjour Had,

Tu as tout à fait raison j'aurais mieux dû formuler ma question. Pour ma défense je n'avais pas fait attention à ce soucis, c'est en traitant les données au fur et à mesure que je l'ai découvert

Tu veux après avoir sélectionner ton acheteur regrouper les objets de commande identique pour faire un cumul global des CA?

C'est exactement ça ! Et effectivement chaque objet a un fournisseur unique.

J'ai transposé ta macro à mon fichier (103 000 lignes tout de même ^^) et j'ai un blocage "Erreur d'execution '6': Dépassement de capacité" et cette ligne est surlignée en jaune :

Code :

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

            For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1

J'ai fait quelques recherches sur internet et c'est apparemment cette ligne "Dim i As Integer" qui pose problème, j'ai tenté de virer integer pour le modifier mais rien de concluant du coup j'ai tout remis comme avant !

[Menhir]

Ouvre l'aide VBA Excel et tape Dim dans la zone de recherche.
L'aide VBA est généralement plus efficace que Google.

[Philippe Tulliez]

Bonjour,

J'ai fait quelques recherches sur internet et c'est apparemment cette ligne "Dim i As Integer" qui pose problème, j'ai tenté de virer integer pour le modifier mais rien de concluant du coup j'ai tout remis comme avant !

Lorsqu'un variable doit contenir un numéro de ligne elle doit être de type Long, le nombre de lignes dans excel étant supérieur à 32767.

[Spamev]

bonjour Philippe,

Effectivement j'ai essayé hier avec la variable long. Cependant le PC mouline et l'éditeur VBA semble se relance. J'interrompt donc la procédure avec echap et l'éditeur me surligne

Code :

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

End If

@Menhir : Oui Oui je suis passé par l'aide VBA de MS, cependant rien de concluant concernant dim, donc je continue mes recherches sur Google !

[Menhir]

Effectivement j'ai essayé hier avec la variable long. Cependant le PC mouline

Avec 103 000 lignes, ça n'est pas étonnant.
Place dans ton code un debug.print qui te permette de visualiser la progression de l'exécution.

[Philippe Tulliez]

Bonjour Maxime,

Effectivement j'ai essayé hier avec la variable long. Cependant le PC mouline et l'éditeur VBA semble se relance. J'interrompt donc la procédure avec echap et l'éditeur me surligne

Je n'ai pas lu quelles étaient tes critères pour exporter la liste de données mais as-tu essayé une autre méthode telle que le filtre avancé d'excel qui permet d'exporter en moins de cinq secondes des milliers de lignes suivant des critères.
Avec un peu d'expérience d'excel et une bonne analyse des questions à poser à l'utilisateur, tu peux même créer un formulaire sur une feuille d'excel où l'utilisateur construit lui même les critères en répondant aux questions.
Dans le tutoriel Les filtres avancés ou élaborés dans Excel, il y a un classeur à télécharger dans lequel il y a une feuille nommée [ControlFilters] qui illustre mes propos.

[had4789]

Bonjour,

Voici un petit code qui normalement fait ce que tu demandes.

Dis moi ce que tu en penses

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
59
60
61
62
Sub Copie_feuil3()
        Dim WsName As String
 
            For Cpt = 1 To 1
 
        WsName = "Feuil3" & Cpt
         Sheets("Feuil3").Copy After:=Worksheets("Feuil3")
               ActiveSheet.Name = "Feuil4"
 
             Next Cpt
 
 
    End Sub
Sub sum()
 
 
 
Application.ScreenUpdating = False
Dim i As Long
 
 
    'Tu vas perdre le tri de ton CA pour trier par objet
 
    Sheets("Feuil3").Range("A2:R3000").Sort key1:=Range("C2"), order1:=xlDescending
 
   Sheets("Feuil4").Select
 
With ThisWorkbook.Sheets("Feuil4")
 
            For i = .Range("C" & .Rows.Count).End(xlUp).Row To 2 Step -1
            'Je dis que si la valeur de la ligne i de la colonne C est égale à la valeur de la ligne i-1 de la colonne C alors
            'J'additionne dans la cellule i de la colonne F les valeurs de CA indiquées dans les ligne i-1 et i de la colonne E.
                        If Sheets("Feuil3").Range("C" & i).Value = Sheets("Feuil3").Range("C" & i - 1).Value Then
                                   .Range("F" & i) = Sheets("Feuil3").Range("E" & i).Value + Sheets("Feuil3").Range("E" & i - 1).Value
 
                        End If
            Next i
End With
 
End Sub
 
Sub suppdouble()
 
Application.ScreenUpdating = False
Dim i As Long
 
   'On va supprimer les doublons
 
   Sheets("Feuil4").Select
 
With ThisWorkbook.Sheets("Feuil4")
 
            For i = .Range("C" & .Rows.Count).End(xlUp).Row To 2 Step -1
            'Après notre calcul, si la valeur de la ligne i  de la colonne Cest égale à celle de la ligne i-1 de la colonne C je supprime la ligne i-1
                        If Sheets("Feuil3").Range("C" & i).Value = Sheets("Feuil3").Range("C" & i - 1).Value Then
                                 .Rows(i - 1).Delete
 
                        End If
            Next i
End With
 
End Sub

[Spamev]

@Had :

Alors ! J'ai dû modifier un peu ton code car j'avais des erreurs. Dans un premier temps à la ligne

Code :

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

Sheets("Feuil3").Range("A2:R3000").Sort key1:=Range("C2"), order1:=xlDescending

j'ai modifié "C2" en "C2:C10000" car sinon la console bloquait.

Ensuite je ne comprends pas bien cette partie :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
With ThisWorkbook.Sheets("Feuil4")
 
            For i = .Range("C" & .Rows.Count).End(xlUp).Row To 2 Step -1
            'Je dis que si la valeur de la ligne i de la colonne C est égale à la valeur de la ligne i-1 de la colonne C alors
            'J'additionne dans la cellule i de la colonne F les valeurs de CA indiquées dans les ligne i-1 et i de la colonne E.
                        If Sheets("Feuil3").Range("C" & i).Value = Sheets("Feuil3").Range("C" & i - 1).Value Then
                                   .Range("F" & i) = Sheets("Feuil3").Range("E" & i).Value + Sheets("Feuil3").Range("E" & i - 1).Value
 
                        End If
            Next i
End With

En effet, tu mentionnes les colonnes F et E, je ne comprends pas leur implication dans la formule.

Enfin la formule supprime bien les doublons mais elle ne fait pas les additions (pour l'instant ) !

Si j'arrive à trouver où ça bloque je te tiens informé !