Discussion :

[ozaland]

Bonjour à tous,

j'ai une macro qui permet en fonction d'un nombre L25 et d'un niveau H17 de récupérer les données correspondantes aux deux critères présentes sur une autre feuille.
si le nombre n'existe pas, la macro fait une moyenne de ses encadrants.
une fois les données récupérer, la macro permet également de modifier la quantité, cela se répercute sur les trois autres données.
pour le moment cette macro est opérationnelle sur une seule ligne.
je vous joins mon fichier, il sera plus explicite.

je me demande si c'est possible de faire une macro qui fait la même chose mais sur plusieurs lignes (nombre de lignes variable) afin de travailler en masse, avec sur chaque ligne un nombre et niveau différent.
quelqu'un peut il me donner son avis sur la faisabilité de cette macro??
merci

[MattChess]

Etre plus précis pour décrire le problème

La description du problème est par endroit fortement floue :
"un nombre L25" : traduire par un prix.
"une autre feuille" : cela ne coûtait pas beaucoup de dire son nom "Perf"
"encadrant", "trois autres données" : ?
"la macro" : on aurait pu donner son nom Worksheet_Change(ByVal Target As Range)
ainsi que celui de la feuille de calcul "travail à la référence".

Eviter de joindre votre .xls

je vous joins mon fichier, il sera plus explicite.

Précisez que le .xls a été passé à l'anti-virus et qu'il ne comporte pas de macros qui se déclenche à l'ouverture.
Joindre un .xls dans un premier post est problématique car l'ouvrir est toujours un risque dans le cas où il y aurait un virus.
Cela explique l'absence de réponse jusqu'à maintenant.
Voir la note en tête du forum : Important : Fichier joint dans vos discussions.

Copier-coller votre code VBA

Il vaut mieux copier-coller le code entre les balises dédiées [CODE] ... [/CODE] et indiquer comment le faire fonctionner, quels sont les paramètres d'entrée et le résultat attendu. Cela force l'auteur à réduire la complexité du problème de façon à le rendre compréhensible.

Revue de code et points d'amélioration

Avant d'analyser le problème, listons les points à améliorer : la procédure Worksheet_Change() fait plus de 60 lignes sans aucune constante nommée. Le code présente une indentation de marge réduite, des constantes littérales et numériques dupliquées, des noms de variables ultra courts et un double système de référence des cellules Range("H17") et Cells(row, column). La difficulté est l'inversion row / column dans l'approche Alphabétique des colonnes à éviter. C'est la référence Cells(row, column) qu'il faudra à terme généraliser car plus flexible et plus performante même si elle peut paraître plus longue à écrire.

Comment résoudre le problème ?

Il faut considérer le code suivant plus comme un point de départ particulièrement au niveau de la convention de codage et de la structuration plutôt qu'une solution opérationnelle. Car le code sera à mettre au point selon votre nouvelle organisation de la feuille de calcul.

Typiquement pour réduire la complexité du problème on aurait pu dès le départ aligner le paramètre de niveau sur la même ligne que le prix pour la généralisation sur plusieurs lignes car actuellement le niveau en H17 n'est pas du tout sur la même ligne que le prix en L25 !

1. Déclarez des constantes VBA pour décrire la feuille Excel

La première chose à faire est de décrire les deux feuilles de calcul cible et source avec des constantes nommées.
Dans Feuil1 "travail à la référence" :

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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
Option Explicit
Const levelUnknown As Byte = 0
Const levelEmpty As Byte = 1
Const levelLow As Byte = 2
Const levelAverage As Byte = 6
Const levelHigh As Byte = 10
 
' Sheet target: "Travail à la référence"
Const rowPriceBegin = 25
Const rowPriceEnd = 30
 
Const colCaHt12mg = 8 ' H
Const colQtSell12 = 11 ' K
Const colPrice = 12 ' L
Const colLevel = 14 ' N
 
' Sheet source: "Perf"
Const strSheetPerf = "Perf"
Const rowSliceMin = 3
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngTmp As Range
 
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Range("H17,F25"), Target) Is Nothing Then
        Application.EnableEvents = False
        FrameSearch rowPriceBegin, LevelParse(Range("H17")), Val(Range("L25"))
        Application.EnableEvents = True
    ElseIf Not Intersect(Range("K25"), Target) Is Nothing Then
        rngTmp = Target
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            .Undo
        End With
        Range("H25") = Range("H25") * rngTmp / Target
        Range("I25") = Range("I25") * rngTmp / Target
        Range("J25") = Range("J25") * rngTmp / Target
        Target = rngTmp
        Application.EnableEvents = True
    End If
End Sub
 
Function LevelParse(ByVal strLevel As String) As Byte
    Select Case strLevel
    Case "bas"
        LevelParse = levelLow
    Case "moyen"
        LevelParse = levelAverage
    Case "haut"
        LevelParse = levelHigh
    Case ""
        LevelParse = levelEmpty
    Case Else
        LevelParse = levelUnknown
    End Select
End Function
 
Sub FrameSearch(ByVal rowPrice As Integer, ByVal levelCl As Byte, ByVal price As Integer)
Dim rngFindPrice As Range
 
    If levelCl = levelEmpty Then
        FrameClear rowPrice
    ElseIf levelCl <> levelUnknown Then
        Set rngFindPrice = Worksheets(strSheetPerf).Range("A:A").Find(price, _
            LookIn:=xlValues, lookat:=xlWhole)
        If rngFindPrice Is Nothing Then
            FrameAverage rowPrice, levelCl, price
        Else
            FrameCopy rngFindPrice.Row, rowPrice
        End If
    End If
End Sub
 
Sub FrameClear(ByVal indRow As Integer)
    Range(Cells(indRow, colCaHt12mg), Cells(indRow, colQtSell12)).ClearContents
End Sub
 
Sub FrameAverage(ByVal rowPrice As Integer, ByVal levelCl As Byte, ByVal price As Integer)
Dim indRow As Integer, indCol As Integer
 
    Application.StatusBar = CStr(rowPrice) + " : tranche de prix inexistante," + _
        "la moyenne des encadrants a été effectuée"
    With Worksheets(strSheetPerf)
        For indRow = rowSliceMin To .Range("A" & Rows.Count).End(xlUp).Row
            If .Cells(indRow, 1) > price Then
                For indCol = 0 To 3 ' Moyenne des encadrants
                  Cells(rowPrice, colCaHt12mg + indCol) = _
                    (.Cells(indRow - 1, levelCl + indCol) + _
                     .Cells(indRow, levelCl + indCol)) / 2
                Next
                Exit For
            End If
        Next
    End With
End Sub
 
Sub FrameCopy(ByVal indRow As Integer, ByVal rowPrice As Integer)
Dim strRangeSrc As String
 
    Select Case levelCl
    Case levelLow
        strRangeSrc = "B" & indRow & ":E" & indRow
    Case levelAverage
        strRangeSrc = "F" & indRow & ":I" & indRow
    Case levelHigh
        strRangeSrc = "J" & indRow & ":M" & indRow
    Case Else
        Exit Sub
    End Select
    Worksheets(strSheetPerf).Range(strRangeSrc).Copy _
        Destination:=Range(Cells(rowPrice, colCaHt12mg), Cells(rowPrice, colQtSell12))
End Sub
 
Sub FrameAll()
Dim indRow As Integer, indCol As Integer, levelCl As Byte, price As Integer
 
    Application.EnableEvents = False
    For indRow = rowPriceBegin To rowPriceEnd
        levelCl = LevelParse(Cells(indRow, colLevel))
        price = Val(Cells(indRow, colPrice))
        FrameSearch indRow, levelCl, price
    Next
    Application.EnableEvents = True
End Sub

On notera les rangées et colonnes importantes à décrire dans la nouvelle organisation de la feuille avec le niveau par exemple dans la dernière colonne libre colLevel. La généralisation sur plusieurs lignes se fera entre rowPriceBegin et rowPriceEnd.

2. Appel de FrameSearch()

La macro initiale Worksheet_Change(ByVal Target As Range) a été divisée par trois en nombre de ligne. On a conservé au maximum le contexte en limitant les modifications. On a extrait la recherche du prix FrameSearch() pour pouvoir la réutiliser dans un context multiligne. On ne s'est intéressé qu'à la 1ere partie Then entre le basculement de Application.EnableEvents. La structuration dans une Sub telle que FrameSearch() permet d'identifier immédiatement les deux paramètres d'entrée le niveau et le prix.

Auparavant, il faut décoder la String du niveau avec LevelParse() qui transforme la String niveau en un type de niveau afin d'éviter les répétitions des constantes littérales "bas", "moyen", "haut" plusieurs fois dans le module.

3. Définition de FrameSearch()

Une fois le code extrait dans la procédure FrameSearch(), il faut la rendre potentiellement réutilisable sur plusieurs lignes d'où le 1er paramètre rowPrice.

Les procédures FrameSearch(), FrameClear(), FrameAverage(), FrameCopy() sont courtes. Elles tiennent sur un seul écran pour faciliter la mise au point et conserver une indentation de marge classiquement avec des TAB. A défaut de commentaires, les noms des procédures servent à la documentation.

Les variables sur une ou deux lettres telles que I, J, Cl ont été bannies car elles sont difficiles à remplacer par un nom de variable plus parlant. Il faut pouvoir immédiatement reconnaître s'il s'agit de rangée ou de colonne.

FrameCopy() sera à améliorer car le style de colonne Alphabétique persiste malgré la factorisation dans strRangeSrc alors qu'il faudrait n'utiliser que Cells(row, column) comme pour le Range dans FrameClear().

4. FrameAll() sur plusieurs lignes

je me demande si c'est possible de faire une macro qui fait la même chose mais sur plusieurs lignes (nombre de lignes variable) afin de travailler en masse, avec sur chaque ligne un nombre et niveau différent.

Finalement la généralisation sur plusieurs lignes se fait dans une courte procédure FrameAll() organisée autour d'un For Next par rangée.

En conclusion, décrivez vos feuilles de calcul avec des constantes nommées puis découpez le problème en plusieurs procédures de tailles réduites plutôt qu'une seule longue procédure monolithique.