vba excel : Boucle for à optimiser

**Vbajon** · 30/07/2018, 08h26

Bonjour,

J'ai ce code qui semble un peu long, et pousse Excel à afficher "Ne répond pas" pendant l'exécution de la macro. Est-ce que quelqu'un a une idée sur la façon dont on peut l'optimiser?

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
nombre_element_ligne_3 = .Cells(3, Columns.Count).End(xlToLeft).Column
 
                            somme = 0
                            For I = 4 To nombre_element_ligne_3
                            somme = somme + .Cells(3, I).Value
                            Next I
 
                            For I = 4 To nombre_element_ligne_3
 
 
                                    For j = 4 To 99
                                    .Cells(j, I) = .Cells(j, 1) * .Cells(3, I) / somme
                                    If .Cells(j, I) > .Cells(3, I) Then
                                    .Cells(j, I).Interior.Color = RGB(255, 0, 0)
                                    End If
 
                                    Next j
 
                                    For j = 104 To 199
                                    .Cells(j, I) = .Cells(j, 1) * .Cells(3, I) / somme
                                    If .Cells(j, I) > .Cells(3, I) Then
                                    .Cells(j, I).Interior.Color = RGB(255, 0, 0)
                                    End If
                                    Next j
 
                                    For j = 204 To 299
                                    .Cells(j, I) = .Cells(j, 1) * .Cells(3, I) / somme
                                    If .Cells(j, I) > .Cells(3, I) Then
                                    .Cells(j, I).Interior.Color = RGB(255, 0, 0)
                                    End If
                                    Next j
 
                                    For j = 304 To 399
                                    .Cells(j, I) = .Cells(j, 1) * .Cells(3, I) / somme
                                    If .Cells(j, I) > .Cells(3, I) Then
                                    .Cells(j, I).Interior.Color = RGB(255, 0, 0)
                                    End If
                                    Next j
 
                            Next I

Cordialement

**halaster08** · 30/07/2018, 09h15

Bonjour,

Juste pour info quel est la valeur de nombre_element_ligne_3 ?

Code :

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

1
2
3
4
                            somme = 0
                            For I = 4 To nombre_element_ligne_3
                            somme = somme + .Cells(3, I).Value
                            Next I

Pourquoi passer par une boucle ? tu peux utiliser worksheetfunction.sum()

Code :

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

1
2
3
If .Cells(j, I) > .Cells(3, I) Then
                                    .Cells(j, I).Interior.Color = RGB(255, 0, 0)
                                    End If

Tu pourrais faire une MFC plutôt que forcer la couleur dans une boucle

Code :

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

1
2
3
 For I = 4 To nombre_element_ligne_3
                                    For j = 4 To 99
                                    .Cells(j, I) = .Cells(j, 1) * .Cells(3, I) / somme

Tu peux essayer d'écrire ce calcul sous forme de formule excel dans la premiere cellule puis de faire un copier coller de la formule sur toute ta plage puis un autre copier coller des valeurs, ça permet d'éviter la lourdeur de la double boucle

**Vbajon** · 30/07/2018, 09h58

1) Le nombre d'élément dans la ligne peut varier selon le type de fichier (cela vient d'un fichier externe). Pour l'instant (pour le fichier que j'ai choisi) il est égal à 67.
2) Pour la somme, tu l'écriras comment? somme = worksheetfunction.sum(Range?)?
3) Je ne connais pas de MFC
4) Je ne peux pas le mettre sous forme de formule excel, puisque la plage est variable.

**halaster08** · 30/07/2018, 10h18

Envoyé par Vbajon

1) Le nombre d'élément dans la ligne peut varier selon le type de fichier (cela vient d'un fichier externe). Pour l'instant (pour le fichier que j'ai choisi) il est égal à 67.
2) Pour la somme, tu l'écriras comment? somme = worksheetfunction.sum(Range?)?
3) Je ne connais pas de MFC
4) Je ne peux pas le mettre sous forme de formule excel, puisque la plage est variable.

2)worksheetfunction.sum(Range(cells(3,4),cells(3,nombre_element_ligne_3
)))

3)https://silkyroad.developpez.com/Exc...onditionnelle/

4)Tu peux, il n'y a que dans la première cellule que tu met la formule peut importe la taille de ta plage, d'après ta double boucle en D4. Ensuite tu copies sur ta plage de taille variable.

Code :

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

1
2
3
4
5
range("D4").formula =""
range("D4").copy
range(cells(4,4),cells(99,nombre_element_ligne_3)).pastespecial xlformulas
'copie colle en valeur pour enlever les formules
'recommence la même chose pour tes autres boucles

idem pour tes autres boucles

**Vbajon** · 30/07/2018, 10h59

Envoyé par halaster08

4)Tu peux, il n'y a que dans la première cellule que tu met la formule peut importe la taille de ta plage, d'après ta double boucle en D4. Ensuite tu copies sur ta plage de taille variable.

Code :

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

1
2
3
4
5
range("D4").formula =""
range("D4").copy
range(cells(4,4),cells(99,nombre_element_ligne_3)).pastespecial xlformulas
'copie colle en valeur pour enlever les formules
'recommence la même chose pour tes autres boucles

idem pour tes autres boucles

Ton idée est très bonne. Mais je reste quand même buté à un obstacle : la formule sera "=A4*D3/SOMME(D3:BR3)" dans la cellule D4. Mais D3:BR3 est figé, il n'y a pas moyen de considérer le nombre_element_ligne_3 si le nombre d'éléments vénait à changer. Y a-t-il moyen de remédier à cela?

**halaster08** · 30/07/2018, 11h10

Envoyé par Vbajon

Ton idée est très bonne. Mais je reste quand même buté à un obstacle : la formule sera "=A4*D3/SOMME(D3:BR3)" dans la cellule D4. Mais D3:BR3 est figé, il n'y a pas moyen de considérer le nombre_element_ligne_3 si le nombre d'éléments vénait à changer. Y a-t-il moyen de remédier à cela?

Attention a ne pas oublier les dollar dans ta formule d'après ton code il me semble que c'est $A4*D$3

Pas besoin d'utiliser SOMME(D3:BR3) puisque tu calcule en vba le résultat de la somme autant le réutiliser ici:

Code :

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

1
2
3
somme =worksheetfunction.sum(Range(cells(3,4),cells(3,nombre_element_ligne_3)))
range("D4").formula ="$A4*D$3/" & somme
'...

edit: Merci Pijaku pour l'amélioration non négligeable, je n'y avais pas pensé, j'ai tendance à copier en vba ce que je ferais en Excel.

**pijaku** · 30/07/2018, 11h28

Envoyé par halaster08

4)Tu peux, il n'y a que dans la première cellule que tu met la formule peut importe la taille de ta plage, d'après ta double boucle en D4. Ensuite tu copies sur ta plage de taille variable.

Code :

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

1
2
3
range("D4").formula =""
range("D4").copy
range(cells(4,4),cells(99,nombre_element_ligne_3)).pastespecial xlformulas

Salut,

Juste pour info...

Code :

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

1
2
3
4
5
Sub Demo()
   Range("D4").Formula = "=C4"
   Range("D4").Copy
   Range(Cells(4, 4), Cells(4, 10)).PasteSpecial xlFormulas
End Sub

Peut s'exprimer plus simplement ainsi :

Code :

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

1
2
3
Sub Demo2()
   Range(Cells(4, 4), Cells(4, 10)).Formula = "=C4"
End Sub

vba excel : Boucle for à optimiser

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager