Discussion :

[relenaheero]

Bonjour,
Je ne trouve pas où est l'erreur dans mon programme pour que Excel se mette en "Ne répond pas".
Pouvez-vous m'aider ?
Le but est de mettre à jour la référence sur un onglet BDD où il n'y a que les chiffre et il manque la lettre. Je vais chercher la référence complète dans l'onglet CP
Merci d'avance.

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
Sub MAJ_CP_V2_Rhumba()
    '   Mise à jour CP dans onglet Base_de_données
    '   Suppression des espace de la colonne A
    Columns("A:A").Select
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Range("A1").Select
 
 
    '   Dernière ligne de la feuille Base_de_données et CP
    Dim DernLigneB As Long
    Dim DernLigneCP As Long
    Dim nbcar As Long
 
    DernLigneB = Sheets("Base_de_données").Range("A65536").End(xlUp).Row
    DernLigneCP = Sheets("CP").Range("A65536").End(xlUp).Row
 
    '   Mise à jour CP dans onglet Base_de_données
    For i = 2 To DernLigneCP
    For j = 2 To DernLigneB
 
    nbcar = Len(Sheets("Base_de_données").Range("A" & j).Value)
    If nbcar = 8 Then GoTo PasDeModification
    If nbcar = 7 Then
 
    If Sheets("CP").Range("A" & i).Value = "" Then GoTo PasDeModification
    If Abs(Left(Sheets("CP").Range("A" & i).Value, 7)) = Abs(Sheets("Base_de_données").Range("A" & j).Value) Then
    Sheets("Base_de_données").Range("A" & j).Value = Sheets("CP").Range("A" & i).Value
    End If
    End If
PasDeModification:
    Next j
    Next i
 
MsgBox ("Fin")

[EricDgn]

Bonjour,

Je ne vois pas pourquoi Excel se met en "ne répond pas", mais je trouve quand même le code assez compliqué. Un petit exemple de données serait utile (quelques lignes des colonnes A des 2 feuilles concernées).

Cordialement.

[droggo]

Bonjour,

Je ne lis JAMAIS les codes non indentés.

[Ben_L]

Bonjour,

Selon le nombre de lignes présentes sur chacun de vos onglets, il n'y a rien d'anormal à ce qu'Excel affiche "Ne répond pas".
Par exemple, avec 10 000 lignes sur chaque onglet, votre code réalisera 100 millions d'itérations...

Pour améliorer les performances :

• Supprimez les tests inutiles
• Minimisez le nombre d'accès aux cellules et bien choisir la méthode pour y accéder

Plusieurs façons de s'y prendre, mais avant tout, pourriez-vous indiquer le nombre de lignes par onglets ainsi que les éventuelles contraintes sur vos correspondances (i.e. une ligne de l'onglet "CP" peut-elle correspondre à plusieurs lignes de l'onglet "BDD" ? Est-il possible de trier la colonne A de chaque onglet avant d'évaluer les correspondances ? etc.) ?

Cdt

[ChristianBosch]

Bonjour, votre code fait que le nombre d'itérations est trop important. Vous avez imbriqué une boucle "for" dans une autre, c'est le meilleur moyens de faire des codes qui pataugent dans la semoule...

Premièrement utilisez des tableaux structurés afin d'avoir accès au références structurés, ce qui va vous réduire la zone de travail et vous permettre de travailler avec un outil propre et malléable.

Deuxièmement je vous propose d'utiliser une méthode moins "force brute" et de passer par la méthode "find" (elle s’apparente au "ctrl+f" dans excel)

En combinant les deux vous allez gagner énormément en temps d’exécution

[barpasc]

Utilisez des tableaux en mémoire au lieu de travailler avec les feuilles car ce n'est pas la même zone de mémoire et ça fait beaucoup de transfert très couteux pour le processeur. Dans un contexte multithread, vous risquez en effet de demander à l'OS d'interrompre un traitement le temps d'en faire un autre...

en vba et d'autres langages les tableaux sont plus rapide car ils utilisent les registres du cpu je crois et c'est x fois plus rapide que d'aller chercher des données dans la RAM.

dim V(9) as long 'tableaux de 10 valeurs pour des nombres entier
dim V(9) as string 'tabelaux de 10 valeurs pour du texte
...

Dimensionnez vos tableaux AVANT de rentrer dans une boucle car c'est possible d'utiliser redim mais c'est très couteux pour le processeur.

Vous avez porbablement le nombre de colonne avant. Pour le nombre de ligne, c'est très rare de connaitre le nombre d'occurence avant.. Le truc, c'est que vous ne pouvez pas redimensionner un tableaux de plusieurs dimensions (à ma connaissance). Avec votre code, au lieux d'écrire d'une feuille à l'autre, comptez le nombre d'occurence qui correspondra au nombre de ligne de votre tableaux. Le code sera plus long mais au niveau de l'execution c'est très très rapide, on est sur du millseconde pour accumuler un compteur dans une variable.

Avec ça vous avez votre nombre de ligne et vous pouvez dimensionner votre tableaux, faire les traitements qui sont dans votre code ou ceux qui ont servi au compteur et puis affichez le tableaux sur une feuille de calcul.

Si avec cette méthode, vous arrivez à bloquer le système d'exploitation, peut-être que optimiser le script qui rassemble les données dans un tableaux et fait le traitement pourra y remédier mais il faudra probabelement mieux faire le traitement dans une base de données

[relenaheero]

Bonjour,

Selon le nombre de lignes présentes sur chacun de vos onglets, il n'y a rien d'anormal à ce qu'Excel affiche "Ne répond pas".
Par exemple, avec 10 000 lignes sur chaque onglet, votre code réalisera 100 millions d'itérations...

Pour améliorer les performances :

• Supprimez les tests inutiles
• Minimisez le nombre d'accès aux cellules et bien choisir la méthode pour y accéder

Plusieurs façons de s'y prendre, mais avant tout, pourriez-vous indiquer le nombre de lignes par onglets ainsi que les éventuelles contraintes sur vos correspondances (i.e. une ligne de l'onglet "CP" peut-elle correspondre à plusieurs lignes de l'onglet "BDD" ? Est-il possible de trier la colonne A de chaque onglet avant d'évaluer les correspondances ? etc.) ?

Cdt

Bonjour,
J'ai un onglet avec 9554 lignes pour l'onglet BDD et l'autre avec 3780 lignes pour l'onglet CP. Oui c'est possible de trier la colonne A de chaque onglet.
Merci d'avance. Cordialement.

[Ben_L]

Bonjour,

Dans ce cas, commencez par trier chacune de vos sources de données (si vous ne connaissez pas la syntaxe, lancez l'enregistreur de macros, puis faîtes la manip à la main, en passant par l'onglet Données ; n'oubliez pas de nettoyer le code généré automatiquement).

Une fois cela fait, il ne vous reste plus qu'à définir deux références à des plages, et à les décaler en fonction du résultat de la comparaison de leur contenu. Par exemple, pour comparer la colonne A de Feuil1 à la colonne A de Feuil2, en commençant à la ligne 2 :

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
Dim Rng1 As Range, Rng2 As Range, Str1 As String, Str2 As String
Set Rng1 = ThisWorkbook.Sheets("Feuil1").Cells(2, 1)
Set Rng2 = ThisWorkbook.Sheets("Feuil2").Cells(2, 1)
Do While Rng1.Value <> "" And Rng2.Value <> ""
    Str1 = Left(Rng1.Text, 7)
    Str2 = Rng2.Text
    If Str2 = Str1 Then
        Rng2.Value = Rng1.Value
        Set Rng1 = Rng1.Offset(1, 0)
        Set Rng2 = Rng2.Offset(1, 0)
    ElseIf Str1 > Str2 Then
        Set Rng2 = Rng2.Offset(1, 0)
    Else
        Set Rng1 = Rng1.Offset(1, 0)
    End If
Loop

Attention néanmoins au fait que (je parle de mémoire...) le tri automatique d'Excel ne se base pas sur le même ordre des caractères que la comparaison de chaîne en VBA.
Si vous n'avez que des majuscules, minuscules et chiffres, cela ne devrait toutefois pas poser de problème.

Enfin, les réponses à cette discussion vous proposant d'utiliser des tableaux structurés ou de travailler en mémoire sont également bonness à prendre en compte. Ces méthodes ne sont pas implémentées dans le code proposé ci-dessus.

Cdt

[Thumb down]

Bonjour,
Tu fais une double boucle, ça consomme des ressources systèmes et windows n'arrive plus à gérer sa pagination mémoire ?

Places un Doevents dans la deuxième boucles.