question sur VBA EXCEL

[caroline24]

svp je suis debutante en vba excel et j'ai un exercice à faire , si quelqu'un peut m'aider c'est urgent

o Colonne A : Code PRODUIT
o Colonne B : Code VENDEUR
o Colonne C : N° de département géographique (français)
o Colonne A : Le Chiffre d'Affaires CA
La feuille SOUS_TOTAUX est initialement vierge.

• Question n°1
• Le traitement VBA Excel qui est demandé est une recopie des données de la feuille BASE vers la feuille SOUS_TOTAUX (titres de colonnes inclus) mais en ne prenant pas en compte les lignes de CA <=100.

• Question n°2
• Le traitement VBA Excel qui est demandé est l'application de l'assistant de sous-totalisation (Cf menu "Données/Sous-totaux..." sous Microsoft Excel) sur la feuille SOUS_TOTAUX.

La sous-totalisation demandée sommera les CA pour les différents produits (3 sous-totaux car 3 produits différents dans le jeu d'essai).

Pour cette question, le recours à l'enregistrement automatique est accepté mais il faudra toutefois veiller à ce que votre procédure puisse être utilisée sur une plage de données plus importante en nombre de lignes.


• Question n°3
• Le traitement VBA Excel qui est demandé ici est encore l'application de l'assistant de sous-totalisation sur la feuille SOUS_TOTAUX.

La sous-totalisation demandée sommera les CA pour les différents produits (3 sous-totaux car 3 produits différents dans le jeu d'essai) et pour les vendeurs (deuxième niveau de totalisation).

Pour cette question, le recours à l'enregistrement automatique est accepté mais il faudra toutefois veiller à ce que votre procédure puisse être utilisée sur une plage de données plus importante en nombre de lignes.


• Question n°4
• Le traitement VBA Excel demandé est identique à celui de la question n°2. Par contre, le recours à l'enregistrement automatique de la commande de menu "Données/Sous-totaux..." n'est pas autorisé.

Le traitement devra être effectué par une approche "algorithmique", c'est à dire qu'une fois que les données seront triées ( selon la colonne A), il faudra :
o parcourir verticalement les données et s'arrêter dès que l'on change de produit
o insérer une ligne blanche et y intégrer la totalisation (qui aura été préparée préalablement)
o ..

• Question n°5
• Le traitement VBA Excel demandé consiste à exporter le contenu de la feuille BASE sous la forme d'un fichier ASCII délimité par des points virgules. Le fichier généré sera nommé BASE.txt.

La procédure sera nommée QUESTION5.
• Question n°6
• Le traitement VBA Excel demandé assurera les opérations suivantes :
o Appel de la procédure QUESTION1
o Tri des données selon le code produit
o Mise en place de sous-totaux (par l'intermédiaire de l'assistant "Données/Sous-totaux...")
o Compression du plan
o Création d'un histogramme présentant le CA de chaque produit et le CA total

 

[caroline24]

Re : question sur VBA EXCEL

Bonsoir Excel-lent ,
merci pour ta rectification , voilà j'ai pu l'employer sur mon exercice ,
mais comment faire avoir des lignes juste pour les resultats

Sub Question4()
'
' Question4 Macro
'
' Touche de raccourci du clavier: Ctrl+Maj+X
'
Columns("A:A").Select
ActiveWorkbook.Worksheets("SOUS_TOTAUX").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SOUS_TOTAUX").Sort.SortFields.Add Key:=Range("A1") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("SOUS_TOTAUX").Sort
.SetRange Range("A224")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
j = 0
k = 0
l = 0

For i = 1 To 22
If Range("A" & i) = "P1" Then
j = Range("C" & i) + j
ElseIf Range("A" & i) = "P2" Then
k = Range("C" & i) + k
ElseIf Range("A" & i) = "P3" Then
l = Range("C" & i) + l
End If
Next i

Range("E" & 9) = j
Range("E" & 15) = k
Range("E" & 22) = l
Range("E" & 1) = "total CA/produit"
End Sub

 

[Excel-lent]

Re : question sur VBA EXCEL

Bonsoir Caroline,

Je vais scinder ma réponse en plusieurs messages, ainsi si tu es dans le coin tu pourras commencer à avancer pendant que je continue les explications.

Tout d'abord, bravo pour ta macro, tu as bien avancé et en plus toute seule.

Reprenons point par point l'ennoncé :

Le traitement devra être effectué par une approche "algorithmique", c'est à dire qu'une fois que les données seront triées ( selon la colonne A),

Donc dans un premier temps nous allons demander à excel de trier les données. Pour ce point, il autorise l'enregistreur de macro. Donc allons y gaiement

-> lancer l'enregistreur de macro
-> sélectionner l'onglet "SOUS_TOTAUX"
-> sélectionner les colonnes A à D
(nous aurions pu sélectionner à la place les CELLULES A1 à D24, mais dans ce cas lorsque le tableau sera plus grand, la fin du tableau ne sera pas triés)
-> cliquer sur "données" puis "trier..."
-> tout en bas, bien s'assurer que pour la question "ma plage de données a une ligne de titres", la case "oui" soit coché
-> premier trie : sélectionner dans la liste déroulante "produit"
-> arréter l'enregistreur de macro

On obtient le code suivant :

Sub Question4()
' Touche de raccourci du clavier: Ctrl+Maj+X

    Sheets("SOUS_TOTAUX").Select
    Columns("A:D").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End Sub

Ces lignes remplacent avantageusement toutes ces lignes :

Sub Question4()
' Touche de raccourci du clavier: Ctrl+Maj+X

Columns("A:A").Select
ActiveWorkbook.Worksheets("SOUS_TOTAUX").Sort.Sort Fields.Clear
ActiveWorkbook.Worksheets("SOUS_TOTAUX").Sort.Sort Fields.Add Key:=Range("A1") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("SOUS_TOTAUX").Sort
.SetRange Range("A224")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

Je te laisse lire cela pendant que je rédige la suite.

A tout à l'heure

 

[Staple1600]

Re : question sur VBA EXCEL

Bonsoir à tous

J'ai cru comprendre qu'il fallait avoir fini pour lundi matin.

Voici mon interprétation de la question 4 (qui ne respecte pas tout à fait les consignes demandées, comme cela tu seras obligée toi de t'y conformer pour que l'exercice soit valable )

Sub QUESTION4()
InsertionLigneVide
AjoutSOMME
End Sub

Private Sub InsertionLigneVide()
Dim DebutLig As Long, i As Long
DebutLig = 2
i = DebutLig + 1
    While Cells(i, "A") <> ""
        If Cells(i, "A") <> Cells(i - 1, "A") Then
        Rows(i).EntireRow.Insert
        i = i + 1
        End If
    i = i + 1
    Wend
End Sub

Private Sub AjoutSOMME()
Dim Zone As Range
For Each Zone In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Areas
Cells(Zone.Row + Zone.Rows.Count, 4).Value = Application.Sum(Range(Cells(Zone.Row, 4), Cells(Zone.Row + Zone.Rows.Count - 1, 4)))
Next Zone
End Sub

Si tu as des questions, n'hésites pas (bien qu'elles soient jusqu'alors peu nombreuses pour une débutante en VBA )

EDITION: Bonsoir Excel-Lent, caroline24
Désolé, je n'avais pas rafraichi, j'en étais resté au message de Chti160 (que je salue au passage), je n'avais donc pas vu le code de caroline24 (qui, je viens donc de le voir, avait bien avancé )

 

[Excel-lent]

Re : question sur VBA EXCEL

Re-bonsoir,

Pour la suite, tu as bien appliqué nos différents conseils et collé avec la question du prof.

Tu balaye les cellules une par une, grâce à l'utilisation de ta boucle For i = 1 To 22 .... Next i

Tu as préparé la totalisation (qui était à faire au préalable).

Juste quelques petits conseils/astuces pour t'améliorer :

1/ Bien penser à définir tes variables au début de la macro (cf. message de Jean Marie n° 24 et mon message n° 25)

2/ En écrivant For i = 1 To 22, tu analyse les lignes 1 à 22.
Or :
-> la ligne 1 contient ta ligne de titre, donc pas la peine de perdre du temps à l'analyser (tout en sachant que cela ne gène en rien ta macro : elle fonctionne très bien).
-> la ligne 22 est la dernière de ton tableau, donc ta macro va bien analyser ton tableau dans son intégralité, mais ton prof indique dans l'ennoncé que la taille de ton tableau peut varier!

Donc pour que ta macro puisse gérer ton tableau dans tous les cas de figure il faut :
-> à la place du chiffre 22 mettre une variable.
-> au début de la macro (avant ta boucle For... To... Next) chercher la dernière ligne de ton tableau et la mettre dans cette variable

Pour cette partie tu connais déjà la méthode, car Robert ta donné la solution dans son message n° 7. Pour reprendre sa méthode :

Au tout début de la macro, définir la variable qui contiendra le numéro de la dernière ligne du tableau :

Dim dl As Long 'déclare la variable dl (Dernière Ligne)

Donc si tu regarde le lien donné précédemment (liste des différents types de variable - perso j'ai mis ce lien dans mes liens favoris ) tu verra qu'une variable de type Long indique que ta variable dl contiendra un nombre entier compris entre -2 147 483 648 et 2 147 483 647.

Très largement suffisant car il faut savoir que :
-> jusqu'à la version 95 (7.0) excel compté 16 384 lignes
-> jusqu'à la version 2003, excel compte 65 536 lignes
-> la version 2007 et 2010 compte 1 048 576 lignes

Ensuite, toujours comme indiqué par notre ami Robert dans le même post, pour trouver le numéro de la dernière ligne d'un tableau

dl = Sheets("SOUS_TOTAUX").Cells(Application.Rows.Count, 1).End(xlUp).Row

(mais évidement il existe d'autres méthodes, mais je ne veux pas te noyer sous une pléthore de solutions différente).

Ensuite, donc pour ta boucle il suffira d'écrire :

For i = 2 To dl

...
Bonne lecture, je continue pendant que tu lis et étudie cela.

 

[Excel-lent]

Re : question sur VBA EXCEL

Re,

(je salue au passage mon ami Staple1600 )

Donc pour résumer, actuellement ta macro donne ceci :

Sub Question4()
' Touche de raccourci du clavier: Ctrl+Maj+X
' Dans un premier temps on défini toutes les variables

Dim i as     ' je te laisse rajouter après le "as" le type de variable approprié ;)
Dim j as     ' je te laisse rajouter après le "as" le type de variable approprié ;)
Dim k as     ' je te laisse rajouter après le "as" le type de variable approprié ;)
Dim dl As Long

' Nous trions les données du tableau situé sur l'onglet "SOUS_TOTAUX"

    Sheets("SOUS_TOTAUX").Select
    Columns("A:D").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

' Nous cherchons la dernière ligne du tableau
   dl = Sheets("SOUS_TOTAUX").Cells(Application.Rows.Count, 1).End(xlUp).Row

' Nous calculons au PREALABLE (avant d'insérer les lignes) comme demandé dans l'énnoncé les différents sous-totaux
    For i = 2 To dl
        If Range("A" & i) = "P1" Then
             j = Range("C" & i) + j
        ElseIf Range("A" & i) = "P2" Then
             k = Range("C" & i) + k
        ElseIf Range("A" & i) = "P3" Then
             l = Range("C" & i) + l
        End If
   Next i

' Maintenant il faut insérer une ligne vide après chaque changement de produit et y insérer le sous-total

End Sub

A tout à l'heure

 

[Staple1600]

Re : question sur VBA EXCEL

Re


Si je ne m'abuse c'est les sous-totaux de la colonne D qu'il faut réaliser (si on se base sur le fichier exemple), c'est à dire la colonne CA.

 

[Excel-lent]

Re : question sur VBA EXCEL

Re,

Comme tu as pu le remarqué, j'ai repris à l'identique ta partie de macro calculant les sous-totaux. Car elle est juste, fonctionne et répond à l'ennoncé du problème.

Mais il faut savoir que si ton tableau contient d'autres produits que P1, P2 et P3 ta macro ne pourras pas traiter ces nouveaux produits (mais j'ai relu ton sujet plusieurs fois, à aucun moment ton prof indique qu'il y aura d'autres produits - sauf erreur).

Maintenant, étant débutant en VBA, comment faire pour insérer une ligne?

-> lancer l'enregistreur de macro
-> sélectionner une ligne (dans mon exemple la ligne 11)
-> insérer une ligne
-> arrêté l'enregistreur de macro

Donc la macro obtenu insére une ligne entre la ligne 10 et la ligne 11. Voici la macro obtenu :

Sub InsérerUneLigne()
    Rows("11:11").Select
    Selection.Insert Shift:=xlDown
End Sub

La macro sélectionne la ligne 11 : Rows("11:11").Select
Puis la macro insère une ligne en dessous de la ligne 10 : Selection.Insert Shift:=xlDown

Avec l'habitude, tu apprendras à améliorer les codes données par l'enregistreur de macro. Par exemple là, excel sélectionne la ligne 11 (comme demandé). Or il faut savoir que cela prend du temps et n'est pas obligatoire.

De plus tu veux rajouter une ligne au dessus de la ligne 11, pourquoi écrire 11 deux fois alors qu'une seule suffit?

Donc les personnes qui pratique couramment le VBA, à la place de toutes ces lignes, écrirons :

Sub InsérerUneLigne()
    Rows(11).Insert
End Sub

Tu peux essayer les deux codes, tu verras que le résultat est le même.

Et voilà, tu as ton bout de code (l'un ou l'autre au choix) qu'il te reste plus qu'à intégrer dans ta nouvelle partie de macro qui répondra à la question suivante :

o parcourir verticalement les données et s'arrêter dès que l'on change de produit
o insérer une ligne blanche et y intégrer la totalisation

A touteeee

 

[Staple1600]

Re : question sur VBA EXCEL

Re

Allo Excel-Lent, tu n'as pas vu ma précédente remarque ? (cf précédent message)
(Toi aussi tu rafraichis pas assez souvent )
Je te félicite au passage pour tes talents de pédagogue que je suis loin de posséder

caroline24:
Toujours pas de question à propos de mon dernier code soumis ?

En se basant sur le dernier code d'Excel-lent en le mixant avec un bout du mien
Il ne te reste plus qu'à insérer les totaux

Sub Question4BIS()
' Touche de raccourci du clavier: Ctrl+Maj+X
' Dans un premier temps on défini toutes les variables
Dim DebutLig As Long, ii As Long

Dim i 'as     ' je te laisse rajouter après le "as" le type de variable approprié ;)
Dim j 'as     ' je te laisse rajouter après le "as" le type de variable approprié ;)
Dim k 'as     ' je te laisse rajouter après le "as" le type de variable approprié ;)
Dim l
Dim dl As Long

' Nous trions les données du tableau situé sur l'onglet "SOUS_TOTAUX"

'    Sheets("SOUS_TOTAUX").Select
'    Columns("A:D").Select
'    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
'        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
'        DataOption1:=xlSortNormal

' Nous cherchons la dernière ligne du tableau
  dl = Sheets("SOUS_TOTAUX").Cells(Application.Rows.Count, 1).End(xlUp).Row

' Nous calculons au PREALABLE (avant d'insérer les lignes) comme demandé dans l'énoncé les différents sous-totaux
   For i = 1 To dl
   Select Case Range("A" & i).Value
   Case Is = "P1"
   j = Range("D" & i) + j
   Case Is = "P2"
   k = Range("D" & i) + k
   Case Is = "P3"
   l = Range("D" & i) + l
   End Select
   Next i
MsgBox j ' pour test
MsgBox k ' pour test
MsgBox l ' pour test

' Maintenant il faut insérer une ligne vide après chaque changement de produit et y insérer le sous-total
DebutLig = 2
ii = DebutLig + 1
    While Cells(ii, "A") <> ""
        If Cells(ii, "A") <> Cells(ii - 1, "A") Then
        Rows(ii).EntireRow.Insert
        ii = ii + 1
        End If
    ii = ii + 1
    Wend
End Sub

PS: J'ai substitué au If /End IF un Select Case /End Select (question de gout personnel)

 

[Excel-lent]

Re : question sur VBA EXCEL

Re,

Excellente remarque Staple1600

J'ai donc corrigé ci-dessous la macro de Caroline en remplaçant les Range("C"...) par Range("D"...)

Merci à toi pour ton aide

Sub Question4()
' Touche de raccourci du clavier: Ctrl+Maj+X
' Dans un premier temps on défini toutes les variables

Dim i As Currency
Dim j As Currency
Dim k As Currency
Dim dl As Long

' Nous trions les données du tableau situé sur l'onglet "SOUS_TOTAUX"

    Sheets("SOUS_TOTAUX").Select
    Columns("A:D").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

' Nous cherchons la dernière ligne du tableau
  dl = Sheets("SOUS_TOTAUX").Cells(Application.Rows.Count, 1).End(xlUp).Row

' Nous calculons au PREALABLE (avant d'insérer les lignes) comme demandé dans l'énnoncé les différents sous-totaux
   For i = 2 To dl
        If Range("A" & i) = "P1" Then
             j = Range("D" & i) + j
        ElseIf Range("A" & i) = "P2" Then
             k = Range("D" & i) + k
        ElseIf Range("A" & i) = "P3" Then
             l = Range("D" & i) + l
        End If
   Next i

' Maintenant insérerons une ligne vide après chaque changement
' de produit et insérons le sous-total préparé au préalable
Range("D" & dl + 1) = l
   For i = dl To 2 Step -1
        If Range("A" & i) <> Range("A" & i - 1) Then
            Rows(i).Insert
            If Range("A" & i - 1) = "P1" Then
                Range("D" & i) = j
            ElseIf Range("A" & i - 1) = "P2" Then
                Range("D" & i) = k
            End If
        End If
   Next i

End Sub

PS. 1 : Staple1600, excuse moi de ne pas avoir pris ta remarque très judicieuse en compte plus rapidement. C'est en grande partie dû au faite que la pédagogie ça prend énormément de temps! A cela rajouté le temps passé à testé un minimum les codes que j'écris, puis me relire 2-3 fois. Histoire d'enlever au moins les plus grosses fautes d'orthographe.

PS. 2 : Pour une fois, pour la dernière partie de macro je t'ai mis la solution brute de décoffrage et mis aucune explication, trop vanné pour ça. Mais si tu as des questions, n'hésite surtout pas. Il n'y a pas de questions bête, juste des réponses "idiotes"

 

[Excel-lent]

Re : question sur VBA EXCEL

Re-bonsoir Caroline,

Comme tu pourras le remarquer, la macro ci-dessus est longue. Mais elle a le mérite d'utiliser des fonctions, codes basique facile à comprendre / à reproduire / à expliquer à son prof.

Mais sinon tu peux très bien prendre le code de Staple1600, s'il n'est pas trop compliqué pour ton niveau de VBA. Tout en sachant que Staple1600 se tient à ta disposition pour répondre à tes questions et t'aider à adapter le code à ton cas concret

Bonne semaine à tous

 

[Staple1600]

Re : question sur VBA EXCEL

Re

Il manque pas le P3 dans ta dernière boucle ?

D'ailleurs, cela me turlupine cette double boucle ..
(Tu me diras, je fais pas mieux avec mes deux procédures pour une seule question)

Va falloir qu'on creuse la question

PS: Excel-lent: j'attends toujours les questions de caroline, qui si elles ne viennent pas, me plongeront dans une grande perplexité car d'habitude, le débutant en VBA a beaucoup de questions a poser

 

[Excel-lent]

Re : question sur VBA EXCEL

Salut Staple1600,

Il faut la pardonner, elle n'a que 21 ans et à 23H50 elle était invisible mais bien présente. Faut se mettre à sa place, avec la tonne d'informations que je lui ai donné d'un coup, ça en fait des choses à assimiler. Même si à priori elle semble déjà en connaître en bonne partie.

Concernant P3, pour pas me casser la tête, je l'ai mis AVANT la boucle

Range("D" & dl + 1) = l

J'ai préféré laisser deux boucles car l’énoncé du problème indiquais clairement :

o insérer une ligne blanche et y intégrer la totalisation (qui aura été préparée préalablement)

Donc la première boucle fait les sous-totaux.

Et seulement APRES, on insère des lignes blanche et intégrons la totalisation

Mais vu l'heure tardive et la tonne d'explication que j'ai mis, peut-être que je n'ai pas suffisamment lu l'énoncé?

Au plaisir de te recroiser sur le forum

 

[Staple1600]

Re : question sur VBA EXCEL

Re

Concernant P3, pour pas me casser la tête, je l'ai mis AVANT la boucle

Code vba:
Range("D" & dl + 1) = l

Houps (encore un coup de ces mauvais yeux que sont les miens)
Faut dire à ma décharge, que 1 et l sont très proches visuellement dans VBA

Et toujours pas de caroline, je suis inquiet
Je vais mal dormir cette nuit et je serai chafouin demain matin.

 

[Excel-lent]

Re : question sur VBA EXCEL

Hello,

j'attends toujours les questions de caroline, qui si elles ne viennent pas, me plongeront dans une grande perplexité car d'habitude, le débutant en VBA a beaucoup de questions a poser

si si je le suis toujours devant vous ^^" , la vérité c'est que j'ai lu un cours VBA pour m’initier a la syntaxe , c'est pourquoi j'ai pu comprendre ce que vous avez programmé

Bref, par rapport à toi, elle est novice, mais elle semble avoir déjà quelques bases solide, au moins suffisant pour comprendre mes codes basiques, et tes codes complexe

(je profite de l'occasion pour saluer tes codes comme il se doit : respectueusement ).

Par contre j'ai peur qu'elle ne soit à même de pouvoir les reproduire/adapter. Or pour progresser, je pense qu'il vaut mieux utiliser un code simple qu'on comprend et qu'on complique au fur et à mesure, plutôt que de partir directement d'un code pour "expert". Risque d'être noyé, démotivé.

De plus apprendre les codes complexes et ne pas avoir les bases, cela peut-être dangereux.

Au plaisir JM

 

[Excel-lent]

Re : question sur VBA EXCEL

toujours pas de caroline, je suis inquiet
Je vais mal dormir cette nuit et je serai chafouin demain matin.

Ne soit pas inquiet mon grand regarde sa fiche, elle était présente au moins jusqu'à 23H56

Je suis sûr qu'elle te fera un gros poutou demain matin

lol

Bonne nuit, fait de jolies rêves, plein de VBA