vba excel : Boucle for à optimiser

Version imprimable

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:

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

Bonjour,

Juste pour info quel est la valeur de nombre_element_ligne_3 ?

Code:

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:

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:

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

30/07/2018, 09h58
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.

Citation:

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:

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

Citation:
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:

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?

30/07/2018, 11h10
halaster08
Citation:

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:

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.

Citation:
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:

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:

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:

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

30/07/2018, 11h40
Vbajon
Citation:
Envoyé par halaster08

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:

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.
Ça m'affiche la même formule partout au lieu des valeurs : $A4*D$3/11118. Seule la somme est effectivement calculée (11118)
30/07/2018, 11h47
halaster08

Code:

range("D4").formula ="=$A4*D$3/" & somme
30/07/2018, 11h59
Vbajon

Citation:

Envoyé par halaster08

Code:

range("D4").formula ="=$A4*D$3/" & somme

Ca marche super rapide. Merci infiniment. Je ne savais pas qu'on pouvait combiner formules Excel et VBA.
Pour MFC, je vais voir le lien que vous m'avez envoyé.
30/07/2018, 12h15
Vbajon

Je vois comment mettre la mise en forme conditionnelle pour la cellule D4. Mais comment l'étendre sur l'ensemble de la plage souhaitée, un peu comme on a fait pour la formule, importée dans VBA?
30/07/2018, 13h34
halaster08

Citation:

Envoyé par Vbajon

Je vois comment mettre la mise en forme conditionnelle pour la cellule D4. Mais comment l'étendre sur l'ensemble de la plage souhaitée, un peu comme on a fait pour la formule, importée dans VBA?

Je n'ai pas l'habitude de manipuler des MFC en vba je connais donc pas l'instruction qui permet de changer la plage d'application de la MFC, avec un peu de recherche tu devrais pouvoir trouver.
Sinon tu peux aussi simplement appliqué la MFC sur une plage suffisamment grande pour ne pas t'en soucier.
30/07/2018, 13h38
Vbajon

Citation:

Envoyé par halaster08

Sinon tu peux aussi simplement appliqué la MFC sur une plage suffisamment grande pour ne pas t'en soucier.

Ca peut être une bonne idée, sans trop se casser la tête. Merci

Fuseau horaire GMT +2. Il est actuellement 03h46.