optimisation boucle

**casefayere** · 22/10/2007, 17h12

Bonjour ou Bonsoir tout le monde,

ça fait un moment que je n'ai pas posé de questions car j'essaie de me débrouiller seul mais je viens de rencontrer un problème que je n'arrive pas à résoudre. la réponse de la macro est très lente pour une boucle simple, que puis-je faire d'autant que je n'ai pas beaucoup de données à vérifier étant en essai (70 à peu près). voici ma boucle et le résultat :

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
adresse_cel = Selection.Address
nsc = Range(adresse_cel).Column
nsr = Range(adresse_cel).Count
nsl = Range(adresse_cel).Row
pt1 = Range(adresse_cel).End(xlUp).Offset(nsl - 1, 0).Address 'adresse 1ere ligne selectionnée
pt2 = Range(pt1).Offset(nsr - 1, 0).Address
 
c = Sheets("codes").Range("a2").End(xlDown).Offset(0, 0).Row
For k = 0 To c
    codecherche = Sheets("CODES").Range("a2").Offset(k, 0).Value
    groupecherche = Sheets("CODES").Range("a2").Offset(k, 2).Value
 
    If codecherche = activitecherchee Then: Exit For
Next k
    Sheets("saisies").Range(pt1).Offset(0, 9).Value = groupecherche

Merci de votre réponse

**Jean-Philippe André** · 22/10/2007, 17h49

bonsoir,
a la place des offsets, qui selon moi ralentissent effectivement ton traitement, je partirais sur un "simple"

Code :

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

Range("a" & k+2).value

ca éviterait des calculs avec l'offset, qui m'ont l'air inutiles ici...
Qu'en dis-tu ?

**casefayere** · 22/10/2007, 18h02

Merci de ta réponse mais j'ai essayé aussitot et ça n'a rien changé

à +

**Jean-Philippe André** · 22/10/2007, 18h08

ok, autre point auquel je n'avais pas encore pensé, pluytot que de faire une boucle for avec un exit for, passe peut être par une boucle while ?

Je reste étonné mais qu'entends tu par "lent" ?

**Gorfael** · 22/10/2007, 18h33

Salut
Si ta macro est lente, donne-la en intégralité. Ce n'est pas parce que tu supposes que c'est la boucle qui ralentit le processus que c'est vrai.

adresse_cel = Selection.Address
ne connaissant pas le début de la macro, je ne peut que préciser que les selection multiple ralentissent la macro
nsc = Range(adresse_cel).Column
??? pourquoi pas selection.column ? pas d'utilité dans le code donné
nsr = Range(adresse_cel).Count
??? nombre de cellules sélectionnées
nsl = Range(adresse_cel).Row
idem colonne : tu as donc la colonne et la ligne de la 1re cellule, en haut à gauche, de ta sélection
pt1 = Range(adresse_cel).End(xlUp).Offset(nsl - 1, 0).Address 'adresse 1ere ligne selectionnée
NON !!! ça dépend du contexte (qu'on a pas !!!)
=> Pt1 = rows(nsl).address
pt2 = Range(pt1).Offset(nsr - 1, 0).Address
Je ne comprends pas ce que tu cherches à calculer

c = Sheets("codes").Range("a2").End(xlDown).Offset(0, 0).Row
Pourquoi offset(0,0) ?!? Excel exécute chacune des instruction (même si elles ne servent à rien) et prend du temps pour le faire
For k = 0 To c
??? va servir à piloter un offset de A2 à dernière ligne +1 de A de Codes ???
codecherche = Sheets("CODES").Range("a2").Offset(k, 0).Value
groupecherche = Sheets("CODES").Range("a2").Offset(k, 2).Value
encore des variables non identifiées prélevées en A et C
If codecherche = activitecherchee Then: Exit For
??? Dans le flou complet : tu sorts si une variable est égale à la valeur de la cellule de A de codes en cours. Les ":" servent à rien
Next k
Sheets("saisies").Range(pt1).Offset(0, 9).Value = groupecherche

C'est un extrait de code où tu supposes que tu perds du temps.
Si le reste de ton code est dans le même style, pas étonnant que ça rame !

Donnes ton code complet (avec les déclarations de variables, les "sous-macros", etc... et le but de ta macro, et on pourra t'aider (enfin, je suppose)

Là, tu complexifies à loisir un code qui, me semble-t-il pourrait être plus simple et sur moins de variables.

La "même" que la tienne (pour ce que j'en comprend :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
Dim K As Long
With Sheets("codes")
    For K = 2 To .Range("A2").End(xlDown)
        If .Range("A" & K) = activitecherchee Then
            Sheets("saisies").Range("J" & Selection.Row) = .Range("C" & K)
            Exit For
        End If
    Next K
End With

A+

**LeForestier** · 22/10/2007, 20h11

Bonjour,
Il doit certainement avoir moyen de simplifié

Supposition pour optimisé...
'selection' est une plage de cellule

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Chercher()
Dim cell As Range
Dim codecherche 'pour mon test
Sheets("feuil1").Select
Range("A1:A20").Select 'pour mon test
codecherche = Range("F5").Value 'pour mon test
    For Each cell In Selection
        If cell.Value = codecherche Then
            groupecherche = Cells(cell.Row, cell.Column + 2).Value
            'Sheets("saisies")......
            Exit For
        End If
    Next cell
End Sub

Je crois que c'est plus simple

S'il y a des paramètres qui ne sont pas bons .. tu dit
A+

**casefayere** · 23/10/2007, 13h49

(re) bonjour

voici ma macro complete
d'une part dans la feuille "saisies" :

Code :

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

1
2
3
Private Sub Worksheet_Change(ByVal Target As Range)
Call depart
End Sub

ensuite, dans module "general ..(declarations)" :

Code :

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

1
2
3
4
5
6
7
8
9
10
Public pt, pt1, pt2
Public c, s, K As Long
Public j, nul, lgactif, lgpassif
Public nsr 'variable nombre de lignes de la selection
Public nsl 'variable numero de ligne de la selection
Public nsc 'variable numero de colonne de la selection
Public adresse_cel
Public activitecherchee, groupecherche, codecherche, message, pourtext, verif As String
Public groupe
Public soldeactivite, tempo

et la macro principale essayant de tenir compte si une seule cellule est selectionnée en prenant garde que la cellule immédiatement à gauche est renseignée ou s'il s'agit d'une recopie incrémentée, en rouge la partie de code qui me ralentit :

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
Sub depart()
verif = ""
adresse_cel = Selection.Address
nsc = Range(adresse_cel).Column
nsr = Range(adresse_cel).Count
nsl = Range(adresse_cel).Row
pt1 = Range(adresse_cel).End(xlUp).Offset(nsl - 1, 0).Address 'adresse 1ere ligne selectionnée
pt2 = Range(pt1).Offset(nsr - 1, 0).Address 'adresse derniere ligne selectionnée
If nsc <> 5 Or Range(pt1).Value = "" Then
    Exit Sub
End If
If nsr = 1 Then
    If Range("d" & nsl).Value = "" Then
        verif = "": message = MsgBox("veuillez d'abord saisir le journal", vbOKOnly, "ATTENTION")
        If message = "1" Then
        Range("e" & nsl).Value = "": Range("d" & nsl).Select: End If
    Else 'recherche pour une seule selection
        activitecherchee = Sheets("saisies").Range(adresse_cel).Value
c = Sheets("codes").Range("a2").End(xlDown).Offset(0, 0).Row
For K = 0 To c
    codecherche = Sheets("CODES").Range("a2").Offset(K, 0).Value
    groupecherche = Sheets("CODES").Range("a2").Offset(K, 2).Value
    
    If codecherche = activitecherchee Then: Exit For
Next K
    tempo = Sheets("saisies").Range("n" & nsl).Address
    Sheets("saisies").Range(tempo).Value = groupecherche
    verif = "ok"
    Exit Sub
End If
Exit Sub

If verif = "ok" Then: Exit Sub
End If' ci-dessous conditions pour recopies incrémentées ou étendues
If Range(adresse_cel).End(xlUp).Offset(nsl - 1, 0).Value = "" And Range(adresse_cel).End(xlUp).Offset(nsl - 1, -1).Value = "" Then
verif = "ok": Range(adresse_cel).End(xlUp).Offset(nsl, -1).Select: Exit Sub
End If
If nsc <> 5 Then
        Exit Sub
End If
If nsr > 1 Then
    If nsc = 5 Then
        If Range("e" & nsl).Offset(1, -1).Value <> "" Then
        pt = "": pt1 = "": pt2 = ""
        Call selectionmultiple
        ElseIf Range("e" & nsl).Offset(1, -1).Value = "" Then
        verif = "": message = MsgBox("veuillez d'abord saisir le journal", , "ATTENTION")
        adresse_cel = Selection.Address
        pt1 = Range(adresse_cel).End(xlUp).Offset(nsl, 0).Address
        pt2 = Range(adresse_cel).End(xlUp).Offset(nsl + nsr - 2, 0).Address
        Range(pt1, pt2).ClearContents: verif = "ok"
        End If
    End If
    End If

  ' ci-dessous empeche la macro si le journal n'est pas saisi en premier
If Range("d" & nsl).Value = "" And Range("e" & nsl).Value <> "" Then
    verif = "": message = MsgBox("veuillez d'abord saisir le journal", vbOKOnly, "ATTENTION")
    If message = "1" Then
    Range("e" & nsl).Value = "": Range("d" & nsl).Select: Exit Sub
    End If
End If
' ci-dessous si les conditions ci-dessus sont remplies pour selection simple, execute la macro
End Sub
Sub selectionsimple()
activitecherchee = Sheets("saisies").Range(adresse_cel).Value
c = Sheets("codes").Range("a2").End(xlDown).Offset(0, 0).Row
For K = 0 To c
    codecherche = Sheets("CODES").Range("a2").Offset(K, 0).Value
    groupecherche = Sheets("CODES").Range("a2").Offset(K, 2).Value
    If codecherche = activitecherchee Then: Exit For
Next K
    Sheets("saisies").Range(pt1).Offset(0, 9).Value = groupecherche
    Sheets("saisies").Range("a2").End(xlDown).Offset(1, 0).Select
    Stop
End Sub
Sub selectioncopiee()
nsr = Range(Selection.Address).Count
nsl = Range(Selection.Address).Row
nsc = Range(Selection.Address).Column
adresse_cel = Selection.Address
    
    Range(adresse_cel).Select
    pourtext = "a"

End Sub
Sub selectionmultiple()
adresse_cel = Selection.Address
nsc = Range(adresse_cel).Column
nsr = Range(adresse_cel).Count
nsl = Range(adresse_cel).Row
s = nsl
pt = Range(adresse_cel).End(xlUp).Offset(nsl - 1, 9).Address
pt1 = Range(adresse_cel).End(xlUp).Offset(nsl + nsr - 2, 9).Address
pt2 = Range(pt, pt1).Address

K = "": n = "": verif = "": activitecherchee = ""
'fin de boucle pour rechercher l'activité dans le feuille des codes
c = Sheets("CODES").Range("A2").End(xlDown).Offset(1, 0).Row
'recherche activité
activitecherchee = Sheets("saisies").Range("e" & nsl).Value

K = 0
    Do
    K = K + 1
    groupe = Sheets("CODES").Range("a2").Offset(K, 2).Value
    Loop While activitecherchee <> Sheets("CODES").Range("a2").Offset(K, 0).Value
K = 0
    verif = "ok"
    Sheets("saisies").Range(pt).Select
    Sheets("saisies").Range(pt).Value = groupe
    Selection.AutoFill Destination:=Range(pt2), Type:=xlFillDefault
    Range("d2").End(xlDown).Offset(1, 0).Select
Exit Sub
End Sub

Merci et, à l'attention de GORFAEL, excuses-moi pour le message privé, je ne savais pas comment faire.

**Gorfael** · 23/10/2007, 14h46

Salut casefayere et le forum

Si tu expliquais simplement ce que tu essaies de faire ?

j'ai modifier le début de ta macro :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub depart()
verif = ""
adresse_cel = Selection.Address
nsc = Range(adresse_cel).Column
nsr = Range(adresse_cel).Count
nsl = Range(adresse_cel).Row
pt1 = Range(adresse_cel).End(xlUp).Offset(nsl - 1, 0).Address 'adresse 1ere ligne selectionnée
pt2 = Range(pt1).Offset(nsr - 1, 0).Address 'adresse derniere ligne selectionnée
Range("B1") = pt1
Range("B2") = pt2
Exit Sub
If nsc <> 5 Or Range(pt1).Value = "" Then

Le reste étant identique
Sur une feuille vierge en colonne A de 1 à 30 "A"
je sélectionne A10 => B1=$A$10 ; B2==$A$10

Je sélectionne A10:H10 => B1=$A$10 ; B2=$A$17 ?!?
A10:B20 => B1:$A$10 ; B2=$A$31

Ton code est à l'image de ça : fait vraisemblablement ce que tu veux dans certains cas, mais à de grosses lacunes.
Et décomposer ton code ne sert à rien, puisque je ne sais pas ce que tu fais.

Par exemple :
c = Sheets("codes").Range("a2").End(xlDown).Offset(0, 0).Row
donne la dernière ligne avant une cellule vide de A.... Ou si c'est vide après A2, la dernière ligne du tableau. Bien évidemment dans ce cas, l'attente est tant soi peu plus longue.

Il est plus simple de construire une macro nouvelle, en limitant les variables à celles vraiment utiles, et en evitant des appels qui ralentissent, que d'essayer de comprendre ce que tu essaies de faire avec des Offset(0,0) ?!?
De plus, pourquoi utiliser les macros automatiques pour lancer le traitement en non pour traiter ?

Donnes le but de ta macro, les données, et on te donnera le code que nous on ferait. Et dans les réponses, tu trouveras certainement des parties de code qui te conviennent.
A+

**casefayere** · 23/10/2007, 15h38

A Gorfael,

j'ai unfichier excel qui marche très bien pour faire ma compta (d'un Comité d'entreprise) et des amis m'ont demandé de leur prévoir un fichier qui corresponde à leurs besoins. Le mien est construit sans VBA mais qu'avec des formules. Je veux donc leur préparer la meme chose mais avec VBA pour leur éviter de faire des erreurs. J'envoie en fichier joint malgré que je n'en sois qu'au début mon projet (avec plein d'erreurs dedans. Pour l'instant, il convient de ne regarder que la feuille "saisie" où les colonnes "journal" et "affectation" metten à jour la colonne "NOM GRP". Il reste encore beaucoup de formules dans les autres cellules mais j'y vais pas à pas, en espérant que tu comprendras et que tu verras le problème immédiat qui me préoccupe. En attendant je vais étudier le code que tu me propose. toutes les données entrées dans le fichier joint ne sont que des essais.

PS : mon fichier est trop gros pour l'envoyer meême en restreignant le nombre de feuilles et en éliminant les fonctions (>128 Ko)

donc en selectionnant une ou en faisant une copie incrémentée sur la ou les cellules de la colonne "affectation", le nom du groupe doit se mettre à jour, j'oblige à tenir compte du journal pour des raisons à venir (mise à jour de "NOM GRP")
Merci encore

optimisation boucle

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager