Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

[sdevers]

Bonjour chers forumeurs,

Je vous explique le but à atteindre: j'ai un fichier excel qui est à traduire dans trois langues. Via un petit formulaire, l'utilisateur décide de la langue dans laquelle il veut travailler aujourd'hui. La langue est ensuite envoyée vers une cellule C1 de la sheet "data". Le 1 pour le Français, le 2 pour le Néerlandais et le 3 pour l'Anglais.

J'ai une autre feuille où j'ai traduit tous les termes de toutes les feuilles dans les 3 langues. Par défaut mon fichier est en français mais j'ai déjà créé les tables de concordance dans la sheet "traduction". J'ai plus de 500 traductions en tout...

Mon but serait de créer une macro qui ferait un rechercher/remplacer pour tous les termes de ma colonne A de ma sheet "traduction" (la colonne A étant celle en français) et qui remplacerait ces termes dans toutes les autres pages de mon classeur. Evidemment, le rechercher/remplacer serait influencé par le choix de la langue (2ème colonne pour le néerlandais et 3ème colonne pour l'anglais).

J'ai vu sur différents forums (celui-ci et d'autres) les explications concernant la macro rechercher/remplacer mais je ne vois pas comment je l'adapterais pour qu'il choississe le premier terme de ma colonne A de la sheet "traduction", le remplace partout puis passe au 2ème terme, etc.

Enfin si, j'ai un début de solution mais ça consisterait à copier 500 fois ma formule. Je suppose que ce n'est pas la bonne solution...
J'avais au départ pensé changer les termes dans toutes mes sheets en utilisant une fonction index (avec des fonctions equiv) mais ça veut dire que je dois adapter toutes mes pages. De nouveau ça serait fastidieux. Et puis, ça m'obligerait à modifier la formule si je fais des ajouts par la suite alors qu'avec une macro, je pourrais me contenter de venir ajouter une ligne à ma sheet "traduction" et elle serait automatiquement adaptée avec mon rechercher/remplacer.

Je vous annexe juste trois pages de mon fichier, la page "data" avec juste le choix de la langue, celle avec toutes les traductions et une des pages de contenu qui devra être traduite. Il y a des liens qui ne fonctionneront plus pour vous mais c'est sans importance pour le point qui nous occupe Vous n'avez pas le formulaire avec le choix de la langue. Celui-ci est déjà plein de macros et je ne souhaitait pas vous encombrer Vous avez juste la première ligne avec le choix de la langue.

Je vous remercie d'avance pour vos solutions ou vos idées. Toute amélioration est bonne à prendre.

Bonne soirée à vous,

 

[CPk]

Re : Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

Bonjour, une petite macro à tester valable que pour le support présenté.

Sub traduction()
Dim c As Range
colf = Feuil1.[C1]
For Each c In Sheet6.UsedRange
If c <> "" Then
Set trouve = Feuil2.Cells.Find(c, lookat:=xlWhole)
If Not trouve Is Nothing Then
c = Feuil2.Cells(trouve.Row, colf + 1)
End If
End If
Next c
End Sub

 

[sdevers]

Re : Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

Merci CPk. J'ai pu adapter ton code à mes besoins. Mais je me rends compte d'un problème auquel je n'avais pas pensé...

Une fois que mes termes sont traduits dans une langue (par exemple en anglais, colonne 3), ma macro "traduction" ne sait pas faire le chemin inverse et revenir en français.

J'imagine le cas où un utilisateur se trompe dans mon formulaire et choisit une mauvaise langue. Il doit recommencer depuis le début (en l'occurence, même en changeant la langue via le formulaire, il ne sait pas faire en sorte de traduire en sens inverse tous les titres). Il doit obligatoirement recommencer le fichier.

Une idée lumineuse ?

Je n'ai pas encore cherché mais si en plus tu as la solution pour créer une boucle sur certaines feuilles (pas toutes malheureusement) pour pas que la macro soit limitée à la Sheet6 ("actif (1)")...

Encore merci pour ton aide.

 

[CPk]

Re : Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

Bonjour, pour changer de langue, il suffit (dans mon fichier) de changer la valeur de C1 (en feuille data) et de lancer la macro.Dans le votre je ne sais pas car en adaptant vous avez peut-être déroger au fonctionnement de celle-ci

Pour la dernière question, vous pouvez utilisez choose ou array.

 

[sdevers]

Re : Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

Bonsoir CPk, Bonjour les autres,

Je me permet de revenir sur le sujet lancé en début de semaine. Je te confirme que ton code fonctionnait très bien et que ce sont mes adaptations qui ont créé des problèmes. Je ne l'avais pas examiné il faut croire. J'en suis donc revenu à ton code (légèrement adapté au nom de mes pages).

Sub traduction()
Dim c As Range
colf = Sheet1.Range("C1")

For Each c In Sheet6.UsedRange
If c <> "" Then
Set trouve = Sheet2.Cells.Find(c, lookat:=xlWhole)

If Not trouve Is Nothing Then
c = Sheet2.Cells(trouve.Row, colf + 1)
End If
End If
Next c
End Sub​

J'ai également essayé d'étendre le code traduction() à d'autres pages grâce à la fonction Array comme tu l'avais suggéré. J'ai d'abord fait un test avec juste array, et ça fonctionne super bien.

Sub arrayworksheet()
Dim Ws As Worksheet

For Each Ws In ThisWorkbook.Worksheets(Array("actif (1)", "actif (2)"))
'Inscrit la valeur 1 dans chacune des feuilles cibles
Ws.Range("O1").Value = 1
Next Ws
End Sub​

J'ai essayé de combiner les deux et là... ben ça ne va plus. Je sèche. Je ne comprends pas ce que j'ai pu oublier... Une idée?

Sub mixdes2()
Dim Ws As Worksheet

For Each Ws In ThisWorkbook.Worksheets(Array("actif (1)", "actif (2)"))
Dim c As Range
colf = Sheet1.Range("C1")

For Each c In Sheet6.UsedRange
If c <> "" Then
Set trouve = Sheet2.Cells.Find(c, lookat:=xlWhole)

If Not trouve Is Nothing Then
c = Sheet2.Cells(trouve.Row, colf + 1)
End If
End If
Next c
Next Ws
End Sub​

Avec ce code, ça ne fonctionne que sur la Sheet6 et elle ne passe pas à la Sheet7. Alors que ça fonctionnait très bien dans l'exemple précédent. Est-ce possible qu'une succession de Next ne plaise pas au langage VBA? J'avais pourtant lu que plusieurs Next imbriqués était envisageable...

Je joins mon fichier de travail. Le code est dans ThisWorkbook.

Merci d'avance,

 

[CPk]

Re : Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

Bonjour sdevers, le forum.

Désolé...En se moment je suis un petit peu pris par le temps.
Pour vous éclairer...Et vous allez en rire : la réponse est dans la question...

Avec ce code, ça ne fonctionne que sur la Sheet6 et elle ne passe pas à la Sheet7

Et quand on regarde votre code...

For Each c In Sheet6.UsedRange
If c <> "" Then

Vous ne faites références qu'à la Sheets6 comme plage de recherche de mot à traduire. Or pour pouvoir traduire "toutes les feuilles", au lieu de n'utiliser que Sheets6 (qui est un objet feuille) on va utiliser un autre objet , "Ws" qui lui est un objet feuille et qui change automatiquement à chaque début de boucle.

Donc vous devez plutôt écrire for each c in Ws.usedrange au lieu de For Each c In Sheet6.UsedRange
(Je n'ai pas testé par manque de temps mais ça me semble fonctionnel).

Avez vous compris l'astuce ?

 

[sdevers]

Re : Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

Et vous allez en rire : la réponse est dans la question...


Et quand on regarde votre code...

J'ai fait tellement d'essais que je ne me rends même plus compte de trucs aussi bêtes... pfff je suis désolé de vous avoir ennuyé pour si peu. Vais aller me reposer et tester ça demain

Encore merci

 

[XL49]

Re : Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

Bonsoir,
voici une proposition sans VBA en utilisant les listes.
En fait, j'ai recopié l'onglet actif dans une nouvelle feuille actif(traduit).
Ca m'a donné la mise en forme.
Puis dans chaque cellule, j'ai mis une formule qui va chercher la traduction (il faut faire recopier formule).
J'utilise beaucoup plus souvent index+equiv, mais là je pense que recherchev est bien adapté.
J'ai aussi nommé les emplacements des données de base pour être plus clair et maintenable.

J'ai rajouté une petite astuce pour le titre qui est dans un cadre en rajoutant une cellule hors de la zone à imprimer pour sa traduction.

VBA reste plus souple à l'utilisation que la recopie de formule (même si ça se fait bien et vite).
Mais ça marche et tu peux ajouter des traductions car la zone "BaseTraductions" est dans un tableau ("TbTraduction")

cordialement

 

[CPk]

Re : Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

Bonjour XL49,sdevers

J'ai fait tellement d'essais que je ne me rends même plus compte de trucs aussi bêtes...
Encore merci

Je sais ce que c'est car je me prends la tête pareille même quand je fais des petits codes de rien du tout. J'en oublie très vite l'essentiel et je pars très loin...voire trop loin même. C'est une maladie contagieuse ne vous en faites pas.

XL49, une version sans vba n'est pas vaine car cela pourra faire un bon backup fonctionnel en cas de problème de codage..

 

[sdevers]

Re : Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

Bonsoir,
voici une proposition sans VBA en utilisant les listes.
En fait, j'ai recopié l'onglet actif dans une nouvelle feuille actif(traduit).
Ca m'a donné la mise en forme.
Puis dans chaque cellule, j'ai mis une formule qui va chercher la traduction (il faut faire recopier formule).
J'utilise beaucoup plus souvent index+equiv, mais là je pense que recherchev est bien adapté.
J'ai aussi nommé les emplacements des données de base pour être plus clair et maintenable.

J'ai rajouté une petite astuce pour le titre qui est dans un cadre en rajoutant une cellule hors de la zone à imprimer pour sa traduction.

VBA reste plus souple à l'utilisation que la recopie de formule (même si ça se fait bien et vite).
Mais ça marche et tu peux ajouter des traductions car la zone "BaseTraductions" est dans un tableau ("TbTraduction")

cordialement

Merci XL49. Bonne idée effectivement même si je vais garder la solution du VBA. Je préfère avoir du texte dans les pages destinées aux utilisateurs plutôt que les formules. Je sens bien que certains utilisateurs vont personnaliser les titres également (en soi ça ne me gêne pas, tant qu'ils ne se plaignent pas ensuite qu'ils ne sont plus traduits automatiquement). S'ils voient une formule INDEX, MATCH ou autre, ils vont rien n'y comprendre...
Je pourrais éventuellement cumuler votre solution et un code VBA qui "fige" le résultat des formules par la suite (Copy-Paste Value) mais alors je préfère conserver l'entièreté en VBA.
Merci pour la réflexion en tous cas.
Bonne journée,

 

[CPk]

Re : Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

sdevers, je vous invite à avoir un fichier sans vba en solution de secours, le jour j il sera plus qu'utile !

 

[sdevers]

Re : Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

Bonjour,

Excusez-moi mais j'ai un nouveau problème avec ce fil de discussion.

J'avais enfin un code qui fonctionnait dans mon fichier de test (j'utilise un fichier de test pour chaque macro que je souhaite implanter et ne modifie mon véritable fichier de travail qu'une fois la macro validée).

Sub traduction()
Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Sheets
If Not Ws.Name = "data" And Not Ws.Name = "traductions" And Not Ws.Name = "background" Then

Dim c As Range

colf = Sheet03.Range("D1")

For Each c In Ws.UsedRange
If c <> "" Then
Set trouve = Sheet02.Cells.Find(c, lookat:=xlWhole)
If Not trouve Is Nothing Then
c = Sheet02.Cells(trouve.Row, colf + 1)
End If
End If
Next c
End If
Next Ws
End Sub

Je veux maintenant l'intégrer dans mon véritable fichier de travail et là, horreur, elle ne fonctionne plus. Je reçois une erreur Type mismatch. Comprends pas. Le même code fonctionne dans un autre fichier (j'ai fait un copier-coller en renommant le numéro des feuilles (Sheet1 devient Sheet03).

J'ai essayé de déboguer mais la commande ne s'arrête sur aucune ligne. J'ai essayé de mettre toutes mes variables en Variant, ça ne change rien.

Pour déjà répondre à CPk, j'ai bien acheté un livre sur le VBA... l'ai oublié au bureau. Et je ne parviens pas à trouver ma réponse sur les différents forums.

Est-ce que possible que ce soit ma ligne If c <> "" Then qui bloquerait? On a défini c As Range. Ce que je comprends pas c'est pourquoi il bloque dans ce fichier-ci alors que la même macro ne bloque pas ailleurs...

Merci d'avance les gens,

 

[CPk]

Re : Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

Bonjour, joignez le fichier défaillant

 

[sdevers]

Re : Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

Bonjour, joignez le fichier défaillant

Voici le fichier en annexe. La macro se nomme traduction et est tout en bas de ThisWorkbook.

 

[CPk]

Re : Rechercher-Remplacer dans plusieurs feuilles et sur plusieurs termes

Bonjour,
Je ne vois pas ce qui cloche, c'est bien une erreur à la lecture d'une cellule mais je ne sais laquelle. Du coup, pour contourner il faut ajouter une phrase (celle qui est en bleue) juste après For each..

For Each c In Ws.UsedRange
On Error Resume Next