Convertir range en tableau

**hehee** · 31/05/2013, 20h15

Bonjour à tous,
J'ai cru comprendre en fouillant dans certains forum, qu'il est beaucoup plus rapide de prendre une base de données dans une feuille Excel, et de la mettre en tableau. Cependant, je n'ai pas trop saisi le truc. (Le comment faire). Car pour le moment, j'ai un code qui prend plus de 5 heures à rouler. Je compare et fait des recherches de 150 000 lignes à 30 000 autres.

Mon code est très gros, et je n'ai pas réussi à le réduire. Mais l'utilisation des tableaux pourrait peut-être m'aider. Comment faire ?

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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
Sub LT_Viole()
 
Dim ligne_commande As Long
Dim ligne_viole, nb_somme As Long
Dim derniere_modif, prochaine_modif, ligne_transaction As Long
Dim nb_transaction As Long
Dim ligne_transaction_somme, i As Long
Dim Progression As Double
Dim somme, somme1 As Double
Dim trouver As Integer
Dim derniere_cellule As Long
Dim Article As Range
 
'Trouve la dernière cellule des Lead Times violés
derniere_cellule = Sheets("LT violé").Range("A1048576").End(xlUp).Row
 
'Compte le nombre de transactions
nb_transaction = Sheets("Transactions").Range("F1").CurrentRegion.Rows.Count
 
Progression = 0
ligne_transaction_somme = 3
ligne_viole = 3
derniere_modif = 3
prochaine_modif = 3
'Cherche pour toutes les transactions
 
'Parcours toutes les transactions d'une feuille
For ligne_transaction = prochaine_modif To nb_transaction
    somme = 0
    somme1 = 0
    nb_somme = 1
    trouver = 0
    ligne_transaction_somme = ligne_transaction
    Sheets("Transactions").Cells(ligne_transaction, 2) = "NON"
 
    'S'il n'y a pas d'article, le passe
   If IsEmpty(Sheets("Transactions").Cells(ligne_transaction, 6)) Then
        GoTo Prochaine_transaction
 
    End If
 
 
    'Si aucune correspondance est trouvée dans la feuille "LT violé", c'est que forcément il n'a pas de d'influence, alors on le passe
   Set Article = Sheets("LT violé").Range("C3:C" & derniere_cellule).Find(Sheets("Transactions").Cells(ligne_transaction, 6), , xlValues, xlPart)
    If Article Is Nothing Then
        GoTo Prochaine_transaction
    End If
 
 
 
        'Calcule la première ligne
       'Val est utilisé, car il peut arriver qu'il y a une lettre (Erreur de la part des employés)
       somme = somme + Val(Sheets("Transactions").Cells(ligne_transaction_somme, 10))
 
    'Permet de compter la somme des transactions d'une même pièce, d'une même journée.
   'Il est important de déterminer si le total est positif ou négatif.
   Do
 
        'Cherche un article correspondant
       'Regarde si c'est la même date
       'Si c'est pas la même date, sort du Do.
       If Sheets("Transactions").Cells(ligne_transaction_somme, 6) = Sheets("Transactions").Cells(ligne_transaction_somme + 1, 6) And _
        Sheets("Transactions").Cells(ligne_transaction_somme, 9) = Sheets("Transactions").Cells(ligne_transaction_somme + 1, 9) Then
            somme = somme + Val(Sheets("Transactions").Cells(ligne_transaction_somme + 1, 10))
 
        Else
            prochaine_modif = ligne_transaction_somme + 1
            Exit Do
 
        End If
 
        'Nb__Somme permet de trouver le nombre d'articles identiques de la même journée
       nb_somme = nb_somme + 1
        ligne_transaction_somme = ligne_transaction_somme + 1
 
    '10000000000 est un chiffre arbitraire, car on se servira jamais de cette ligne de commande
   Loop Until ligne_transaction_somme = 10000000000#
 
 
    'Si la somme est <0, c'est qu'il y a des lignes qui ont probablement causées un requis au système.
   If somme < 0 Then
 
        'Parcours toutes les transactions de la sommation (Précédent Do)
       Do
            'Parcours toutes les lignes de la feuille "LT violé" afin de trouver une correspondance
           Do
                'Si le # de l'article de la transation est inférieure à celui-ci vérifié, on passe à la prochaine transaction.
               If Sheets("Transactions").Cells(ligne_transaction, 6) < Sheets("LT violé").Cells(ligne_viole, 3) Then
                    GoTo Next_transaction
                End If
 
 
                'Cherche un article correspondant
               'Vérifie si c'est une quantité négative
               'Regarde si la date de commande est < à la Date saisie
               'Regarde si la date saisie est inférieure à la date de livraison
               If Sheets("Transactions").Cells(ligne_transaction, 6) = Sheets("LT violé").Cells(ligne_viole, 3) And _
                Sheets("Transactions").Cells(ligne_transaction, 10) < 0 And _
                Sheets("LT violé").Cells(ligne_viole, 10) < Sheets("Transactions").Cells(ligne_transaction, 9) And _
                Sheets("Transactions").Cells(ligne_transaction, 9) < Sheets("LT violé").Cells(ligne_viole, 12) Then
 
 
                    'Actualise la somme. Si celle-ci est toujours négative, il y a d'autres transactions qui ont causées un LT violé.
                   somme1 = somme1 + Val(Sheets("Transactions").Cells(ligne_transaction, 10))
 
                    If somme1 > 0 And nb_somme > 0 Then
                        GoTo Next_transaction
                    End If
 
 
                    If somme1 = 0 Then
                        GoTo Next_transaction
 
                    End If
 
                    'Inscrit qu'il y a eu un LT violé à cause de cette transaction
                   'Inscrit le résultat à savoir si la commande a été livrée à temps ou non.
                   Sheets("Transactions").Cells(ligne_transaction, 2) = "OUI"
                    Sheets("Transactions").Cells(ligne_transaction, 3) = Sheets("LT violé").Cells(ligne_viole, 7)
                    Sheets("Transactions").Cells(ligne_transaction, 4) = Sheets("LT violé").Cells(ligne_viole, 8)
 
                    derniere_modif = ligne_viole
 
                    'Permet de savoir si on a trouvé une correspondance exacte (Un LT violé)
                   trouver = 1
 
 
                End If
 
 
            ligne_viole = ligne_viole + 1
 
            'Si on a trouvé quelque chose
           If trouver = 1 Then
                GoTo Next_transaction
            End If
 
            Loop Until ligne_viole = derniere_cellule
 
Next_transaction:
        nb_somme = nb_somme - 1
 
        'Vérifie si c'est une quantité positive
       If Sheets("Transactions").Cells(ligne_transaction, 10) > 0 Then
            somme1 = somme1 + Val(Sheets("Transactions").Cells(ligne_transaction, 10))
        End If
 
        'S'assure qu'on a vérifé toutes les transactions
       If nb_somme > 0 And trouver = 0 Then
            Sheets("Transactions").Cells(ligne_transaction, 2) = "NON"
            ligne_transaction = ligne_transaction + 1
 
        ElseIf nb_somme > 0 And trouver = 1 Then
            ligne_transaction = ligne_transaction + 1
            trouver = 0
 
        ElseIf nb_somme = 0 And trouver = 0 Then
            Sheets("Transactions").Cells(ligne_transaction, 2) = "NON"
 
        End If
 
        'Si la ligne_viole active est supérieure à 10, on recule de 10 pour être sur d'inclure toutes les données
       If ligne_viole > 10 And derniere_modif < 10 Then
            ligne_viole = ligne_viole - 10
        ElseIf derniere_modif > 10 Then
            ligne_viole = derniere_modif - 10
        Else
            ligne_viole = derniere_modif
        End If
 
        Loop Until nb_somme = 0
 
 
 
'Si la somme >= 0, c'est que les transactions s'annulent
   Else
 
'Valide s'il a trouvé un LT violé ou non
Validation:
        If trouver = 0 Then
 
            'Met "NON" à toutes les lignes de la somme si il n'a rien trouvé
           For i = ligne_transaction To ligne_transaction_somme
                ligne_viole = derniere_modif
                'Inscrit "NON" si aucune transaction n'a occasionnée de LT violé
               Sheets("Transactions").Cells(i, 2) = "NON"
            Next i
 
            ligne_transaction = ligne_transaction_somme
 
        Else
 
            'Si la ligne_viole active est supérieure à 10, on recule de 10 pour être sur d'inclure toutes les données
           If ligne_viole > 10 And derniere_modif < 10 Then
                ligne_viole = ligne_viole - 10
            ElseIf derniere_modif > 10 Then
                ligne_viole = derniere_modif - 10
            Else
                ligne_viole = derniere_modif
            End If
 
        End If
 
    End If
 
Prochaine_transaction:
'Actualise la barre de progression
Progression_actualiser (Int((ligne_transaction / nb_transaction) * 100))
 
 
 
Next ligne_transaction
 
 
End Sub
 
'Actualise la barre de progression
Sub Progression_actualiser(ByRef Progression As Integer)
'Sub Progression_form()
Progression_form.Height = 108
'Call Progression_form.Show
 
'Progression = Progression + 1
Progression_form.Image_barre_2.Width = Progression * 1.5
Progression_form.Label_barre_2.Caption = Progression & "%"
DoEvents
 
 
End Sub

Merci infiniment à celui ou celle qui m'aidera!

**PMO2017** · 31/05/2013, 22h58

Bonjour,

Je n'ai pas cherché à entrer dans votre code.
*****
Ci-dessous un exemple de code expliquant la technique.
Auparavant, mettez des données (au moins quelques nombres) dans une plage commençant en A1 (supposons dans A1:C10) puis lancez la macro qui va multiplier par 2 tous les nombres rencontrés et les inscrire dans la plage.

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
Sub aa()
Dim R As Range
Dim var As Variant
Dim i As Long
Dim j As Long
'--- La plage des cellules adjacentes à A1 (A1 inclus) ---
Set R = [a1].CurrentRegion
'--- On monte toutes les données de la plage dans un variant    ---
'--- Ce variant va se comporter comme un tableau à 2 dimensions ---
var = R
'### Exemple de traitement ###
'--- Si la donnée est un nombre, on le multiplie par 2 ---
For i = 1 To UBound(var, 1) 'nombre de lignes
  For j = 1 To UBound(var, 2) 'nombre de colonnes
    If IsNumeric(var(i, j)) Then
      var(i, j) = var(i, j) * 2
    End If
  Next j
Next i
'--- On inscrit les données du variant dans la plage ---
R = var
End Sub

**Marc-L** · 31/05/2013, 23h09

Bonjour.

Récemment je suis intervenu sur une procédure prenant 48 secondes pour s'exécuter à chaque appel.

Rien qu'en supprimant l'Userform de la barre de progression, 30 secondes ont été épargnées !

En travaillant ensuite avec une variable tableau au lieu d'utiliser les cellules d'une feuille de calculs, encore 15 secondes de gagnées,
réduisant donc la procédure à environ 3 secondes mais seulement au premier appel car le tableau étant déclaré statique,
l'exécution est instantanée lors des appels suivants !

Voir l'article Utiliser les variables tableaux en VBA Excel …

**hehee** · 31/05/2013, 23h11

Est-ce que les fonction fonctionne tous de la même façon avec des tableaux ?
Est-il possible de faire des Find, If, Isempty.. ?

**hehee** · 01/06/2013, 01h21

Merci de votre aide !!

J'ai créer 2 tableaux dynamiques et j'ai fait mon analyse dans les tableaux dynamiques au lieu des range et...... WOWWWWWWWWWWWWWWW!!!!!!!! J'ai passé de 6 heures à 3 secondes !!!!

Sérieusement, ouff !!

Merci un million de fois !

**PMO2017** · 01/06/2013, 10h10

Bonjour,

Bravo pour vous être posé la bonne question et avoir su appliquer les pistes que nous indiquions à la résolution de votre problème.
Récompense : une magistrale optimisation.

Bonne journée et à plus.

Convertir range en tableau

Macros et VBA Excel

Discussions similaires

Partager

Partager