Discussion :

[Virgtoul]

Bonjour a tous,

Je suis debutante en VBA, et ai besoin de votre aide precieuse! Apres moultes recherches sur le net je n'ai rien trouve qui marche...
Je souhaite sur un fichier:
1) inserer une colonne en G, puis copier coller une formule ("concatenenr") ecrite sur la 2e ligne de cette colonne (en colonne G) et la coller jusqu'a la derniere ligne de ce fichier,
2) trier cette meme colonne G par ordre alphabetique puis la colonne A de mon fichier par ordre chronologique, et idem, toujours en selectionnant jusqu'a la derniere ligne du fichier.
3) inserer des sous totaux par changement de nom dans ma colonne G, jusqu'a la derniere ligne de mon fichier.
J'ai reussi a enregistrer une macro sur un fichier donne avec un certain nombre de lignes, mais je voudrais que ma macro puisse s'adapter au nombre de lignes de mon fichier, ce qui je n'arrive pas a faire marcher.

Voici la macro enregistree sur mon fichier qui contient 6052 lignes (je joins le fichier a ce message, avec un onglet contenant les donnees et l'autre contenant ce que je veux obtenir).

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
' Reasonablenesstest3 Macro
'
 
'
    Range("D1").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G1").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "Name & ID"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-1],"" ("",RC[-2],"") "")"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G6052")
    Range("G2:G6052").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("G2:G6052") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("B2:B6052") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Data").Sort
        .SetRange Range("A1:K6052")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=7, Function:=xlSum, TotalList:=Array(3, 8), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub

Un grand merci d'avance de votre aide!

[Pierre Fauconnier]

Salut.

• Abandonne l'enregistreur de macros pour ce genre de travail. C'est une vraie misère, ce truc;
• Découpe ton projet en petits bouts isolés dans des procédures simples qui ne font qu'une seule chose (Déterminer la dernière ligne, ajouter une formule, trier, ajouter des sous-totaux);
• Assemble ces morceaux au sein d'une procédure générale qui les appelle les uns à la suite des autres.

En procédant ainsi, tu peux tester chaque fonction/procédure indépendamment des autres, et tu isoles chaque problème à traiter. Il est toujours plus simple, pour déplacer la montagne, de déplacer caillou après caillou

[Virgtoul]

Merci Pierre de ton retour rapide!
En revanche comme je suis maxi de chez maxi debutante l'enregistreur est bien utile pour la syntaxe tout du moins...

Y a til des e learnings bien foutus pour apprendre cette syntaxe?

ou as-tu toi meme des suggestions a me faire de lignes pour ma macro?

merci encore!

Virginie

[Pierre Fauconnier]

Il y a pas mal de tutos sur DVP, mais rien ne vaut la pratique sur un cas concret...

Avant tout, il me semble que ta fonction CONCATENATE n'est pas syntaxiquement correcte. Pourrais-tu dire ce que tu veux concaténer (en français ou en donnant la formule Excel?)

[Virgtoul]

oui, c'est justement ce que je tente de faire, pratiquer sur des cas concrets !
Mais c'est pas évident car le langage est bien spécifique!

pour ce qui est de la formule concatenate, c'est la syntaxe en anglais, et elle marche bien celle-ci - ce qu'elle fait c'est qu'elle fusionne, en colonne G, la syntaxe des cellules en colonne F et E en mettant entre parenthèse ce qu'il y a en colonne E.

Ma macro plante en revanche quand j'essaie de dérouler cette formule jusqu'à la dernière ligne. Elle bugue au niveau de la troisième ligne ci dessous:

Code :

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

1
2
3
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-1],"" ("",RC[-2],"")"")"
    Selection.AutoFill Destination:=Range("G2").End(xlDown)

des idées?

merci!

[Pierre Fauconnier]

Sur base de la première illustration ci-dessous, j'ai créé un code, très morcelé mais qui permet d'isoler chaque traitement, et qui produit une plage illustrée dans la seconde image.

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
Option Explicit
 
 
Function GetLastRow(wks As Worksheet, ColumnLetter As String) As Long
  GetLastRow = wks.Range(ColumnLetter & wks.Rows.Count).End(xlUp).Row
End Function
 
Function AddFormula(rng As Range, Formula As String)
  rng.Formula = Formula
End Function
 
Function InsertColumnBefore(rng As Range, Title As String)
  rng.Insert shift:=xlToRight
  rng(1, 0).Value = Title
End Function
 
Function SortRange(rng As Range)
  rng.Sort key1:=rng.Range("g:g"), order1:=xlAscending, key2:=rng.Range("a:a"), order2:=xlAscending, Header:=xlYes
End Function
 
Function AddSubTotals(rng As Range)
  rng.Subtotal GroupBy:=7, Function:=xlSum, TotalList:=Array(4), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Function
 
Sub Treatment(wks As Worksheet)
  Dim LastRow As Long
  Dim rng As Range
  Dim Formula As String
 
  LastRow = GetLastRow(wks, "A")
  InsertColumnBefore wks.Range("g:g"), "xxx"
 
  Set rng = wks.Range("g2:g" & LastRow)
  Formula = "=concatenate(b2,"" "",c2)"
  AddFormula rng, Formula
  SortRange wks.Range("a1:h" & LastRow)
  AddSubTotals wks.Range("a1:h" & LastRow)
End Sub
 
Sub Main()
  Treatment Feuil4
End Sub

Ce code n'a pas pas pour but de résoudre ton problème et il faudra que tu l'adaptes, a priori assez peu, notamment sur la définition de la plage finale sur laquelle porte les sous-totaux. Il faudra l'adapter à ton cas.

Il permet d'illustrer qu'en découpant ton code, tu peux isoler les traitements pour les tester séparément. Si, pour une raison ou une autre, un traitement particulier devait être modifié, il suffirait de modifier et de tester à nouveau uniquement la petite fonction qui le réalise puis, sur base d'une nouvelle plage originale, de relancer un traitement complet. Le coût de cette méthode de codage, qui est aussi son avantage, est de t'obliger à penser à toutes les étapes et de créer des fonctions atomiques qui réalisent une seule partie du traitement complet (procédure Main). En modifiant uniquement Feuil4 dans la procédure Main puis en lançant celle-ci, tu peux appliquer exactement le même traitement à une autre feuille de ton classeur.


Pour prendre un exemple: Si l'ajout des sous-totaux ne doit pas se faire avec un total sur D mais sur une autre colonne, tu adaptes uniquement le code de cette fonction. Simple et efficace.
Je sais que cela semble compliqué, voire idiot, de réaliser des fonctions qui n'ont qu'une ligne de code, mais crois-moi, c'est bien plus simple à gérer qu'un code spaghetti interminable qu'il faut relancer entièrement parce l'avant dernière ligne de traitement pose problème lors de la création du code et des tests.

Je pense que le code parle de lui-même mais si tu as besoin d'explications, je suis là. Il y a plein d'autres solutions. Ce que je souhaite ici, c'est illustrer une bonne manière d'y arriver.

[Virgtoul]

Merci encore Pierre, tu es très patient! et super rapide!
Je comprends bien la logique de ton code et ça me plait bien aussi de fonctionner comme ça, c'est propre!

J'ai essaye de le décortiquer: tu définis d'abord les fonctions puis tu lances la macro a partir de "Sub", correct?
voici mes questions:
1) lignes 4 a 6: ici on cherche a atteindre la dernière ligne du fichier, correct?
- que signifie la variable "long", c'est toi qui a décidé de la nommer ainsi?
- pourquoi dans la formule End(xlUp) et pas End(xldown) ? Puisque l'on cherche a aller jusqu'à la dernière ligne du fichier?

2) à quoi sert cette partie ci, lignes 8 a 10?

Code :

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

1
2
3
Function AddFormula(rng As Range, Formula As String)
  rng.Formula = Formula
End Function

3) ligne 18: la colonne A je souhaite la trier par ordre chronologique, est-ce que c'est la même formule que celle par ordre alphabétique?

Code :

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

key2:=rng.Range("a:a"), order2:=xlAscending

4) ligne 40: à quoi servent ces lignes?

Code :

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

1
2
3
Sub Main()
  Treatment Feuil4
End Sub

merci encore!

Je pense avoir à peu près compris le reste

[Pierre Fauconnier]

En général, j'essaie de donner des noms parlants à mes fonctions et mes variables.

Lignes 4 à 6: correct. Ici, la fonction s'appuie sur la colonne A, mais tu pourrais, dans cette fonction, modifier pour t'appuyer sur une autre colonne.

.End(xlUp) Simule le CTRL+Flèche vers le haut. Permet, en partant de la dernière ligne de la feuille (65536 jusque XL2003, 1048576 depuis XL2007) de remonter jusqu'à la dernière ligne utilisée dans la colonne. Ca permet de ne pas s'arrêter sur une cellule vide lorsque tu descends à partir de la ligne 1. Si tu es certain que ta colonne est remplie sur chaque ligne du tableau, tu peux utiliser .End(xlDown) à partir de la 1, mais c'est un risque d'erreur.

Lignes 8 à 10: Cette fonction remplit la plage rng (la colonne G bornée sur le tableau, soit G2 jusqu'à la dernière ligne utilisée) avec la formule que tu lui passe. A nouveau, une ligne pour une fonction, certains vont trouver cela stupide, mais si, par la suite, tu dois par exemple réaliser un collage spécial valeurs pour fixer les données ou autres, tu le fais alors à l'intérieur de cette fonction, sans toucher au reste du code. Ça te permet aussi de tester la fonction sur des plages de test avec des formules de test. C'est un des intérêts du découpage en fonctions isolées.

Ordre chronologique croissant : xlAscending, décroissant = xlDescending. Le tri est chronologique puisqu'il s'appuie sur une colonne qui contient des dates.

Sub Main() permet de lancer le tout. cette procédure permet de démarrer toute la procédure sans devoir passer de paramètres. Tu pourrais par exemple utiliser un bouton qui lancerait cette procédure au départ d'une feuille de calcul. Tu aurais pu te passer de cette procédure et utiliser Traitment, mais tu n'aurais pu alors lancer cette procédure qu'en manuel via la fenêtre d'exécution en VBA (CTRL+G si elle n'est pas visible).

[Virgtoul]

ok, merci encore!

J'ai essaye de lancer la macro en faisant 2-3 modifs pour l'adapter a mon cas et:
1) je ne trouve pas dans la liste la premiere macro definie dans le premier sub



2) il n'y a que la 2e macro definie par "main" mais celle-ci elle ne reconnait pas la feuille Data qui pourtant s'appelle bien comme ca dans mon fichier.



est-ce que je fais quelque chose d'incorrect a un moment donne?

merci!

[Pierre Fauconnier]

Tu ne verras effectivement que Main et c'est normal

Tu ne peux pas utiliser Data, en tout cas de cette manière. Tu dois utiliser Sheet2 (nom VBA de la feuille) ou bien Worksheets("Data") (Nom Excel de la feuille)

Tu pourrais aussi, dans le projet VBA, sélectionner Sheet2(Data) et en dessous, dans la feuille de propriétés, modifier (Name) et l'appeler par exemple shData. Tu pourras alors utiliser Treatment shData.

Désolé. J'aurais dû te préciser cette subtilité.