Problème dans programme VBA Excel se mets en "Ne répond pas"

Version imprimable

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:

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

16/09/2021, 11h15
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.
16/09/2021, 11h50
droggo

Bonjour,

Je ne lis JAMAIS les codes non indentés. :mouarf:
16/09/2021, 12h24
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
16/09/2021, 13h26
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
20/09/2021, 10h42
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
21/09/2021, 14h03
relenaheero
Citation:
Envoyé par 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
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.

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:

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

24/09/2021, 11h02
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.