optimisation boucle

Version imprimable

optimisation boucle

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:

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

22/10/2007, 18h49
Jean-Philippe André

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

Code:

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

ca éviterait des calculs avec l'offset, qui m'ont l'air inutiles ici...
Qu'en dis-tu ?
22/10/2007, 19h02
casefayere

j'ai essayé

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

à +
22/10/2007, 19h08
Jean-Philippe André

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" ?

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:

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+

Bonjour,
Il doit certainement avoir moyen de simplifié ;)
Supposition pour optimisé...
'selection' est une plage de cellule

Code:

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 :P
S'il y a des paramètres qui ne sont pas bons .. tu dit
A+

re optimisation boucle

(re) bonjour

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

Code:

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

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

Code:

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:

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.

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:

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 :yaisse2:
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+

23/10/2007, 16h38
casefayere

re boucles pour Gorfael

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
23/10/2007, 16h55
casefayere

boucles et fichier à gorfael

re,
ne pouvant envoyer le fichier trop gros le voici en pdf avec les deux feuilles qui m'interessent

Merci
23/10/2007, 17h43
casefayere

enfin pour les boucles

rerere bonjour,

J'ai enfin trouvé mon problème qui etait pourtant simple mais vous ne pouviez pas deviner si je fait des conn...
la boucle marchait quand même mais le programme revenait sur l'instruction worksheet.change et recommençait le calcul, je suis un idiot.

Mais je vais étudier et plancher sur vos propositions qui me paraissent plus simples, c'est comme ça qu'on apprend.

encore merci à tous et plus particulièrement à Gorfael

a+