Peut-on m'aider à transformer en boucle

[Bernard34]

Bonsoir le forum,

Je reviens vers vous pour une simplification de code VBA et si possible une accélaration.

Dans la macro, j'ai plusieurs passages de ce type:

Range("B36").Select
Application.Calculation = xlAutomatic
'repérage dernière ligne agents
derliZ = Range("Q65536").End(xlUp).Row
'repérage dernière ligne Analyse
derliw = Range("a65536").End(xlUp).Row
Range("A36").Select
Cells(36, 2).FormulaLocal = "=RECHERCHEV(Q36;Agent!$A$2:$d$65536;4;0)"
Range("b36:b" & derliZ).Select
Application.CutCopyMode = False
Selection.FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Cells(36, 3).FormulaLocal = "=SI(B36<>0;SOMMEPROD((ALE=$B$6)*(Refs='Analyse'!B36)*(PARC<>""ENT"")*1)+SOMMEPROD((ALE=$B$7)*(Refs='Analyse'!B36)*(PARC<>""ENT"")*1)+SOMMEPROD((ALE=$B$8)*(Refs='Analyse'!B36)*(PARC<>""ENT"")*1)+SOMMEPROD((ALE=$B$9)*(Refs='Analyse'!B36)*(PARC<>""ENT"")*1)+SOMMEPROD((ALE=$B$10)*(Refs='Analyse'!B36)*(PARC<>""ENT"")*1)+SOMMEPROD((ALE=$B$11)*(Refs='Analyse'!B36)*(PARC<>""ENT"")*1)+SOMMEPROD((ALE=$B$12)*(Refs='Analyse'!B36)*(PARC<>""ENT"")*1)+SOMMEPROD((ALE=$B$13)*(Refs='Analyse'!B36)*(PARC<>""ENT"")*1);"""")"
Range("c36:c" & derliZ).Select
Application.CutCopyMode = False
Selection.FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Cells(36, 1).FormulaLocal = "=RECHERCHEV(B36;Agent!$n$2:$o$1000;2;0)"
Range("a36:a" & derliZ).Select
Application.CutCopyMode = False
Selection.FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Comment faire pour traiter en FOR ... NEXT ou autre solution qui m'éviterait de positionner le calcul en automatique, ce qui ralentit considérablement le temps de traitement.

Je vous remercie de votre aide.
Bernard

 

[skoobi]

Re : Peut-on m'aider à transformer en boucle

Bonsoir,

Ce serait beaucoup plus pratique si tu expliques ce que fait la macro, on gagnera du temps et avec le fichier encore mieux!

 

[Gael]

Re : Peut-on m'aider à transformer en boucle

Bonsoir Bernard, bonsoir skoobi,

Un peu d'accord avec Skoobi, surtout pour un code sans aucun commentaire.

Pour t'aider un peu concernant le calcul, j'utilise souvent une astuce simple qui consiste à mettre le calcul en manuel et faire des calculs partiels au besoin avec un code du style:

Range("A1:A5").calculate 'sur une plage
Sheets("Test").calculate 'sur une feuille
ActiveCell.EntireRow.Calculate 'sur une ligne

Car on a rarement besoin de recalculer toute l'application mais seulement quelques éléments.

Dans certaines applications, je laisse toujours le calcul en manuel et j'utilise une macro évènementielle pour recalculer la ligne sur laquelle l'utilisateur a fait une modif par exemple.

En termes de temps gagné, c'est considérable.

@+

Gael

 

[Bernard34]

Re : Peut-on m'aider à transformer en boucle

Bonsoir et merci de votre intérêt,
Mon classeur gère un fichier évoluant entre 25 et 30 000 lignes et est remis à jour chaque semaine. Il comporte des informations concernant 7 agences.

La macro en question réalise l'analyse de ce fichier sur 16 colonnes et sur plusieurs stades:

1°) l'analyse globale d'une agence parmi les sept(que l'utilisateur choisit) avec une répartition par équipe composant cette agence et une comparaison de cette agence par rapport à l'ensemble des agences (le poids de l'un par rapport aux autres).

2°) l'analyse qualitative par rapport aux objectifs assignés sur plusieurs critères.

3°) Pour l'agence retenue, la macro détermine les agents la concernant et réalise l'analyse individuelle de chaque agent.
Ce pavé est donc d'une longueur variable à la suite duquel vient se greffer l'analyse d'un autre onglet du classeur.

Pour l'analyse individuelle j'utilise des formules sommeprod ou recherchev.
Comme je ne sais pas créer une routine, je place les formules une à une dans la première ligne concernée et je recopie vers le bas. Je travaille sur 16 colonnes et sur un nombre de ligne aléatoire (évoluant de 10 à 50).
C'est cette phase qui est longue.

En espérant avoir été suffisamment clair

Bernard

 

[Bernard34]

Re : Peut-on m'aider à transformer en boucle

Bonsoir Gael,

Je viens de tester ta réponse dont je te remercie.
En fait, je ne gagne rien, car la macro pose une formule dans une cellule, la recopie vers le bas autant de fois que nécessaire, calcule, recopie les valeurs pour ne pas retarder les calculs suivants.

Néanmoins, je ne connaissais pas cette possibilité de désigner la plage de recalcul.

Merci de ton intérêt.

Ce n'est pas facile de joindre un bout de fichier. Il s'agit d'un fichier protégé par la CNIL comportant des données très confidentielles. Je ne vois pas comment le tranformer pour en joindre un bout.

 

[Gael]

Re : Peut-on m'aider à transformer en boucle

Bonsoir Bernard,

Tu peux créer un exemple de toutes pièces avec juste la structure et quelques données bidon permettant de mieux comprendre le problème.

@+

Gael

 

[skoobi]

Re : Peut-on m'aider à transformer en boucle

Re,

pour ce qui est de la rapidité, il n'y a pas seulement le calcul automatique qui peut ralentir le code mais aussi l'utilisation de "Select", vrai poison celui là .
En plus il n'apporte rien de plus dans un code.

Remplace:

     Range("b36:b" & derliZ).Select
    Application.CutCopyMode = False
    Selection.FillDown

par

     Range("b36:b" & derliZ).FillDown

Une question: juste après tu écris:

     Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Tu ne veux pas conserver les formules mais juste garder les valeurs c'est ça? Dans ce cas essaye ceci:

Range("b36:b" & derliZ).Value = Range("b36:b" & derliZ).Value

à reproduire pour les 3 autres colonnes.

Pour la boucle, ce serait plus clair avec un fichier comme te le conseille Gael, que je salue au passage.

Et puis pour 25 à 30000 lignes, faudra pas attendre de miracle, encore que....

 

[Bernard34]

Re : Peut-on m'aider à transformer en boucle

Bonsoir à tous,

J'ai trituré dans tous les sens la réponse de Skoobi et j'ai trouvé, sans boucle.
En fait, il me suffisait de faire directement:
Range("L36:L" & derliZ).FormulaLocal = "=si(c36<>0;ARRONDI(K36/C36;4);0)"
Range("L36:L" & derliZ).NumberFormat = "0.00%"
Range("l36:l" & derliZ).Value = Range("l36:l" & derliZ).Value
De ce fait, la formule s'ajuste dans chaque cellule et le calcul se fait en mémoire et n'a pas besoin du calcul automatique.

J'ai accéléré le traitement et réduit notablement le nombre de lignes VBA.

Un grand merci à tous

Bernard