Logo GNT
Actualités Tests & Guides Bons Plans
Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11
Entraide Applications Microsoft Office Excel Macro interminable...à optimiser

Macro interminable...à optimiser

9 réponses
Avatar
Patrice FY
Bonjour,

Mon probl=E8me est le suivant:
J'ai environ 10000 lignes dans une feuille A et environ=20
9800 dans une feuille B. Je compare pour chaque ligne de=20
ma feuille A le contenu d'une cellule au contenu d'une=20
cellule de ma feuille B pour trouver les contenu=20
indentiques et mettre un e valeur dans une autre cellule=20
de ma feuille A. J'ai utilis=E9 deux boucles FOR TO NEXT=20
style
For a =3D 1 to 10000
For b=3D 1 to 9800
je compare mes lignes feuille A range("a") et feuille=20
B range("b")et si =E9galit=E9 je mets une valeur dans ma=20
feuille A
Next b
Next a
Cela fonctionne parfaitement mais la macro dure de tr=E8s=20
tr=E8s longues heures !!!
Comment r=E9duire la dur=E9e de cette macro ???? Merci
Signaler un problème avec ce contenu

9 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
LSteph
Bonsoir Patrice,

Le code en lui même permettrait de savoir plus précisément quelle
comparaison tu fais,
et mieux de voir en quoi il pourrait être optimisé.

lSteph

"Patrice FY" a écrit dans le message
de news: 0b9a01c537aa$54e97080$
Bonjour,

Mon problème est le suivant:
J'ai environ 10000 lignes dans une feuille A et environ
9800 dans une feuille B. Je compare pour chaque ligne de
ma feuille A le contenu d'une cellule au contenu d'une
cellule de ma feuille B pour trouver les contenu
indentiques et mettre un e valeur dans une autre cellule
de ma feuille A. J'ai utilisé deux boucles FOR TO NEXT
style
For a = 1 to 10000
For b= 1 to 9800
je compare mes lignes feuille A range("a") et feuille
B range("b")et si égalité je mets une valeur dans ma
feuille A
Next b
Next a
Cela fonctionne parfaitement mais la macro dure de très
très longues heures !!!
Comment réduire la durée de cette macro ???? Merci
Avatar
Jean Elens
Une bonne idee serait de trier les deux series de comparaison.
ainsi tu pourrais au lieu de parcourrir la liste B 10000 fois, faire un saut
direct vers une ligne où tu sais que les valeurs précédentes ne sont pas
egales, et arreter la comparaison de la liste B une fois que les valeurs ne
sont > .




"Patrice FY" wrote in message
news:0b9a01c537aa$54e97080$
Bonjour,

Mon problème est le suivant:
J'ai environ 10000 lignes dans une feuille A et environ
9800 dans une feuille B. Je compare pour chaque ligne de
ma feuille A le contenu d'une cellule au contenu d'une
cellule de ma feuille B pour trouver les contenu
indentiques et mettre un e valeur dans une autre cellule
de ma feuille A. J'ai utilisé deux boucles FOR TO NEXT
style
For a = 1 to 10000
For b= 1 to 9800
je compare mes lignes feuille A range("a") et feuille
B range("b")et si égalité je mets une valeur dans ma
feuille A
Next b
Next a
Cela fonctionne parfaitement mais la macro dure de très
très longues heures !!!
Comment réduire la durée de cette macro ???? Merci
Avatar
ROCHE Pierre
Bonsoir,
Il te faut faire un appareillage (comme on dit dans le milieu informatique)
aménagé :
1 - Trier les 2 feuilles
2 - Faire ta boucle de lecture de la feuille A
3 - Comparer un à un les éléments de la feuille B
3a - Si tu as égalité => action dans feuille A
3b - Si cellule feuille B> cellule feuille A => passage à la cellule
suivante de la feuille A
4 - Test de cette nouvelle cellule de la feuille A par rapport à la cellule
de la feuille B qui a permis d'arrêter la boucle précédente (3b)
5 - revenir par la même méthode au point 3
6 - Faire la séquence 3-3a-3b-4 jusqu'à épuisement des cellules de la
feuille A

Exemple :
En feuille A, tu as les 5 valeurs 3, 4, 5, 6 et 8 dans les cellules A1 à A5
En feuille B, tu as les 4 valeurs 1, 3, 5 et 7 dans les cellules B1 à B4

L'algorythme est le suivant :

Début de Traitement
INDICE_B = 1
POUR I =1 à 5 par pas de 1
POUR J = INDICE_B à 4 par pas de 1
SI B[J]>=A[I]
ALORS
SI B[J]=A[I]
ALORS
Ecriture dans Feuille A ou autre action
FIN_SI
INDICE_B=J (=> les autres valeurs de A sont au moins égales à
celle de B référencée par INDICE_B => servira dans la prochaine boucle J)
SORTIR BOUCLE J (ou J=4) (les autres valeurs de B ne peuvent
convenir => arrêt boucle)
FIN_SI
J SUIVANT
SI INDICE_B=4 ET A[I]>B[INDICE_B] (=> la valeur actuelle de A est
supérieure à la plus grande valeur de B (dernière) => plus de possibilité de
cas identiques)
ALORS
SORTIR BOUCLE I (ou I=5)
FIN_SI
I SUIVANT
Fin de Traitement

PS : 4 et 5 devront bien entendu être variabilisés

J'espère que cela pourra t'aider dans ta programmation VBA

Cordialement

"Patrice FY" a écrit dans le message
de news: 0b9a01c537aa$54e97080$
Bonjour,

Mon problème est le suivant:
J'ai environ 10000 lignes dans une feuille A et environ
9800 dans une feuille B. Je compare pour chaque ligne de
ma feuille A le contenu d'une cellule au contenu d'une
cellule de ma feuille B pour trouver les contenu
indentiques et mettre un e valeur dans une autre cellule
de ma feuille A. J'ai utilisé deux boucles FOR TO NEXT
style
For a = 1 to 10000
For b= 1 to 9800
je compare mes lignes feuille A range("a") et feuille
B range("b")et si égalité je mets une valeur dans ma
feuille A
Next b
Next a
Cela fonctionne parfaitement mais la macro dure de très
très longues heures !!!
Comment réduire la durée de cette macro ???? Merci
Avatar
MichDenis
Bonjour Patrice FY,

Essaie quelque chose dans le genre :

Cet exemple suppose que les données sont dans les 2 cas en colonne A de la feuille1 et de la feuille2.
et si on retrouve dans la colone A de la feuille 2, la même donnée que la colonne A en feuille1, il s'inscrira un "E" dans la
colonne D de la feuille 1

Il ne te reste plus qu'à adapter au besoin

'---------------------------------
Sub Doublons()

Dim Rg As Range

With Worksheets("Feuil1")
Set Rg = .Range("A2:A" & _
.Range("A65536").End(xlUp).Row)
End With

Application.EnableEvents = False
Application.ScreenUpdating = False
Adr = "Feuil2!" & Rg.Address
'Pour inscrire E en colonne D,
'soit 3 colonnes à droite de la colonne A
With Rg.Offset(, 3)
.FormulaLocal = "=si(estnum(Equiv(A2;" & _
Adr & ";0));""E"";"""")"
.Value = .Value
End With
Application.EnableEvents = True
Set Rg = Nothing
End Sub
'---------------------------------


Salutations!







"Patrice FY" a écrit dans le message de news: 0b9a01c537aa$54e97080$
Bonjour,

Mon problème est le suivant:
J'ai environ 10000 lignes dans une feuille A et environ
9800 dans une feuille B. Je compare pour chaque ligne de
ma feuille A le contenu d'une cellule au contenu d'une
cellule de ma feuille B pour trouver les contenu
indentiques et mettre un e valeur dans une autre cellule
de ma feuille A. J'ai utilisé deux boucles FOR TO NEXT
style
For a = 1 to 10000
For b= 1 to 9800
je compare mes lignes feuille A range("a") et feuille
B range("b")et si égalité je mets une valeur dans ma
feuille A
Next b
Next a
Cela fonctionne parfaitement mais la macro dure de très
très longues heures !!!
Comment réduire la durée de cette macro ???? Merci
Avatar
ROCHE Pierre
Cet algorythme marche uniquement si une cellule de la Feuille A n'est
présente qu'UNE SEULE FOIS dans la feuille B (pas de doublons)

S'il y a des doublons, l'algorythme devient le suivant :

Début de Traitement
INDICE_B = 1
POUR I =1 à 5 par pas de 1
POUR J = INDICE_B à 4 par pas de 1
SI B[J]>=A[I]
ALORS
SI B[J]=A[I]
ALORS
Ecriture dans Feuille A ou autre action
SINON
INDICE_B=J (=> les autres valeurs de A sont supérieures à
celle de B référencée par INDICE_B => servira dans la prochaine boucle J)
SORTIR BOUCLE J (ou J=4) (les autres valeurs de B ne peuvent
convenir pour le cas traité => arrêt boucle)
FIN_SI
FIN_SI
J SUIVANT
SI INDICE_B=4 ET A[I]>B[INDICE_B] (=> la valeur actuelle de A est
supérieure à la plus grande valeur de B (dernière) => plus de possibilité de
cas identiques)
ALORS
SORTIR BOUCLE I (ou I=5)
FIN_SI
I SUIVANT
Fin de Traitement



"ROCHE Pierre" a écrit dans le message de news:

Bonsoir,
Il te faut faire un appareillage (comme on dit dans le milieu
informatique) aménagé :
1 - Trier les 2 feuilles
2 - Faire ta boucle de lecture de la feuille A
3 - Comparer un à un les éléments de la feuille B
3a - Si tu as égalité => action dans feuille A
3b - Si cellule feuille B> cellule feuille A => passage à la cellule
suivante de la feuille A
4 - Test de cette nouvelle cellule de la feuille A par rapport à la
cellule de la feuille B qui a permis d'arrêter la boucle précédente (3b)
5 - revenir par la même méthode au point 3
6 - Faire la séquence 3-3a-3b-4 jusqu'à épuisement des cellules de la
feuille A

Exemple :
En feuille A, tu as les 5 valeurs 3, 4, 5, 6 et 8 dans les cellules A1 à
A5
En feuille B, tu as les 4 valeurs 1, 3, 5 et 7 dans les cellules B1 à B4

L'algorythme est le suivant :

Début de Traitement
INDICE_B = 1
POUR I =1 à 5 par pas de 1
POUR J = INDICE_B à 4 par pas de 1
SI B[J]>=A[I]
ALORS
SI B[J]=A[I]
ALORS
Ecriture dans Feuille A ou autre action
FIN_SI
INDICE_B=J (=> les autres valeurs de A sont au moins égales à
celle de B référencée par INDICE_B => servira dans la prochaine boucle J)
SORTIR BOUCLE J (ou J=4) (les autres valeurs de B ne peuvent
convenir => arrêt boucle)
FIN_SI
J SUIVANT
SI INDICE_B=4 ET A[I]>B[INDICE_B] (=> la valeur actuelle de A est
supérieure à la plus grande valeur de B (dernière) => plus de possibilité
de cas identiques)
ALORS
SORTIR BOUCLE I (ou I=5)
FIN_SI
I SUIVANT
Fin de Traitement

PS : 4 et 5 devront bien entendu être variabilisés

J'espère que cela pourra t'aider dans ta programmation VBA

Cordialement

"Patrice FY" a écrit dans le message
de news: 0b9a01c537aa$54e97080$
Bonjour,

Mon problème est le suivant:
J'ai environ 10000 lignes dans une feuille A et environ
9800 dans une feuille B. Je compare pour chaque ligne de
ma feuille A le contenu d'une cellule au contenu d'une
cellule de ma feuille B pour trouver les contenu
indentiques et mettre un e valeur dans une autre cellule
de ma feuille A. J'ai utilisé deux boucles FOR TO NEXT
style
For a = 1 to 10000
For b= 1 to 9800
je compare mes lignes feuille A range("a") et feuille
B range("b")et si égalité je mets une valeur dans ma
feuille A
Next b
Next a
Cela fonctionne parfaitement mais la macro dure de très
très longues heures !!!
Comment réduire la durée de cette macro ???? Merci





Avatar
LSteph
Re,
Alors, s'il s'avère qu'il n'y a qu'une colonne de Feuil1 à comparer avec une
de Feuil2
Sans VBA cela prend quelques secondes dans la colonne adjacente cette
formule en b2 à recopier vers le bas suffirait:
=NB.SI(Feuil2!$A$2:$A$9801;A2)

lSteph

"Patrice FY" a écrit dans le message
de news: 0b9a01c537aa$54e97080$
Bonjour,

Mon problème est le suivant:
J'ai environ 10000 lignes dans une feuille A et environ
9800 dans une feuille B. Je compare pour chaque ligne de
ma feuille A le contenu d'une cellule au contenu d'une
cellule de ma feuille B pour trouver les contenu
indentiques et mettre un e valeur dans une autre cellule
de ma feuille A. J'ai utilisé deux boucles FOR TO NEXT
style
For a = 1 to 10000
For b= 1 to 9800
je compare mes lignes feuille A range("a") et feuille
B range("b")et si égalité je mets une valeur dans ma
feuille A
Next b
Next a
Cela fonctionne parfaitement mais la macro dure de très
très longues heures !!!
Comment réduire la durée de cette macro ???? Merci
Avatar
Herdet
Bonjour LSteph,
Tu as donc donné une solution partielle à ton problème.
Le mieux est d'activer le magnétophone et d'enregistrer toutes les manip
faites à la main
- sélection de la plage de cellules
- écriture de la formule =NB.SI(Feuil2!$A$2:$A$9801;A2)
- recopie vers le bas (CtrlB ou Alt Enter en restant dans l'éditeur)
- exploiter les résultats et mettre les valeurs dans les autres cellules
- resélectionner la plage et effacer
- sélectionner A1
- après arrêt, nettoyer ou adapter la macro
En en-tête, tu peux ajouter :
Application.Calculation = xlCalculationManual ' au cas où il serait
désactivé
Application.ScreenUpdating = False 'pour accélérer un peu le traitement

... et à la fin
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True 'nécessaire avant un Msgbox ou un
affichage de boite de dialogue par exemple

Pour traiter plusieurs milliers de lignes, j'emploie toujours en priorité
cette méthode bien plus rapide que des boucles VBA
pour les fonctions de tableur INDEX, INDEX/EQUIV, NB, NBVAL, SOMME.SI,
etc...

Cordialement
Robert Dezan


"LSteph" a écrit dans le message de news:
%
Re,
Alors, s'il s'avère qu'il n'y a qu'une colonne de Feuil1 à comparer avec
une de Feuil2
Sans VBA cela prend quelques secondes dans la colonne adjacente cette
formule en b2 à recopier vers le bas suffirait:
=NB.SI(Feuil2!$A$2:$A$9801;A2)

lSteph

"Patrice FY" a écrit dans le message
de news: 0b9a01c537aa$54e97080$
Bonjour,

Mon problème est le suivant:
J'ai environ 10000 lignes dans une feuille A et environ
9800 dans une feuille B. Je compare pour chaque ligne de
ma feuille A le contenu d'une cellule au contenu d'une
cellule de ma feuille B pour trouver les contenu
indentiques et mettre un e valeur dans une autre cellule
de ma feuille A. J'ai utilisé deux boucles FOR TO NEXT
style
For a = 1 to 10000
For b= 1 to 9800
je compare mes lignes feuille A range("a") et feuille
B range("b")et si égalité je mets une valeur dans ma
feuille A
Next b
Next a
Cela fonctionne parfaitement mais la macro dure de très
très longues heures !!!
Comment réduire la durée de cette macro ???? Merci





Avatar
LSteph
Bonjour Herdet,

Ta proposition est intéressante mais manuellement
Un petit Doubleclic sur le petit carré noir en bas à droite suffit,

si on veut vraiment une macro la proposition de MD est bien efficace!

reste que
...je répondais à Patrice, qui ne s'est pas encore remanifesté pour
préciser sa demande
(voir les autres posts)
Il pourrai s'agir de lignes et non pas d'une seule colonne.

a+

lSteph





"Herdet" a écrit dans le message de news:

Bonjour LSteph,
Tu as donc donné une solution partielle à ton problème.
Le mieux est d'activer le magnétophone et d'enregistrer toutes les manip
faites à la main
- sélection de la plage de cellules
- écriture de la formule =NB.SI(Feuil2!$A$2:$A$9801;A2)
- recopie vers le bas (CtrlB ou Alt Enter en restant dans l'éditeur)
- exploiter les résultats et mettre les valeurs dans les autres cellules
- resélectionner la plage et effacer
- sélectionner A1
- après arrêt, nettoyer ou adapter la macro
En en-tête, tu peux ajouter :
Application.Calculation = xlCalculationManual ' au cas où il serait
désactivé
Application.ScreenUpdating = False 'pour accélérer un peu le
traitement

... et à la fin
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True 'nécessaire avant un Msgbox ou
un affichage de boite de dialogue par exemple

Pour traiter plusieurs milliers de lignes, j'emploie toujours en priorité
cette méthode bien plus rapide que des boucles VBA
pour les fonctions de tableur INDEX, INDEX/EQUIV, NB, NBVAL, SOMME.SI,
etc...

Cordialement
Robert Dezan


"LSteph" a écrit dans le message de news:
%
Re,
Alors, s'il s'avère qu'il n'y a qu'une colonne de Feuil1 à comparer avec
une de Feuil2
Sans VBA cela prend quelques secondes dans la colonne adjacente cette
formule en b2 à recopier vers le bas suffirait:
=NB.SI(Feuil2!$A$2:$A$9801;A2)

lSteph

"Patrice FY" a écrit dans le
message de news: 0b9a01c537aa$54e97080$
Bonjour,

Mon problème est le suivant:
J'ai environ 10000 lignes dans une feuille A et environ
9800 dans une feuille B. Je compare pour chaque ligne de
ma feuille A le contenu d'une cellule au contenu d'une
cellule de ma feuille B pour trouver les contenu
indentiques et mettre un e valeur dans une autre cellule
de ma feuille A. J'ai utilisé deux boucles FOR TO NEXT
style
For a = 1 to 10000
For b= 1 to 9800
je compare mes lignes feuille A range("a") et feuille
B range("b")et si égalité je mets une valeur dans ma
feuille A
Next b
Next a
Cela fonctionne parfaitement mais la macro dure de très
très longues heures !!!
Comment réduire la durée de cette macro ???? Merci









Avatar
LSteph
j'oubliais ...pour ce qui est de la mise à jour écran,
cela bien d'accord c'est une des améliorations potentielles de son code ...
...il va sûrement revenir et on aura + d'éléments ...
:-) à bientôt
lSteph

"Herdet" a écrit dans le message de news:

Bonjour LSteph,
Tu as donc donné une solution partielle à ton problème.
Le mieux est d'activer le magnétophone et d'enregistrer toutes les manip
faites à la main
- sélection de la plage de cellules
- écriture de la formule =NB.SI(Feuil2!$A$2:$A$9801;A2)
- recopie vers le bas (CtrlB ou Alt Enter en restant dans l'éditeur)
- exploiter les résultats et mettre les valeurs dans les autres cellules
- resélectionner la plage et effacer
- sélectionner A1
- après arrêt, nettoyer ou adapter la macro
En en-tête, tu peux ajouter :
Application.Calculation = xlCalculationManual ' au cas où il serait
désactivé
Application.ScreenUpdating = False 'pour accélérer un peu le
traitement

... et à la fin
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True 'nécessaire avant un Msgbox ou
un affichage de boite de dialogue par exemple

Pour traiter plusieurs milliers de lignes, j'emploie toujours en priorité
cette méthode bien plus rapide que des boucles VBA
pour les fonctions de tableur INDEX, INDEX/EQUIV, NB, NBVAL, SOMME.SI,
etc...

Cordialement
Robert Dezan


"LSteph" a écrit dans le message de news:
%
Re,
Alors, s'il s'avère qu'il n'y a qu'une colonne de Feuil1 à comparer avec
une de Feuil2
Sans VBA cela prend quelques secondes dans la colonne adjacente cette
formule en b2 à recopier vers le bas suffirait:
=NB.SI(Feuil2!$A$2:$A$9801;A2)

lSteph

"Patrice FY" a écrit dans le
message de news: 0b9a01c537aa$54e97080$
Bonjour,

Mon problème est le suivant:
J'ai environ 10000 lignes dans une feuille A et environ
9800 dans une feuille B. Je compare pour chaque ligne de
ma feuille A le contenu d'une cellule au contenu d'une
cellule de ma feuille B pour trouver les contenu
indentiques et mettre un e valeur dans une autre cellule
de ma feuille A. J'ai utilisé deux boucles FOR TO NEXT
style
For a = 1 to 10000
For b= 1 to 9800
je compare mes lignes feuille A range("a") et feuille
B range("b")et si égalité je mets une valeur dans ma
feuille A
Next b
Next a
Cela fonctionne parfaitement mais la macro dure de très
très longues heures !!!
Comment réduire la durée de cette macro ???? Merci