Discussion :

[jeanmidudu]

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

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

[summerfllore]

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 !

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

[jeanmidudu]

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

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
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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[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

[jeanmidudu]

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 :

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

[joe.levrai]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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)

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[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

[jeanmidudu]

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 :

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

[summerfllore]

Merci encore

[joe.levrai]

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


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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[jeanmidudu]

@joe.levrai

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)

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)