Macro lente VBA

Version imprimable

1 pièce(s) jointe(s)

Macro lente VBA

Bonjour,

Actuellement j'ai créé une macro qui a pour but de comparer les valeurs des lignes successives d'une même colonne (la colonne 12), si les valeurs sont identiques alors je regarde une deuxième colonne de comparaison (la colonne 18) et je compare les valeurs successives entre elles. Si ces valeurs sont identique alors la colonne 19 affiche un "NON" sinon c'est un "OUI".

J'ai un gros problème de lenteur lors de l'exécution de ce code....

Voici le code pour plus de compréhension :
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 Sub Result_Cable_Presta() Application.ScreenUpdating = False Dim a&, b& Dim Carac1$, Carac2$ Dim NbLigne& NbLigne = Sheets("ROCA19").Range("A200000").End(xlUp).Row For a = 2 To NbLigne Carac1 = Sheets("ROCA19").Cells(a, 12) Carac2 = Sheets("ROCA19").Cells(a, 18) For b = 3 To NbLigne If Sheets("ROCA19").Cells(a, 19) = "oui" Then GoTo suivant End If[ATTACH=CONFIG]486211[/ATTACH] If Carac1 Like Sheets("ROCA19").Cells(b, 12) Then If Carac2 Like Sheets("ROCA19").Cells(b, 18) Then Sheets("ROCA19").Cells(a, 19) = "non" Else Sheets("ROCA19").Cells(a, 19) = "oui" End If End If Next b suivant: Next a Application.ScreenUpdating = True End Sub
En pièce jointe le résultat attendu avec les bonnes colonnes pour plus de compréhension :D

Merci beaucoup pour votre aide.

Pièce jointe 486214

25/06/2019, 09h46
BENNASR

1 pièce(s) jointe(s)

bonjour
tu compare le ligne i avec la ligne i-1 non?? et le résultat oui/non s'enregistre devant la ligne i ou la ligne i-1 ??
aussi en ligne L7 <> de la ligne L6 mais tu met un NON !!
merci de préciser ce que tu cherches à faire avec précision
et en général : si tu cherche à comparer la combinaison (colonne L colonne R ) de la ligne i par rapport à la ligne i-1 tu peux faire sans VBA
en colonne T (par exemple) tu fais la concaténation avec la formule : CONCATENER(L2;R2)
et colonne S (résultat ) tu fais la comparaison : SI(T3=T2;"NON";"OUI")
voila un exemple
25/06/2019, 11h36
Samuel24

Bonjour Bennasr et merci pour la réactivité.

Je prend les valeurs de la ligne i et les stocks dans les variables "Carac1" et "Carac2" puis je compare ces deux valeurs à la ligne i+1 puis i+2 et ainsi de suite.
Le résultat de cette comparaison s'enregistre sur la ligne i.

Pour les lignes L7 et L8 c'est un non car ce sont les même valeurs dans la colonne 1 et 2.

J'essaye d'optimiser cette macro pour que tout le monde puisse l'utiliser sans problème car le fichier concerné comporte plus de 20 000 lignes.
25/06/2019, 13h10
BENNASR

1 pièce(s) jointe(s)

Citation:

Je prend les valeurs de la ligne i et les stocks dans les variables "Carac1" et "Carac2" puis je compare ces deux valeurs à la ligne i+1 puis i+2 et ainsi de suite

donc la ligne 4 doit avoir un OUI parce qu'il différent de la ligne 5 non ??
je continue avec mon exemple et je modifie la formule ainsi :

Citation:

SI(T2=T3;"NON";"OUI")

un exemple ci joint et merci de dire l'erreur dans cette exemple pour ne pas tourner en rond

Bonjour

On utilisant une variable tableau

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub Result_Cable_Presta()
Dim Lastlig As Long, i As Long, j As Long
Dim Tb
 
Application.ScreenUpdating = False
With Worksheets("ROCA19")
    Lastlig = .Cells(.Rows.Count, 18).End(xlUp).Row
    Tb = .Range("L2:S" & Lastlig + 1)
    i = 1
    Do While i < Lastlig
        j = i
        Do While Tb(j, 1) & "|" & Tb(j, 7) = Tb(j + 1, 1) & "|" & Tb(j + 1, 7)
            Tb(j, 8) = "NON"
            j = j + 1
        Loop
        Tb(j, 8) = IIf(j = i, "OUI", "NON")
        i = j + 1
    Loop
    .Range("L2:S" & Lastlig) = Tb
End With
End Sub

25/06/2019, 13h31
Pierre Fauconnier

Salut.

Question: Pourquoi en VBA? Une formule suffirait, non? Et si par VBA, on "singe" Excel en plaçant la formule par VBA.
25/06/2019, 13h36
Samuel24

C'est un peu difficile à expliquer en gros :
La première étape est une comparaison de la valeur de la ligne i de la colonne L ("Valeur 1" pour la ligne 2) avec la valeur de la ligne i+1 de la colonne L ("Valeur 1" pour la ligne 3).
Si ces deux valeurs sont exactement les mêmes alors une comparaison de la valeur de la ligne i de la colonne R ("Valeur A" pour la ligne 2) avec la valeur de la ligne i+1 de la colonne R ("Valeur A" pour la ligne 3) aura lieu.
Si les deux valeurs sont les même alors se sera un "NON".

La ligne 4 possède la "Valeur 1" alors que la ligne 5 possède la "Valeur 2" donc ces deux lignes ne sont même pas comparées.

Dans mon exemple, prenons le cas des lignes 7, 8 et 9 nous pouvons voir que ces 3 lignes possèdent la même valeur dans la colonne L mais la ligne 9 ne possède pas la même valeur dans la colonne R que les lignes 7 et 8.
La macro ressortira alors un "NON" pour les lignes 7 et 8 car elles ont les mêmes valeurs mais elle sortira un "OUI" pour la ligne 9 car elle est unique.

Dans le cas d'une ligne ou elle ne possède aucun doublon dans la colonne L, ce sera automatiquement un "OUI"
25/06/2019, 13h42
Samuel24

Citation:

Envoyé par Pierre Fauconnier

Salut.

Question: Pourquoi en VBA? Une formule suffirait, non? Et si par VBA, on "singe" Excel en plaçant la formule par VBA.

Bonjour Pierre,

Je ne vois vraiment pas comment faire sans passer par VBA personnellement, si tu as des propositions je suis preneur :D

Citation:
Envoyé par mercatog
Bonjour

On utilisant une variable tableau
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Sub Result_Cable_Presta() Dim Lastlig As Long, i As Long, j As Long Dim Tb Application.ScreenUpdating = False With Worksheets("ROCA19") Lastlig = .Cells(.Rows.Count, 18).End(xlUp).Row Tb = .Range("L2:S" & Lastlig + 1) i = 1 Do While i < Lastlig j = i Do While Tb(j, 1) & "|" & Tb(j, 7) = Tb(j + 1, 1) & "|" & Tb(j + 1, 7) Tb(j, 8) = "NON" j = j + 1 Loop Tb(j, 8) = IIf(j = i, "OUI", "NON") i = j + 1 Loop .Range("L2:S" & Lastlig) = Tb End With End Sub
Bonjour mercatog,

Je ne comprend pas tout à ton programme mais malheureusement je trouve des "NON" sur des lignes qui devrait ressortir en "OUI", peut-être que des que tu trouve un "NON" ou un "OUI" sur une des valeurs de la colonne L tu ne traite pas les autres valeurs identiques de cette même colonne qui peuvent donner un résultat différent.

25/06/2019, 13h57
Pierre Fauconnier

Tu compares la ligne 2 avec la ligne 1 ou avec la ligne 3? Parce qu'à la lecture de ton énoncé, que je fasse l'un ou l'autre, je n'arrive pas au même résultat que celui que tu illustres
25/06/2019, 14h01
Samuel24

Citation:

Envoyé par Pierre Fauconnier

Tu compares la ligne 2 avec la ligne 1 ou avec la ligne 3? Parce qu'à la lecture de ton énoncé, que je fasse l'un ou l'autre, je n'arrive pas au même résultat que celui que tu illustres

Je compare la ligne 2 à la ligne 1, puis la ligne 3 à la ligne 1, puis la 4 à la ..... jusqu'à avoir comparé toutes les valeurs de la colonne L à la ligne 1 et ensuite je passe à la ligne 2 de la colonne L et j'effectue le même calcul...
25/06/2019, 14h08
Pierre Fauconnier

1 pièce(s) jointe(s)

Alors, je ne comprends pas la valeur NON à la ligne 7 :koi:

Je ne comprends pas bien pourquoi remonter chaque fois sur la ligne 1 du tableau. Les valeurs semblent triées sur la première colonne puis sur la seconde de ton test. Dès lors, comparer une ligne avec la précédente n'est-il pas suffisant?

Pièce jointe 486342
25/06/2019, 14h13
Samuel24

La ligne 7 sera un "NON" comme la ligne 8 car elles possèdent les mêmes valeurs sur les deux colonnes.

Effectivement les valeurs de la colonne1 sont triées
25/06/2019, 14h16
Pierre Fauconnier

Tu dis que tu compares la 7 avec la 6 et la 8 avec la 7 (voir ta réponse à ma question).

Ou alors, je ne comprends pas ton énoncé, alors.
25/06/2019, 14h22
Samuel24

Je compare de haut en bas donc :
la ligne 2 avec la ligne 3
la ligne 2 avec la ligne 4
la ligne 2 avec la ligne 5
la ligne 2 avec la ligne X
Dès que la ligne 2 à était comparée à toutes les autres lignes, je compare :
la ligne 3 avec la ligne 4
la ligne 3 avec la ligne 5
la ligne 3 avec la ligne X
...
25/06/2019, 14h27
Pierre Fauconnier

Je ne comprends pas pourquoi tu dois comparer la 1 à TOUTES les autres... Dès que tu as une rupture, ça passera à OUI.

Ce que tu dis dans ton dernier message n'est pas logique avec le NON de la ligne 4, puisqu'il y a rupture sur la 5 en première colonne.
25/06/2019, 14h36
Samuel24

Effectivement je croit comprendre ce que tu veux dire, le but est de trouver toutes les valeurs identiques de la colonne1 et de comparer leurs valeurs de la colonne2. Si des lignes possèdent un doublon dans la colonne2 alors elles sont taguées "NON"

J'espère avoir étais un minimum clair cette fois :ptdr:
25/06/2019, 14h51
Pierre Fauconnier

1 pièce(s) jointe(s)

Si tu as voulu dire: je teste les lignes en doublon sur deux colonnes, alors oui, c'était plus clair... Et dans ce cas, il n'est pas nécessaire qu'elles soient triées

Pièce jointe 486374
25/06/2019, 15h13
mercatog

Citation:

Envoyé par Samuel24

Bonjour mercatog,

Je ne comprend pas tout à ton programme mais malheureusement je trouve des "NON" sur des lignes qui devrait ressortir en "OUI", peut-être que des que tu trouve un "NON" ou un "OUI" sur une des valeurs de la colonne L tu ne traite pas les autres valeurs identiques de cette même colonne qui peuvent donner un résultat différent.

Je pars du principe comme l'illustre ton exemple de valeurs identiques successives des colonnes L et R.
25/06/2019, 15h19
Samuel24

C'est parfait Pierre, un grand merci à tout le monde pour l'aide.
26/06/2019, 16h29
Samuel24

1 pièce(s) jointe(s)

Bonjour,

désolé de revenir vers vous, je vais essayer d'exprimer mon besoin le plus clairement possible :
Pour une même valeur trouvée en colonne 1, si je trouve en colonne 2 des valeurs identiques alors j'affiche "NON" en colonne 3 sinon j'affiche "OUI"
Il n'y a pas de notion de lignes successives.
J'ai refait un exemple de résultat attendu.

Pièce jointe 486741

Par exemple : j'affiche un NON en face de la Valeur 1 car en colonne 2 je n'ai que "Valeur A"
Pour la Valeur 2, J'affiche OUI car en colonne 2 j'ai "Valeur A" et "Valeur B"
Pour la Valeur 3, j'aimerais afficher OUI sur toutes les lignes car on trouve "Valeur C" et "Valeur D"
Pour la Valeur 4, j'affiche OUI car J'ai Valeur E et Valeur F

Pour résumer, je veux afficher "NON" sur l'ensemble des lignes correspondantes à une valeur uniquement quand je trouve, les mêmes valeurs en colonne 2.

Est-ce que c'est plus clair ?
26/06/2019, 16h37
Pierre Fauconnier

1 pièce(s) jointe(s)

Ok...

On va donc compter le nombre de lignes du tableau qui correspondent à la ligne sur laquelle la formule se trouve, et comparer ce nombre à celui des lignes qui ont la même valeur en col1 que celle de la ligne de la formule. Si égalité, NON, sinon, OUI

Pièce jointe 486749

Bonjour Pierre,

Après beaucoup de tests, ta formule convient parfaitement mais j'aimerais diminuer le temps d'exécution et l'incrémenter en VBA.
Pour le temps d'exécution j'utilise " Application.ScreenUpdating = False " qui fonctionne parfaitement.
Le problème est l'incrémentation en VBA, j'arrive à afficher la formule mais le résultat est sous la forme "#NOM?", elle ne s'exécute pas.

voici mon code :
Code:

1 2 3 4 Range("T2").Select ActiveCell.FormulaR1C1 = _ "=SI(NB.SI.ENS([Nomproduittechnique],[@[Nomproduittechnique]],[cable],[@cable])=NB.SI.ENS([Nomproduittechnique],[@[Nomproduittechnique]]),""NON"",""OUI"")" Range("T2").Select
As-tu une idée ?

Salut

Tu devrais utiliser Formula à la place de FormulaR1C1. Formula et FormulaR1C1 requièrent que la formule soit en anglais. En plus, R1C1 impose un adressage des formules R1C1 que perso je n'aime pas.
Code:

1 2 ActiveCell.Formula = _ "=If(Countifs([Nomproduittechnique],[@[Nomproduittechnique]],[cable],[@cable])=countifs([Nomproduittechnique],[@[Nomproduittechnique]]),""NON"",""OUI"")"
Il n'est pas nécessaire d'activer la cellule pour utiliser la formule. Tu gagneras beaucoup de temps à ne pas activer tes cellules. Tu peux de suite utiliser range("t2").formula="...". Tu peux même placer la formule en une seule passe sur la plage voulue: range("t2:t1000").formula="..."En plus du screenupdating, tu pourrais désactiver le calcul pour accélérer le traitement
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 Sub test() Dim CalculationMode As XlCalculation On Error GoTo Catch CalculationMode = Application.Calculation Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Range("t2:t1000").Formula = "..." Catch: Application.Calculation = CalculationMode Application.ScreenUpdating = True End Sub

Merci pour les infos, j'avais vu de mon côté que le R1C1 imposait un changement des ";" en "," car c'est en anglais.

J'ai essayé avec ton code mais pas moyen de le faire marcher, il s'exécute mais rien ne s'affiche.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Private Sub Filtre_Occupation()
    Dim CalculationMode As XlCalculation
 
    On Error GoTo Catch
    CalculationMode = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Range("T2").Formula = _
    "=If(Countifs([Nomproduittechnique],[@[Nomproduittechnique]],[cable],[@cable])=countifs([Nomproduittechnique],[@[Nomproduittechnique]]),""NON"",""OUI"")"
 
Catch:
  Application.Calculation = CalculationMode
  Application.ScreenUpdating = True
 
End Sub

Quand je commente la partie du calcul, je trouve une erreur d'exécution '1004' définie par l'application ou par l'objet

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Private Sub Filtre_Occupation()
'    Dim CalculationMode As XlCalculation
'
'    On Error GoTo Catch
'    CalculationMode = Application.Calculation
'    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Range("T2").Formula = _
    "=If(Countifs([Nomproduittechnique],[@[Nomproduittechnique]],[cable],[@cable])=countifs([Nomproduittechnique],[@[Nomproduittechnique]]),""NON"",""OUI"")"
 
'Catch:
'  Application.Calculation = CalculationMode
  Application.ScreenUpdating = True
 
End Sub

Une idée ?

02/07/2019, 16h47
Pierre Fauconnier

1 pièce(s) jointe(s)

Je n'ai pas ton fichier, donc je ne sais pas reproduire ce que tu fais

Code:

Range("tableau1[colonne1]").Formula = "=IF(COUNTIFS([NomProduitTechnique],[@NomProduitTechnique],[Cable],[@Cable])=COUNTIFS([NomProduitTechnique],[@NomProduitTechnique]),""OUI"",""NON"")"

Pièce jointe 488084

Cela dit, je vois que tu mets cela en T2. Si T2 fait partie de ta table de données, ok. Sinon, ça ne saurait pas fonctionner tel quel. La formule que je t'ai donnée a été rédigée pour être utilisée dans une colonne formulée du tableau.
03/07/2019, 15h05
Samuel24

bonjour Pierre et merci pour tout, ça marche parfaitement et le traitement et bien moins long. :D

Fuseau horaire GMT +2. Il est actuellement 01h24.