Discussion :

[ldescham]

Bonjour à tous,

par souci d'optimisation, et parce que j'atteins les limites de ma machine et/ou d'excel, je pense qu'il faut que je remplace les formules d'un grand jeu de données par les valeurs. J'ai essayé plusieurs méthodes :

1- calculer toute ma feuille (soit 44 colonnes pour 65415 lignes !), copier l'ensemble et coller les valeurs = ECHEC
2- Faire la même chose, mais étape par étape (quelques colonnes à la fois) = ECHEC
3- calculer toute ma feuille, puis utiliser :

Code :

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

Range("A1:AR" & LastLig).Value = Range("A1:AR" & LastLig).Value

...mais là encore = ECHEC
4- faire la même chose, mais étape par étape = ECHEC...

à chaque fois, excel ne répond pas, et j'ai plus de 50% de la mémoire utilisée (soit plus de 800000 K), et la macro n'aboutit pas...

Je mets ci-dessous le début de mon code, ce qui correspond à la méthode 4, qui me paraît pourtant être la moins gourmande :

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
Option Explicit
 
Sub d_Traitement_part1()
 
Dim LastLig As Long
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
LastLig = Sheets("Final Data").Cells(Cells.Rows.Count, 1).End(xlUp).Row
 
    'Creation et preparation --> Feuille Traitement GLOBAL
    Sheets.Add After:=Sheets("Final Data")
    ActiveSheet.Name = "Traitement-GLOBAL"
    Sheets("Final Data").Activate
    Application.CutCopyMode = False
    Range("A1:B" & LastLig).Copy
    Sheets("Traitement-GLOBAL").Activate
    Range("A2").Activate
    ActiveSheet.Paste
    Range("B2").Value = "Episodes AC"
 
    Sheets("Final Data").Activate
    Application.CutCopyMode = False
    Range("Y1:Y" & LastLig).Copy
    Sheets("Traitement-GLOBAL").Activate
    Range("C2").Activate
    ActiveSheet.Paste
    Range("C2").Value = "Posters"
    Range("D2").Value = "EP + POS"
    Range("D3").Formula = "=IF(AND(B3<>"""",C3=""POS""),1,"""")"
    Range("D4").Formula = "=IF(AND(B4<>"""",C4=""POS"",B4<>B3),COUNT(D3)+1,"""")"
    Range("D5").Formula = "=IF(AND(B5<>"""",C5=""POS"",B5<>B4),COUNT(D$3:D4)+1,"""")"
    Range("D5").AutoFill Destination:=Range("D5:D" & LastLig), Type:=xlFillDefault
 
    Columns("A:D").EntireColumn.AutoFit
 
    'Traitement-GLOBAL - Computation --> Episodes
    Range("E1").Value = "AC (affinage)"
    Range("E2").Value = "Mem Deb"
    Range("F2").Value = "Mem Fin"
    Range("G2").Value = "Duree"
    Range("H2").Value = "Latence"
 
    Range(Cells(2, 1), Cells(2, 44)).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
 
        '*Mem Deb AC
    Range("E3").FormulaR1C1 = "=IF('Final Data'!R[-1]C[-3]<>"""",RC1,"""")"
    Range("E4").FormulaR1C1 = _
        "=IF(AND('Final Data'!R[-1]C[-3]<>"""",'Final Data'!R[-1]C[-3]<>'Final Data'!R[-2]C[-3]),RC1,R[-1]C)"
 
        '*Mem Fin AC
    Range("F4").FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(AND('Final Data'!R[-1]C[-4]<>"""",'Final Data'!R[-1]C[-4]<>'Final Data'!RC[-4]),RC1,R[-1]C))"
 
        '*Duree AC
    Range("G4").FormulaR1C1 = "=IF(RC[-1]<>R[-1]C[-1],RC[-1]-RC[-2],"""")"
    Range("E4:G4").AutoFill Destination:=Range("E4:G" & LastLig), Type:=xlFillDefault
 
        '*Latence AC
    Range("H3").FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-3]<>R[-1]C[-3],RC[-3]-RC[-2],""""))"
    Range("H3").AutoFill Destination:=Range("H3:H" & LastLig), Type:=xlFillDefault
 
    Range("H1:H" & LastLig).Select
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
 
Application.Calculation = xlCalculationAutomatic
 
Range("D1:H" & LastLig).Value = Range("D1:H" & LastLig).Value
 
End Sub

Quelqu'un a-t-il une idée de ce qui cloche, sachant que si je fais la même chose directement sur excel, avec un copié/collé, ça fonctionne ?
Avez-vous des pistes ou une procédure à me proposer pour résoudre ce problème ?

Merci d'avance, parce que là je sèche !

Loïc.

[Invité]

déjà remplace test activate copy past par des

Code :

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

Sheets("Final Data").Range("Y1:Y" & LastLig).Copy Sheets("Traitement-GLOBAL").Range("C2")

tu n’utiliseras d'espace mémoire!

[ldescham]

merci, c'est exactement le genre d'astuces utiles qu'il me faut. J'utilise en effet des procédures "bricolage" pas très optimales, mais je progresse au fur et à mesure grâce à ce type de réponse.

Il y a fort à parier que mon problème provient d'une utilisation excessive et inutile de la mémoire. J'ai donc beaucoup de travail pour arriver à mes fins.

Voici le code actualisé dans ce sens, mais qui conduit toujours à un manque de ressources :

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
Option Explicit
 
Sub d_Traitement_part1()
 
Dim LastLig As Long
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
LastLig = Sheets("Final Data").Cells(Cells.Rows.Count, 1).End(xlUp).Row
 
    'Creation et preparation --> Feuille Traitement GLOBAL
    Sheets.Add After:=Sheets("Final Data")
    ActiveSheet.Name = "Traitement-GLOBAL"
    Application.CutCopyMode = False
    Sheets("Final Data").Range("A1:B" & LastLig).Copy Sheets("Traitement-GLOBAL").Range("A2")
    Range("B2").Value = "Episodes AC"
 
    Application.CutCopyMode = False
    Sheets("Final Data").Range("Y1:Y" & LastLig).Copy Sheets("Traitement-GLOBAL").Range("C2")
    Range("C2").Value = "Posters"
    Range("D2").Value = "EP + POS"
    Range("D3").Formula = "=IF(AND(B3<>"""",C3=""POS""),1,"""")"
    Range("D4").Formula = "=IF(AND(B4<>"""",C4=""POS"",B4<>B3),COUNT(D3)+1,"""")"
    Range("D5").Formula = "=IF(AND(B5<>"""",C5=""POS"",B5<>B4),COUNT(D$3:D4)+1,"""")"
    Range("D5").AutoFill Destination:=Range("D5:D" & LastLig), Type:=xlFillDefault
 
    Columns("A:D").EntireColumn.AutoFit
 
    'Traitement-GLOBAL - Computation --> Episodes
    Range("E1").Value = "AC (affinage)"
    Range("E2").Value = "Mem Deb"
    Range("F2").Value = "Mem Fin"
    Range("G2").Value = "Duree"
    Range("H2").Value = "Latence"
 
    Range(Cells(2, 1), Cells(2, 44)).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
 
        '*Mem Deb AC
    Range("E3").FormulaR1C1 = "=IF('Final Data'!R[-1]C[-3]<>"""",RC1,"""")"
    Range("E4").FormulaR1C1 = _
        "=IF(AND('Final Data'!R[-1]C[-3]<>"""",'Final Data'!R[-1]C[-3]<>'Final Data'!R[-2]C[-3]),RC1,R[-1]C)"
 
        '*Mem Fin AC
    Range("F4").FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(AND('Final Data'!R[-1]C[-4]<>"""",'Final Data'!R[-1]C[-4]<>'Final Data'!RC[-4]),RC1,R[-1]C))"
 
        '*Duree AC
    Range("G4").FormulaR1C1 = "=IF(RC[-1]<>R[-1]C[-1],RC[-1]-RC[-2],"""")"
    Range("E4:G4").AutoFill Destination:=Range("E4:G" & LastLig), Type:=xlFillDefault
 
        '*Latence AC
    Range("H3").FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-3]<>R[-1]C[-3],RC[-3]-RC[-2],""""))"
    Range("H3").AutoFill Destination:=Range("H3:H" & LastLig), Type:=xlFillDefault
 
    Range("H1:H" & LastLig).Select
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
 
Application.Calculation = xlCalculationAutomatic
 
Range("D1:H" & LastLig).Value = Range("D1:H" & LastLig).Value
 
Exit Sub

[Invité]

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
 
Sub d_Traitement_part1()
 
Dim LastLig As Long
 Dim ee
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
LastLig = Sheets("Final Data").Cells(Cells.Rows.Count, 1).End(xlUp).Row
 
    'Creation et preparation --> Feuille Traitement GLOBAL
    Sheets.Add After:=Sheets("Final Data")
    ActiveSheet.Name = "Traitement-GLOBAL"
    Application.CutCopyMode = False
    ee = Sheets("Final Data").Range("A1:B" & LastLig).Value
    Sheets("Traitement-GLOBAL").Range("A1:B" & LastLig) = ee
 
    Range("B2").Value = "Episodes AC"
 
    Application.CutCopyMode = False
    ee = Sheets("Final Data").Range("Y1:Y" & LastLig).Value
  Sheets("Traitement-GLOBAL").Range("C2:C" & LastLig) = ee
    Range("C2").Value = "Posters"
    Range("D2").Value = "EP + POS"
    Range("D3").Formula = "=IF(AND(B3<>"""",C3=""POS""),1,"""")"
    Range("D4").Formula = "=IF(AND(B4<>"""",C4=""POS"",B4<>B3),COUNT(D3)+1,"""")"
    Range("D5").Formula = "=IF(AND(B5<>"""",C5=""POS"",B5<>B4),COUNT(D$3:D4)+1,"""")"
    Range("D5").AutoFill Destination:=Range("D5:D" & LastLig), Type:=xlFillDefault
 
    Columns("A:D").EntireColumn.AutoFit
 
    'Traitement-GLOBAL - Computation --> Episodes
    Range("E1").Value = "AC (affinage)"
    Range("E2").Value = "Mem Deb"
    Range("F2").Value = "Mem Fin"
    Range("G2").Value = "Duree"
    Range("H2").Value = "Latence"
 
    Range(Cells(2, 1), Cells(2, 44)).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
 
        '*Mem Deb AC
    Range("E3").FormulaR1C1 = "=IF('Final Data'!R[-1]C[-3]<>"""",RC1,"""")"
    Range("E4").FormulaR1C1 = _
        "=IF(AND('Final Data'!R[-1]C[-3]<>"""",'Final Data'!R[-1]C[-3]<>'Final Data'!R[-2]C[-3]),RC1,R[-1]C)"
 
        '*Mem Fin AC
    Range("F4").FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(AND('Final Data'!R[-1]C[-4]<>"""",'Final Data'!R[-1]C[-4]<>'Final Data'!RC[-4]),RC1,R[-1]C))"
 
        '*Duree AC
    Range("G4").FormulaR1C1 = "=IF(RC[-1]<>R[-1]C[-1],RC[-1]-RC[-2],"""")"
    Range("E4:G4").AutoFill Destination:=Range("E4:G" & LastLig), Type:=xlFillDefault
 
        '*Latence AC
    Range("H3").FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-3]<>R[-1]C[-3],RC[-3]-RC[-2],""""))"
    Range("H3").AutoFill Destination:=Range("H3:H" & LastLig), Type:=xlFillDefault
 
    Range("H1:H" & LastLig).Select
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
 
Application.Calculation = xlCalculationAutomatic
 
Range("D1:H" & LastLig).Value = Range("D1:H" & LastLig).Value
 
Exit Sub
End Sub

[ldescham]

Re-merci,

j'ai appliqué cette méthode, y compris à la fin pour remplacer les formules de mes colonnes, avec :

Code :

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

1
2
ee = Sheets("Traitement-GLOBAL").Range("D1:H" & LastLig).Value
Sheets("Traitement-GLOBAL").Range("D1:H" & LastLig) = ee

Mais même résultat...

...peut-être faudrait-il que mes différentes formules soient exécutées "en dur" via un algo vba pour n'inscrire que les valeurs dès le départ ?

Dans ce cas, comment gérer les AutoFill ? Ou plutôt, comment inscrire en dur + de 65000 lignes, sachant que la plupart du temps, les cellules utilisent la valeur de la cellule précédente ?

Désolé, ça fait beaucoup de questions, mais j'ai plus beaucoup de cheveux à arracher, sachant qu'il me reste encore plusieurs étapes de traitement.

[ldescham]

Des news :

J'ai trouvé un moyen d'écrire en dur directement, sans passer par un autofill, en utilisant une boucle for :

Par exemple, je remplace les lignes 27-28 du dernier code posté par rdurupt par :

Code :

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

1
2
3
    For i = 5 To LastLig
    Range("D" & i).Value = Evaluate("IF(AND(B" & i & "<>"""",C" & i & "=""POS"",B" & i & "<>B" & i - 1 & "),COUNT(D$3:D" & i - 1 & ")+1,"""")")
    Next

Le traitement est long, mais au moins, excel ne plante plus...

Si vous avez d'autres pistes ou d'autres solutions pour gagner en vitesse de traitement, je suis preneur !

[Invité]

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
 
Sub d_Traitement_part1()
 
Dim LastLig As Long
 Dim ee
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
LastLig = Sheets("Final Data").Cells(Cells.Rows.Count, 1).End(xlUp).Row
 
    'Creation et preparation --> Feuille Traitement GLOBAL
    Sheets.Add After:=Sheets("Final Data")
    ActiveSheet.Name = "Traitement-GLOBAL"
    Application.CutCopyMode = False
    Sheets("Traitement-GLOBAL").Range("A1:B" & LastLig) = Sheets("Final Data").Range("A1:B" & LastLig).Value
    Sheets("Traitement-GLOBAL").Range("C2:C" & LastLig) = Sheets("Final Data").Range("Y1:Y" & LastLig).Value
 
    Range("B2").Value = "Episodes AC"
 
    Application.CutCopyMode = False
 
 
    Range("C2").Value = "Posters"
    Range("D2").Value = "EP + POS"
    Range("D3").Formula = "=IF(AND(B3<>"""",C3=""POS""),1,"""")"
    Range("D4").Formula = "=IF(AND(B4<>"""",C4=""POS"",B4<>B3),COUNT(D3)+1,"""")"
    Range("D5").Formula = "=IF(AND(B5<>"""",C5=""POS"",B5<>B4),COUNT(D$3:D4)+1,"""")"
    Range("D5").AutoFill Destination:=Range("D5:D" & LastLig), Type:=xlFillDefault
 
    Columns("A:D").EntireColumn.AutoFit
 
    'Traitement-GLOBAL - Computation --> Episodes
    Range("E1").Value = "AC (affinage)"
    Range("E2").Value = "Mem Deb"
    Range("F2").Value = "Mem Fin"
    Range("G2").Value = "Duree"
    Range("H2").Value = "Latence"
 
    Range(Cells(2, 1), Cells(2, 44)).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
 
        '*Mem Deb AC
    Range("E3").FormulaR1C1 = "=IF('Final Data'!R[-1]C[-3]<>"""",RC1,"""")"
    Range("E4").FormulaR1C1 = _
        "=IF(AND('Final Data'!R[-1]C[-3]<>"""",'Final Data'!R[-1]C[-3]<>'Final Data'!R[-2]C[-3]),RC1,R[-1]C)"
 
        '*Mem Fin AC
    Range("F4").FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(AND('Final Data'!R[-1]C[-4]<>"""",'Final Data'!R[-1]C[-4]<>'Final Data'!RC[-4]),RC1,R[-1]C))"
 
        '*Duree AC
    Range("G4").FormulaR1C1 = "=IF(RC[-1]<>R[-1]C[-1],RC[-1]-RC[-2],"""")"
    Range("E4:G4").AutoFill Destination:=Range("E4:G" & LastLig), Type:=xlFillDefault
 
        '*Latence AC
    Range("H3").FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-3]<>R[-1]C[-3],RC[-3]-RC[-2],""""))"
    Range("H3").AutoFill Destination:=Range("H3:H" & LastLig), Type:=xlFillDefault
 
    Range("H1:H" & LastLig).Select
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
 
Application.Calculation = xlCalculationAutomatic
 
Range("D1:H" & LastLig).Value = Range("D1:H" & LastLig).Value
 
Exit Sub
End Sub

[ldescham]

J'ai bien noté la nouvelle méthode pour récupérer les données d'une autre plage sans utiliser de copier/coller, et sans passer par une variable déclarée, mais pour le reste, rien n'a changé : pas réponse, mémoire utilisée à + de 50%, et processus sans fin...

Je vais partir sur un ensemble de boucles for (voir mon post précédent), sauf s'il y a une autre solution...

[ldescham]

Re bonjour le forum,

Ayé, j'ai pu de cheveux... dur dur.

Après moults essais, notamment via les boucles for dont le parlais plus haut, j'en suis revenu à la méthode initiale, sans énorme succès...

Pour les boucles for, ça a l'air de marcher, mais le traitement est très long, et surtout, il conduit aussi au bout d'un moment au même type d'erreur : ça sent le problème de mémoire...

D'ailleurs, avec la méthode initiale, mais cette fois en écrivant les valeurs sur une autre feuille, j'arrive enfin à mes fins. Le problème, c'est que pour traiter mes 44 colonnes de 65000 lignes, j'ai scindé mon traitement en trois parties. La première partie passe, mais à la deuxième ou troisième, j'obtiens le message : "Erreur 7 : Mémoire insuffisante". L'apparition de ce message n'est pas lié à un évènement donné, mais pointe systématiquement sur une des lignes qui tente d'écrire les valeurs calculées dans une autre feuille.
Comme pour confirmer le problème de mémoire, le traitement va plus loin si Excel ou le PC vient de redémarrer, sans jamais aller au bout...

Voici le code utilisé pour la première partie du traitement :

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
Option Explicit
 
Sub d_Traitement_part1()
 
Dim LastLig As Long
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
LastLig = Sheets("Final Data").Cells(Cells.Rows.Count, 1).End(xlUp).Row
 
    'Creation et preparation --> Feuille Temp
    Sheets.Add After:=Sheets("Final Data")
    ActiveSheet.Name = "Temp"
    Sheets("Temp").Range("A2:B" & LastLig) = Sheets("Final Data").Range("A1:B" & LastLig).Value
    Sheets("Temp").Range("C2:C" & LastLig) = Sheets("Final Data").Range("Y1:Y" & LastLig).Value
 
    Range("B2").Value = "EP"
    Range("C2").Value = "POS"
    Range("D2").Value = "EP + POS"
    Range("D3").Value = Evaluate("IF(AND(B3<>"""",C3=""POS""),1,"""")")
    Range("D4").Value = Evaluate("IF(AND(B4<>"""",C4=""POS"",B4<>B3),COUNT(D3)+1,"""")")
    Range("D5").Formula = "=IF(AND(B5<>"""",C5=""POS"",B5<>B4),COUNT(D$3:D4)+1,"""")"
    Range("D5").AutoFill Destination:=Range("D5:D" & LastLig), Type:=xlFillDefault
 
    'Temp - Computation --> Episodes
    Range("E1").Value = "AC"
    Range("E2").Value = "Mem Deb"
    Range("F2").Value = "Mem Fin"
    Range("G2").Value = "Duree"
    Range("H2").Value = "Latence"
 
        '*Mem Deb AC
    Range("E3").Value = Evaluate("IF('Final Data'!R[-1]C[-3]<>"""",RC1,"""")")
    Range("E4").FormulaR1C1 = _
        "=IF(AND('Final Data'!R[-1]C[-3]<>"""",'Final Data'!R[-1]C[-3]<>'Final Data'!R[-2]C[-3]),RC1,R[-1]C)"
 
        '*Mem Fin AC
    Range("F4").FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(AND('Final Data'!R[-1]C[-4]<>"""",'Final Data'!R[-1]C[-4]<>'Final Data'!RC[-4]),RC1,R[-1]C))"
 
        '*Duree AC
    Range("G4").FormulaR1C1 = "=IF(RC[-1]<>R[-1]C[-1],RC[-1]-RC[-2],"""")"
    Range("E4:G4").AutoFill Destination:=Range("E4:G" & LastLig), Type:=xlFillDefault
 
        '*Latence AC
    Range("H3").FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-3]<>R[-1]C[-3],RC[-3]-RC[-2],""""))"
    Range("H3").AutoFill Destination:=Range("H3:H" & LastLig), Type:=xlFillDefault
 
    'Temp - Computation --> RM
    Range("I1").Value = "RM"
    Range("I2").Value = "Mem Deb"
    Range("J2").Value = "Mem Fin"
    Range("K2").Value = "Duree"
    Range("L2").Value = "Latence"
    Range("M2").Value = "Duree Intra (int.)"
    Range("N2").Value = "Duree Intra"
 
        '*Mem Deb RM
    Range("I3").Value = Evaluate("IF('Final Data'!R[-1]C[20]<>"""",RC1,"""")")
    Range("I4").FormulaR1C1 = _
        "=IF(AND('Final Data'!R[-1]C[20]<>"""",'Final Data'!R[-1]C[20]<>'Final Data'!R[-2]C[20]),RC1, R[-1]C)"
 
        '*Mem Fin RM
    Range("J4").FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(AND('Final Data'!R[-1]C[19]<>"""",'Final Data'!R[-1]C[19]<>'Final Data'!RC[19]),RC1,R[-1]C))"
 
        '*Duree RM
    Range("K4").FormulaR1C1 = _
        "=IF(RC[-1]<>R[-1]C[-1],RC[-1]-RC[-2],"""")"
    Range("I4:K4").AutoFill Destination:=Range("I4:K" & LastLig), Type:=xlFillDefault
 
        '*Latence RM
    Range("L3").FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-3]<>R[-1]C[-3],RC[-3]-RC[-2],""""))"
    Range("L3").AutoFill Destination:=Range("L3:L" & LastLig), Type:=xlFillDefault
 
        '*Duree Intra (int.) RM
    Range("M4").Formula = "=IF(AND(B4<>"""",'Final Data'!AC3<>""""),M3+20,0)"
 
        '*Duree Intra RM
    Range("N4").Formula = "=IF(AND(M4>0,M5=0),M4-20,"""")"
    Range("M4:N4").AutoFill Destination:=Range("M4:N" & LastLig), Type:=xlFillDefault
 
    'Computation --> AP
    Range("O1").Value = "AP"
    Range("O2").Value = "Mem Deb"
    Range("P2").Value = "Mem Fin"
    Range("Q2").Value = "Duree"
    Range("R2").Value = "Latence"
    Range("S2").Value = "Duree Intra (int.)"
    Range("T2").Value = "Duree Intra"
 
        '*Mem Deb AP
    Range("O3").Value = Evaluate("IF('Final Data'!R[-1]C[22]<>"""",RC1,"""")")
    Range("O4").FormulaR1C1 = _
        "=IF(AND('Final Data'!R[-1]C[22]<>"""",'Final Data'!R[-1]C[22]<>'Final Data'!R[-2]C[22]),RC1, R[-1]C)"
 
        '*Mem Fin AP
    Range("P4").FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(AND('Final Data'!R[-1]C[21]<>"""",'Final Data'!R[-1]C[21]<>'Final Data'!RC[21]),RC1,R[-1]C))"
 
        '*Duree AP
    Range("Q4").FormulaR1C1 = _
        "=IF(RC[-1]<>R[-1]C[-1],RC[-1]-RC[-2],"""")"
    Range("O4:Q4").AutoFill Destination:=Range("O4:Q" & LastLig), Type:=xlFillDefault
 
        '*Latence AP
    Range("R3").FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-3]<>R[-1]C[-3],RC[-3]-RC[-2],""""))"
    Range("R3").AutoFill Destination:=Range("R3:R" & LastLig), Type:=xlFillDefault
 
        '*Duree Intra (int.) AP
    Range("S4").Formula = "=IF(AND(B4<>"""",'Final Data'!AK3<>""""),S3+20,0)"
 
        '*Duree Intra AP
    Range("T4").Formula = "=IF(AND(S4>0,S5=0),S4-20,"""")"
    Range("S4:T4").AutoFill Destination:=Range("S4:T" & LastLig), Type:=xlFillDefault
 
    'Temp - Computation --> AP(2+)
    Range("U1").Value = "AP(2+)"
    Range("U2").Value = "Mem Deb"
    Range("V2").Value = "Mem Fin"
    Range("W2").Value = "Duree"
    Range("X2").Value = "Latence"
    Range("Y2").Value = "Duree Intra (int.)"
    Range("Z2").Value = "Duree Intra"
 
        '*Mem Deb AP2+
    Range("U3").Value = Evaluate("IF(AND('Final Data'!R[-1]C[16]<>"""",'Final Data'!R[-1]C[17]>1),RC1,"""")")
    Range("U4").FormulaR1C1 = _
        "=IF(AND('Final Data'!R[-1]C[16]<>"""",'Final Data'!R[-1]C[17]>1,'Final Data'!R[-1]C[16]<>'Final Data'!R[-2]C[16]),RC1, R[-1]C)"
 
        '*Mem Fin AP2+
    Range("V4").FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(AND('Final Data'!R[-1]C[15]<>"""",'Final Data'!R[-1]C[16]>1,'Final Data'!R[-1]C[15]<>'Final Data'!RC[15]),RC1,R[-1]C))"
 
        '*Duree AP2+
    Range("W4").FormulaR1C1 = _
        "=IF(RC[-1]<>R[-1]C[-1],RC[-1]-RC[-2],"""")"
    Range("U4:W4").AutoFill Destination:=Range("U4:W" & LastLig), Type:=xlFillDefault
 
        '*Latence AP2+
    Range("X3").FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-3]<>R[-1]C[-3],RC[-3]-RC[-2],""""))"
    Range("X3").AutoFill Destination:=Range("X3:X" & LastLig), Type:=xlFillDefault
 
        '*Duree Intra (int.) AP2+
    Range("Y4").Formula = "=IF(AND(B4<>"""",'Final Data'!AK3<>"""",'Final Data'!AL3>1),Y3+20,0)"
 
        '*Duree Intra AP2+
    Range("Z4").Formula = "=IF(AND(Y4>0,Y5=0),Y4-20,"""")"
    Range("Y4:Z4").AutoFill Destination:=Range("Y4:Z" & LastLig), Type:=xlFillDefault
 
    'Temp - Computation --> PEI
    Range("AA1").Value = "PEI"
    Range("AA2").Value = "Mem Deb"
    Range("AB2").Value = "Mem Fin"
    Range("AC2").Value = "Duree"
    Range("AD2").Value = "Latence"
    Range("AE2").Value = "Duree Intra (int.)"
    Range("AF2").Value = "Duree Intra"
 
        '*Mem Deb PEI
    Range("AA3").Value= Evaluate("IF('Final Data'!R[-1]C[-8]=""IND"",RC1,"""")")
    Range("AA4").FormulaR1C1 = _
        "=IF(AND('Final Data'!R[-1]C[-8]=""IND"",'Final Data'!R[-1]C[-8]<>'Final Data'!R[-2]C[-8]),RC1, R[-1]C)"
 
        '*Mem Fin PEI
    Range("AB4").FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(AND('Final Data'!R[-1]C[-9]=""IND"",'Final Data'!R[-1]C[-9]<>'Final Data'!RC[-9]),RC1,R[-1]C))"
 
        '*Duree PEI
    Range("AC4").FormulaR1C1 = _
        "=IF(RC[-1]<>R[-1]C[-1],RC[-1]-RC[-2],"""")"
    Range("AA4:AC4").AutoFill Destination:=Range("AA4:AC" & LastLig), Type:=xlFillDefault
 
        '*Latence PEI
    Range("AD3").FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-3]<>R[-1]C[-3],RC[-3]-RC[-2],""""))"
    Range("AD3").AutoFill Destination:=Range("AD3:AD" & LastLig), Type:=xlFillDefault
 
        '*Duree Intra (int.) PEI
    Range("AE4").Formula = "=IF(AND(B4<>"""",'Final Data'!S3=""IND""),AE3+20,0)"
 
        '*Duree Intra PEI
    Range("AF4").Formula = "=IF(AND(AE4>0,AE5=0),AE4-20,"""")"
    Range("AE4:AF4").AutoFill Destination:=Range("AE4:AF" & LastLig), Type:=xlFillDefault
 
Application.Calculation = xlCalculationAutomatic
 
    'Creation et preparation --> Feuille Finale pour Valeurs
    Sheets.Add After:=Sheets("Temp")
    ActiveSheet.Name = "Traitement-GLOBAL"
    Sheets("Traitement-GLOBAL").Range("A1:AR" & LastLig) = Sheets("Temp").Range("A1:AR" & LastLig).Value
 
    Sheets("Temp").Range("E1:AR" & LastLig).ClearContents
 
End
 
End Sub

Je désespère, et toute aide et conseils seront les bienvenus. Je pense qu'il faut optimiser le code de façon générale, mais peut-être aussi trouver un moyen plus optimal pour obtenir les valeurs sur la dernière feuille, ou pour les écrire directement.

A bientôt,

Loïc.

[julio44]

Bonjour,

Une proposition qui est peut-être à côté de la plaque :

Ne pourrais-tu pas importer toutes ces données dans VBA lui-même, les traiter dans VBA et non dans ta feuille Excel et les restituer une fois traitées dans ta feuille Excel ?

Bon courage

[casefayere]

Bonjour,

Peut-être à adapter et controler car fait à main levée :

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
Sub d_Traitement_part1()
 
Dim LastLig As Long
Dim Tbl
 
    'Creation et preparation --> Feuille Traitement GLOBAL
    Sheets.Add After:=Sheets("Final Data")
    ActiveSheet.Name = "Traitement-GLOBAL"
    With Sheets("Final Data")
      LastLig = .Range("B" & .Rows.Count).End(xlUp).Row
      Tbl = .Range("A1:B" & LastLig) 'tes données dans une variable tableau
      With Sheets("Traitement-GLOBAL")
        .Range("A2").Resize(UBound(Tbl, 1), UBound(Tbl, 2)) = Tbl 'et tu remets les valeurs du tableau
        .Range("B2").Value = "Episodes AC" 'ça c'est ton problème
      End With
      Tbl = .Range("Y1:Y" & LastLig)
    End With
    With Sheets("Traitement-GLOBAL")
      .Range("C2").Resize(UBound(Tbl, 1), UBound(Tbl, 2)) = Tbl
      Application.Calculation = xlCalculationManual
      .Range("C2").Value = "Posters"
      .Range("D2").Value = "EP + POS"
      .Range("D3").Formula = "=IF(AND(B3<>"""",C3=""POS""),1,"""")"
      .Range("D4").Formula = "=IF(AND(B4<>"""",C4=""POS"",B4<>B3),COUNT(D3)+1,"""")"
      .Range("D5").Formula = "=IF(AND(B5<>"""",C5=""POS"",B5<>B4),COUNT(D$3:D4)+1,"""")"
      .Range("D5").AutoFill Destination:=Range("D5:D" & LastLig), Type:=xlFillDefault
      .Columns("A:D").EntireColumn.AutoFit
 
      'Traitement-GLOBAL - Computation --> Episodes
      .Range("E1").Value = "AC (affinage)"
      .Range("E2").Value = "Mem Deb"
      .Range("F2").Value = "Mem Fin"
      .Range("G2").Value = "Duree"
      .Range("H2").Value = "Latence"
 
      With .Range(Cells(2, 1), Cells(2, 44)).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
      End With
        '*Mem Deb AC
      .Range("E3").FormulaR1C1 = "=IF('Final Data'!R[-1]C[-3]<>"""",RC1,"""")"
      .Range("E4").FormulaR1C1 = _
        "=IF(AND('Final Data'!R[-1]C[-3]<>"""",'Final Data'!R[-1]C[-3]<>'Final Data'!R[-2]C[-3]),RC1,R[-1]C)"
 
        '*Mem Fin AC
      .Range("F4").FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(AND('Final Data'!R[-1]C[-4]<>"""",'Final Data'!R[-1]C[-4]<>'Final Data'!RC[-4]),RC1,R[-1]C))"
 
        '*Duree AC
      .Range("G4").FormulaR1C1 = "=IF(RC[-1]<>R[-1]C[-1],RC[-1]-RC[-2],"""")"
      .Range("E4:G4").AutoFill Destination:=.Range("E4:G" & LastLig), Type:=xlFillDefault
 
        '*Latence AC
      .Range("H3").FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-3]<>R[-1]C[-3],RC[-3]-RC[-2],""""))"
      .Range("H3").AutoFill Destination:=.Range("H3:H" & LastLig), Type:=xlFillDefault
 
 
      With .Range("H1:H" & LastLig).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
      End With
    Tbl = .Range("D1:H" & LastLig)
    .Range("D1:H" & LastLig) = Tbl
End With
    Application.Calculation = xlCalculationAutomatic
End Sub

[ldescham]

Bonjour,

merci julio44 et casefayere pour vos réponses complémentaires.

Le passage par une variable tableau semble une solution parfaite, mais que je ne maîtrise pas. A ce jour, j'avais réussi à m'en sortir au prix d'un traitement plutôt long, en procédant par étapes successives et avec beaucoup de bricolage.

C'est pourquoi l'exemple de casefayere va sûrement me permettre de gagner en performance (mais aussi en compétence), et à première vue, ça fonctionne parfaitement.

Je teste plus en profondeur et je pense clore la discussion bientôt !

Merci pour l'aide apportée et à bientôt,

Loïc.

[AlainTech]

Bonjour,

Pour les problèmes de lenteur, dès qu'on manipule une feuille où il y a des formules, penser au

Code :

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

Application.Calculation = xlCalculationManual

Oups... Désolé, pas vu qu'il y était déjà.

Pour la mémoire insuffisante, tester en sauvant le classeur à différentes étapes du traitement.

[ldescham]

Bonjour,

merci pour votre dernière réponse, et je 'excuse pour le temps que j'ai mis à répondre.

Malheureusement, la sauvegarde des différentes étapes ne résout pas le problème. J'ai aussi opté pour l'écriture des valeurs colonne par colonne, mais rien n'y fait : j'obtiens toujours une erreur "mémoire insuffisante" au bout d'un temps relativement variable.

Je pense toujours que faire mes calculs dans une variable tableau, pour n'écrire que les valeurs en dur reste la meilleure solution. Malheureusement je n'arrive pas à appliquer cette méthode ni même à comprendre ce qu'il faut que je fasse. J'épluche les forums pour trouver des éléments, mais c'est assez difficile à conceptualiser, et certaines fonctions restent très obscures (Resize, UBound, LBound etc.).

A bientôt,

Loïc.

[Marc-L]

Bonjour, bonjour !

L'aide intégrée au VBA Excel est pourtant claire !

Et ne pas oublier les tutoriels pour complément d'information …

• Utiliser les variables en VBA Excel

• Utiliser les variables tableaux en VBA Excel

• Conceptualisation des variables tableau en VBA …

[ldescham]

Bonjour,

merci, ce sont ces tutos que j'explore. Je veux bien croire que tout y est expliqué, mais il me faut du temps pour intégrer tout ça et surtout pour appliquer les procédures à bon escient. Pour l'instant je pêche toujours, et les tests que je fais ne conduisent pas à la réussite...

Quelqu'un peut-il jeter un oeil à cette tentative, sûrement maladroite, et me dire si je vais dans la bonne direction ? Pour re-situer cette affaire, je cherche à effectuer des manipulations de données dans une variable tableau, pour éviter d'avoir à écrire les formules de calcul dans une feuille du classeur, avant de récupérer les valeurs pour les mettre dans une autre feuille et supprimer la première.

- Est-ce que ce type de procédure peut vraiment résoudre mon problème de mémoire, sachant que le temps d'exécution est très proche (sur cette portion du code en tout cas) du temps d'exécution initial ?
- Quel(s) conseil(s) pouvez-vous me donner pour l'optimiser ?
- Et une question plus spécifique : dans une variable tableau, comment manipuler un ensemble de valeurs - de type Range("A:E") ? J'ai essayé pas mal de choses sans y arriver... J'ai laissé dans le code (en rouge), ce qui ne produit pas d'erreur, mais qui n'aboutit pas au résultat escompté (pour chaque cellule de la colonne, je voudrais ici numéroter les cas où les conditions sont vérifiées).

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
Sub d_TEST_NEWTraitement_part1()

Dim LastLig As Long
Dim Tbl As Variant
Dim i As Long
Dim x As Long


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

LastLig = Sheets("Final Data").Cells(Cells.Rows.Count, 1).End(xlUp).Row

    'Creation et preparation --> Feuille Temp & Traitement Global
    Sheets.Add After:=Sheets("Final Data")
    ActiveSheet.Name = "Temp"
    
    Tbl = Sheets("Final Data").Range("A1:B" & LastLig + 1)
    Sheets("Temp").Range("A2").Resize(UBound(Tbl, 1), UBound(Tbl, 2)) = Tbl

    Tbl = Sheets("Final Data").Range("Y1:Y" & LastLig + 1)
    Sheets("Temp").Range("C2").Resize(UBound(Tbl, 1), UBound(Tbl, 2)) = Tbl
    
    Range("B2").Value = "Episodes AC"
    Range("C2").Value = "Posters"
    Range("D2").Value = "EP + POS"

    Tbl = Sheets("Temp").Range("D1:D" & LastLig + 1)
    Tbl(3, 1) = Evaluate("IF(AND(Temp!B3<>"""",Temp!C3<>""""),1,"""")")
    Tbl(4, 1) = Evaluate("IF(AND(Temp!B4<>"""",Temp!C4<>"""",Temp!B4<>Temp!B3),COUNT(Tbl(3,1))+1,"""")")
    
    For i = 5 To UBound(Tbl, 1)
    Tbl(i, 1) = Evaluate("IF(AND(Temp!B" & i & "<>"""",Temp!C" & i & "<>"""",Temp!B" & i & "<>Temp!B" & i - 1 & "), COUNT(Tbl(3, 1): Tbl(" & i - 1 & ", 1)) + 1,"""")")
    Next i
        
    Sheets("Temp").Range("D1:D" & LastLig + 1) = Tbl
    
   
End Sub

Désolé d'insister sur ce topic, mais malgré mes bonnes volontés je ne m'en sors pas...

A bientôt,

Loïc.

[mercatog]

As tu un fichier exemple?

[ldescham]

Bonjour,

tiens, ça devrait vous rappeler quelque chose
voici un fichier exemple :

http://cjoint.com/?0Fghm1aKc9c

au passage, je ne sais pas si ça a de l'importance, mais au vu de la taille de mes fichiers, surtout après l'ensemble du traitement, je suis passé en format .xlsb

Désolé pour le lien cijoint, mais le fichier ne passait pas ici...

A bientôt,

Loïc.

[mercatog]

Avec un fichier vide (colonnes B et Y que tu utilises), tu n'aide guère

[ldescham]

Bonsoir,

Mais... les colonnes ne sont pas vides (si?).

Le fichier est énorme parce qu'il s'agit d'une retranscription d'une vidéo découpée en tranches de 20 ms. Par exemple, la première valeur trouvée en colonne B est située en ligne 2472. Ceci est nécessaire pour étudier certaines composantes dynamiques des comportements. J'ai un certain nombre de retranscriptions à traiter, donc je me dois d'être flexible, d'où un certain nombre de macros de tri qui interviennent après ce traitement.

Après il est possible que le fichier ait planté. Je peux dans ce cas là retransférer un fichier d'exemple.

A bientôt,

Loïc.