Code VBA remplacement automatique par une liste de mots

Version imprimable

Voir 40 message(s) de cette discussion en une page

Il y avait bon nombre d'erreurs dans le code de Joe.levrai, je les ai toutes corrigées. A tester donc :

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
 
Option Explicit
 
Sub Valeurs_remplacer()
    Dim sh As Worksheet, Feuille As Worksheet
    'Dim Tableau_Traitement()
    Dim Tableau_ValeurDepart() As String, Tableau_ValeurArrivee() As String
    Dim inf As Long, sup As Long, i As Long
 
    Set sh = Sheets("Info.complémentaires")
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    With sh
        sup = .Range("A" & Rows.Count).End(xlUp).Row + 1
        ReDim Tableau_ValeurDepart(sup)
        ReDim Tableau_ValeurArrivee(sup)
        For i = 0 To sup
            Tableau_ValeurDepart(i) = .Range("A" & i + 1).Value
            Tableau_ValeurArrivee(i) = .Range("B" & i + 1).Value
        Next i
        inf = LBound(Tableau_ValeurDepart, 1)
    End With
 
    For Each Feuille In Worksheets
        If Feuille.Name <> "Info.complémentaires" And Feuille.Name <> "Fiche UF*" Then
            For i = inf To sup
                Feuille.Cells.Replace Tableau_ValeurDepart(i), Tableau_ValeurArrivee(i), xlWhole
            Next i
        End If
    Next Feuille
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Et si tu pouvais nous joindre ton fichier stp, sinon dis nous juste que tu ne peux pas :P

21/06/2016, 15h19
summerfllore

Bravo !!!

Mille merci, ton code fonctionne parfaitement !!!

C'est vraiment génial cela fait 2 semaines que je cherchais... j'aurai du venir plus tot ^^
Non mais vraiment ca va me faire gagner un temps fou !:ptdr:

Merci encore c'est super, maintenant je vais étudier ton code pour le comprendre car j'aimerai vraiment réussir à intégrer le langage vba :mrgreen:

Je te le commente un peu pour t'aider à le comprendre :

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
35
36
 
Option Explicit 'Oblige la déclaration des variables, pour éviter plein d'erreurs bêtes
 
Sub Valeurs_remplacer()
    'Déclaration des variables
    Dim sh As Worksheet, Feuille As Worksheet      'Déclarer les feuilles et les utiliser dans des variables est plus rapide qu'un accès dynamique avec leur nom
    Dim Tableau_ValeurDepart() As String, Tableau_ValeurArrivee() As String 'De même pour l'utilisation des tableaux à la pace des Range, gain de temps important
    Dim inf As Long, sup As Long, i As Long 'inf et sup servent aussi à accélérer le code, i sert à parcourir les cellules/tableaux
 
    Set sh = Sheets("Info.complémentaires") 'On met la feuille la plus utilisée dans une variable afin d'y accéder plus efficacement (vitesse et lisibilité du code)
    Application.ScreenUpdating = False         'On empêche l'application de mettre à jour l'affichage (exécution plus rapide)
    Application.Calculation = xlCalculationManual 'Idem pour les calculs de formules
 
    With sh 'On indique que tout ce qui commence par " . " appartient à l'objet sh (qui est ta feuille déclarée précédemment)
        sup = .Range("A" & Rows.Count).End(xlUp).Row + 1 'On récupère le nombre de valeurs à modifier, qui sera le nombre d'éléments sur lesquels bouclés mais aussi la borne supérieur de tes tableaux
        ReDim Tableau_ValeurDepart(sup) 'Du coup tes tableaux doivent contenir au moins ce nombre d'éléments
        ReDim Tableau_ValeurArrivee(sup) 'Idem
        For i = 0 To sup 'Pour chaque valeur contenue dans ta liste
            Tableau_ValeurDepart(i) = .Range("A" & i + 1).Value 'On remplit les tableaux
            Tableau_ValeurArrivee(i) = .Range("B" & i + 1).Value 'Idem
        Next i
        inf = LBound(Tableau_ValeurDepart, 1) 'On récupère la borne inférieure de ton tableau
    End With
 
    For Each Feuille In Worksheets 'Pour chaque feuille du classeur
        If Feuille.Name <> "Info.complémentaires" And Feuille.Name <> "Fiche UF*" Then 'Si son nom est différent de ... (tu as compris) alors
            For i = inf To sup 'Pour chaque élément de tes tableaux
                Feuille.Cells.Replace Tableau_ValeurDepart(i), Tableau_ValeurArrivee(i), xlWhole 'Dans la feuille, on remplace comme tu l'as demandé la valeur de la
' colonne de gauche en fait, par la valeur de la colonne de droite 
            Next i
        End If
    Next Feuille
'On permet de nouveau la mise à jour de l'affichage et des formules
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Une chose qui serait intéressante, ce serait de savoir combien de temps cela met maintenant.
Pour ça, dans le code, ajoute ceci :

Code:

1
2
3
4
5
6
7
 
'au tout début, avant les déclarations
Dim debut As single
debut = timer
 
'et à la toute fin, juste avant de sortir de la fonction :
MsgBox("Temps d'exécution : " & Timer - debut)

Voili voilou, si tu as d'autres questions, n'hésite pas ;)

P.S : N'oublie pas de remercie Joe.levrai qui a quand même fourni une bonne partie du code.

21/06/2016, 15h59
summerfllore

j'ai 261 en temps.. c'est un peu long mais bon ca fonctionne :)

Oui oui merci à vous deux et a tous ceux qui ont mis leur idée et des pistes :)

Merci pour ton temps pour les explications c'est super :P

J'ai peut-être un moyen pour accélérer encore un peu (pas sûr), voici le code à tester :
une petite chose qui peut avoir son importance, ce code ne respecte pas la casse (Majuscules/minuscules) de tes valeurs. En gros, "CouCoU" = "coucou" dans ta recherche.

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
35
36
 
Option explicit
 
Sub Valeurs_remplacer()
    'Déclaration des variables
    Dim sh As Worksheet, Feuille As Worksheet      'Déclarer les feuilles et les utiliser dans des variables est plus rapide qu'un accès dynamique avec leur nom
    Dim Tableau() As String 'On a un seul tableau avec les 2 colonnes
    Dim inf As Long, sup As Long, i As Long 'inf et sup servent aussi à accélérer le code, i sert à parcourir les cellules/tableaux
 
    Set sh = Sheets("Info.complémentaires") 'On met la feuille la plus utilisée dans une variable afin d'y accéder plus efficacement (vitesse et lisibilité du code)
    Application.ScreenUpdating = False         'On empêche l'application de mettre à jour l'affichage (exécution plus rapide)
    Application.Calculation = xlCalculationManual 'Idem pour les calculs de formules
 
    With sh 'On indique que tout ce qui commence par " . " appartient à l'objet sh (qui est ta feuille déclarée précédemment)
        sup = .Range("A" & Rows.Count).End(xlUp).Row + 1 'On récupère le nombre de valeurs à modifier, qui sera le nombre d'éléments sur lesquels bouclés mais aussi la borne supérieur de tes tableaux
        ReDim Preserve Tableau(sup,1) 'Du coup le tableau doit contenir au moins sup ligne et 2 colonnes (la première occurrence est toujours 0)
        For i = 0 To sup 'Pour chaque valeur contenue dans ta liste
            'On remplit le tableau
            Tableau(i,0) = .cells(i + 1, 1).value 'On remplit les tableaux
            Tableau(i,1) = .cells(i + 1, 2).value 'Idem
        Next i
        inf = LBound(Tableau, 1) 'On récupère la borne inférieure de ton tableau
    End With
 
    For Each Feuille In Worksheets 'Pour chaque feuille du classeur
        If Feuille.Name <> "Info.complémentaires" And Feuille.Name <> "Fiche UF*" Then 'Si son nom est différent de ... (tu as compris) alors
            For i = inf To sup 'Pour chaque élément de tes tableaux
                Feuille.Cells.Replace Tableau(i,0), Tableau(i,1), xlWhole, xlByRows, false 'Dans la feuille, on remplace comme tu l'as demandé la 
'valeur de la colonne de gauche en fait, par la valeur de la colonne de droite, la casse n'est pas respectée
            Next i
        End If
    Next Feuille
'On permet de nouveau la mise à jour de l'affichage et des formules
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Teste à nouveau le temps avec le précédent code donné et si tu obtiens des résultats proches, fais le teste plusieurs fois pour voir lequel est réellement le plus rapide

Citation:

Envoyé par jeanmidudu

Il y avait bon nombre d'erreurs dans le code de Joe.levrai, je les ai toutes corrigées.

Bonjour,

peux-t-on avoir la liste des "bon nombre d'erreurs" ?

Certes, j'ai écrit le code à la volée et ne l'ai pas testé ... cependant à part avoir oublié la propriété .Value sur les deux lignes où je crée les tableaux de valeurs, je visualise mal ce qui aurait posé problème ? Le End Sub écrit par erreur au lieu d'un End With étant signalé à l'exécution de façon très précise (le terme sera surligné et le message dira qu'il faut mettre "End With" grosso modo)

d'ailleurs, tu n'as pas corrigé ce point, tu l'as contourné en utilisant une boucle qui passe sur chaque cellule pour alimenter le tableau, ce qui est un peu moins performant

J'aime les critiques, elles permettent d'apprendre ... mais uniquement quand elles sont développées.
Code:

1 2 Tableau_ValeurDepart = .Sheets("Info.complémentaires").UsedRange.Columns(1).Value Tableau_ValeurArrivee = .Sheets("Info.complémentaires").UsedRange.Columns(2).Value
J'ai vu également que tu voulais rassembler les valeurs d'entrée et de sortie dans un seul tableau, là encore une boucle est chronophage quand on peut le faire d'une seule passe :
Code:

1 2 3 4 5 6 With ThisWorkbook.Sheets("Info.complémentaires").UsedRange.Columns(1) Tableau_DepartEtArrivee = .Resize(.Rows.Count, 2).Value End With Debug.Print Tableau_DepartEtArrivee(1, 1) & " : première valeur à chercher" Debug.Print Tableau_DepartEtArrivee(1, 2) & " : première valeur de remplacement"
on gagne en temps d'exécution, en ligne de code et en lisibilité
(cette dernière phrase n'est qu'un avis personnel)

22/06/2016, 08h25
jeanmidudu

@joe.levrai

Oups là, je ne voulais pas t'offenser, désolé. Je n'avais pas moi-même pensé à utiliser les tableaux...

Pour les erreurs je ne me souviens plus de tout mais

Code:

Tableau_ValeurDepart = .Sheets("Info.complémentaires").UsedRange.Columns(1).Value

que ce soit avec ou sans le " .value " retourne l'erreur 13 incompatibilité de type à cause du " UsedRange "
Et j'avais bien remarqué le problème du " End sub " (c'est d'ailleurs le truc qui m'a frappé et m'a fait reprendre ton code pour le tester), mais le changer en "End With" n'a en rien résolu l'erreur précédente.

Ensuite il y avait cette partie ci :

Code:

Feuille.UsedRange.Replace Tableau_ValeurDepart(i, 1), Tableau_ValeurArrivee(i, 1), xlWhole

Tu remarqueras que là l'erreur retournée est l'erreur 9 : l'indice n'appartient pas à la sélection. Tu ne peux pas accéder à la colonne 1 de tes tableaux puisqu'ils n'en ont pas.
Il en va de même lors de l'utilisation de LBound et UBound (je considère tout cela comme une seule et même erreur)

C'est vrai qu'en fait ça ne fait pas beaucoup d'erreurs. Bref.
Sinon, petite observation personnelle, j'ai supprimé l'utilisation de ThisWorkbook pour accélérer l'exécution, mais son utilisation est un peu plus sécuritaire (bien que toujours sujette à problèmes).

Et pour finir, je suis d'accord avec toi sur le fait que boucler sur les lignes pour remplir le tableau soit très consommateur.
22/06/2016, 08h51
summerfllore

Bonjour tout le monde,

En fait le temps d'éxécution était dit a des "agent de services" qui se sont retrouver dans toutes les cellules vides de mon document (j'ai fait une mauvaise manip mais impossible de dire laquelle) ce qui a fortement alourdie le fichier du coup....

Autrement le code ne met que quelques seconde a tourner , mais merci en tout cas pour vos idées et le temps passer sur mon problème. C'est vraiment très gentil, merci à vous deux :lol::mouarf::zoubi:

Voilà comment faire sans utiliser de boucle (et en utilisant des tableaux de 1 à la dernière ligne, pour faciliter la correspondance avec les indices dans les feuilles)

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
 
Option explicit
 
Sub Valeurs_remplacer()
    'Déclaration des variables
    Dim sh As Worksheet, Feuille As Worksheet      'Déclarer les feuilles et les utiliser dans des variables est plus rapide qu'un accès dynamique avec leur nom
    Dim Tableau 'On a un seul tableau avec les 2 colonnes
    Dim sup As Long, i As Long 'inf et sup servent aussi à accélérer le code, i sert à parcourir les cellules/tableaux
 
    Set sh = Sheets("Info.complémentaires") 'On met la feuille la plus utilisée dans une variable afin d'y accéder plus efficacement (vitesse et lisibilité du code)
    Application.ScreenUpdating = False         'On empêche l'application de mettre à jour l'affichage (exécution plus rapide)
    Application.Calculation = xlCalculationManual 'Idem pour les calculs de formules
 
    With sh 'On indique que tout ce qui commence par " . " appartient à l'objet sh (qui est ta feuille déclarée précédemment)
        sup = .Range("A" & Rows.Count).End(xlUp).Row 'On récupère le nombre de valeurs à modifier, qui sera le nombre d'éléments sur lesquels bouclés mais aussi la borne supérieur de tes tableaux
        Set Tableau = .Range("A1:B" & sup) 'On remplit le tableau avec toutes les valeurs
    End With
 
    For Each Feuille In Worksheets 'Pour chaque feuille du classeur
        If Feuille.Name <> "Info.complémentaires" And Feuille.Name <> "Fiche UF*" Then 'Si son nom est différent de ... (tu as compris) alors
            For i = 1 To sup 'Pour chaque élément de tes tableaux
                Feuille.Cells.Replace Tableau(i, 1), Tableau(i, 2), xlWhole, xlByRows, False 'Dans la feuille, on remplace comme tu l'as demandé la
'valeur de la colonne de gauche en fait, par la valeur de la colonne de droite, la casse n'est pas respectée
            Next i
        End If
    Next Feuille
'On permet de nouveau la mise à jour de l'affichage et des formules
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

C'est vrai que sans boucle c'est beaucoup plus rapide (entre 5 et 7 fois plus rapide pour 10 000 instructions)

22/06/2016, 10h13
summerfllore

Merci encore :)

Bonjour,

jeanmidudu, je n'ai pas du tout été offensé, mais c'est frustrant de pas savoir ce qui a été nettoyé, moi je veux continuer d'apprendre :zoubi:

Attention néanmoins à certains points :

- quand tu injectes une matrices provenant d'Excel dans un tableau VBA ... même si il n'y a qu'une colonne (ou une ligne), le tableau est en deux dimensions !

pour te le montrer, prend une feuille vierge et écrit A / B / C en A1 / A2 / A3 (ça fonctionne aussi si tu mets ça en ligne)

passe ce code
Code:

1 2 3 4 5 6 7 8 9 10 Sub toto() Dim Tabl() Tabl = Cells(1, 1).CurrentRegion.Value ' erreur 'MsgBox Tabl(1) MsgBox Tabl(1, 1) End Sub
- je n'ai pas besoin de typer mes tableaux "As String" et je peux les laisser en variant

prend une feuille vierge, écrit A / B / C en A1 / A2 / A3 et Z / Y / C en B1 / B2 / B3
passe ce code
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Sub touto() Dim Tableau_DepartEtArrivee() With ThisWorkbook.Sheets("Feuil1").UsedRange.Columns(1) Tableau_DepartEtArrivee = .Resize(.Rows.Count, 2).Value End With ' on va afficher 'A : première valeur à chercher 'Z : première valeur de remplacement Debug.Print Tableau_DepartEtArrivee(1, 1) & " : première valeur à chercher" Debug.Print Tableau_DepartEtArrivee(1, 2) & " : première valeur de remplacement" End Sub
Maintenant, il est avéré que tu as rencontré des erreurs (tu ne les as pas inventé quand même), et j'ai à coeur d'aller au bout de la chose
Disposes-tu encore de l'exemple que tu as fait tourner (une feuille avec les données + la procédure) et qui plantait ?

Je sais que parfois, dans certains contextes, je dois forcer le type d'un tableau ... mais ça m'est arrivé une fois ou deux dans ma "jeune" carrière et j'ai jamais compris pourquoi il butait sur ça.

EDIT : je viens de voir que tu as reçu des -1 sur certains de tes messages, ce n'est pas moi, j'ai cru bon de le préciser ;)

23/06/2016, 08h19
jeanmidudu

@joe.levrai

Citation:

quand tu injectes une matrices provenant d'Excel dans un tableau VBA ... même si il n'y a qu'une colonne (ou une ligne), le tableau est en deux dimensions !

C'est vrai, mille excuses je n'avais pas fait attention (tu remarqueras d'ailleurs que c'est aussi ce que j'ai fait dans le dernier code)

Citation:

je n'ai pas besoin de typer mes tableaux "As String" et je peux les laisser en variant

C'est également vrai mais je ne crois pas avoir dit qu'il s'agissait d'une erreur.

Je n'ai plus le code, mais j'avais seulement réutilisé ce qui se trouve dans ce sujet (les feuilles Excel comme le code). La seule chose que je n'explique pas, c'est l'erreur d'incompatibilité de type (13) avec l'utilisation de UsedRange. Après au cours du développement de certaines applications, je sais que les problèmes peuvent survenir des options par défaut d'Excel, des références absentes (je ne passe que ce soit le cas ici), ou même simplement du transfert du classeur d'un dossier à un autre.... Il m'est arrivé qu'un ensemble de données soit transformé en tableau (mise en forme tableau) à cause d'un simple copier-coller (ce que je n'explique pas non plus d'ailleurs)

Voir 40 message(s) de cette discussion en une page