Fonction Replace fonctionne seulement en dehors des formules

**Pierre Fauconnier** · 19/09/2017, 19h32

Perso, je reste persuadé qu'il est préférable d'importer les données puis de travailler en local.

Mais perso, je reste aussi persuadé qu'il y a une coquille dans le texte cherché, car je ne vois a priori pas pourquoi la méthode .replace ne fonctionnerait pas (surtout si elle a fonctionné avant).

La chaine M:\Service QUALITE\Gestion de l''outillage (calibrated tools)\Calibrated tools\[Liste des outillages calibrés (QM-F61) rev 2.xlsm]Outillage'!$T$10:$U$500 peut receler plusieurs soucis et on a déjà fait plus simple comme chemin d'accès...

Vérifie bien que la chaîne que tu utilises correspond bien à ton texte à remplacer... Une idée serait de saisir ce chemin dans une cellule et d'appeler la cellule dans ton code. D'abord, tu éviterais le hard coding, puis tu pourrais t'assurer que la chaine utilisée dans la cellule correpond à ce qui est cherché.

Comme test, tu pourrais reprendre l'idée de Marc et utiliser ta chaine dans un replace pour voir si ça matche ou pas...

**Docmarti** · 20/09/2017, 00h04

Bonjour.

Remplace

Code :

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

1
2
ToReplace(4) = "$T$10:$U$500;2" 
By(4) = "$M$2:$U$500;3"

PAR

Code :

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

1
2
ToReplace(4) = "$T$10:$U$500,2" 
By(4) = "$M$2:$U$500,3"

**Nagel Tha** · 20/09/2017, 09h38

Envoyé par Docmarti

Bonjour.

Remplace

Code :

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

1
2
ToReplace(4) = "$T$10:$U$500;2" 
By(4) = "$M$2:$U$500;3"

PAR

Code :

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

1
2
ToReplace(4) = "$T$10:$U$500,2" 
By(4) = "$M$2:$U$500,3"

Merci pour l'astuce je vais essayer

**Nagel Tha** · 20/09/2017, 09h45

Envoyé par Pierre Fauconnier

Perso, je reste persuadé qu'il est préférable d'importer les données puis de travailler en local.

Mais perso, je reste aussi persuadé qu'il y a une coquille dans le texte cherché, car je ne vois a priori pas pourquoi la méthode .replace ne fonctionnerait pas (surtout si elle a fonctionné avant).

La chaine M:\Service QUALITE\Gestion de l''outillage (calibrated tools)\Calibrated tools\[Liste des outillages calibrés (QM-F61) rev 2.xlsm]Outillage'!$T$10:$U$500 peut receler plusieurs soucis et on a déjà fait plus simple comme chemin d'accès...

Vérifie bien que la chaîne que tu utilises correspond bien à ton texte à remplacer... Une idée serait de saisir ce chemin dans une cellule et d'appeler la cellule dans ton code. D'abord, tu éviterais le hard coding, puis tu pourrais t'assurer que la chaine utilisée dans la cellule correpond à ce qui est cherché.

Comme test, tu pourrais reprendre l'idée de Marc et utiliser ta chaine dans un replace pour voir si ça matche ou pas...

Pour l'importation de données il faudra refondre toute la gestion des fichiers. D'une part je n'aurai pas le temps et d'autre part le service INFO ne va pas aimer un tel remaniement xD

Sinon tu as raison et j'y pensais déjà avant pour rendre le code ergonomique : remplacer la définition old school des variables par la définition par une plage.

Je vais tenter ce test.

Sinon je me suis rendu compte qu'une manipulation à la noix fonctionnait : dézinguer le lien par une modif au hasard qui de toute façon ne s'applique pas (le chemin de ma macro s'applique) puis je remplace morceau par morceau. Une boîte de dialogue "mettre à jour les valeurs" apparaît dans le fichier. Je pense que c'est elle qui est en cause. J'ai DisplayAlerts = False dans mon code donc c'est bizarre que cela m'empêche de faire le remplacement...une idée ?

**Nagel Tha** · 20/09/2017, 12h10

J'ai adapté ce code qui ne trouve rien dans mes 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
31
32
33
34
 
Sub MultipleSearch()
    Application.FindFormat.Clear
    ' Get name to search
    Dim ToReplace As String: ToReplace = "$T$10:$U$500;2;"
 
    ' Get search range
    Dim rgSearch As Range
    Dim x As Integer
    x = 1
    For x = 1 To Worksheets.Count
    Set rgSearch = ActiveWorkbook.Worksheets(x).Range("A1:Z300")
 
    Dim cell As Range
    Set cell = rgSearch.Find(ToReplace, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
 
    ' If not found then exit
    If cell Is Nothing Then
        Debug.Print "Not found"
 
    Else
 
    ' Store first cell address
    Dim firstCellAddress As String
    firstCellAddress = cell.Address
 
    Do
        Debug.Print "Found: " & cell.Address
        Set cell = rgSearch.FindNext(cell)
        cell.Value = Replace(cell.Value, "$T$10:$U$500;2;", "$M$2:$U$500;3;")
    Loop While firstCellAddress <> cell.Address
End If
Next x
End Sub

**Pierre Fauconnier** · 20/09/2017, 12h26

et en essayant selection.Value = Replace(expression:=cell.Value, Find:="$T$10:$U$500;2;", replace:="$M$2:$U$500;3;", compare:=vbTextCompare) sur une cellule censée devoir être remplacée?

Si ça ne fonctionne toujours pas, c'est que la valeur cherchée n'est pas présente...

**unparia** · 20/09/2017, 12h54

Ton tout premier message parle de remplacements de sous-chaines dans des formules.
Et c'est sur cette base que je t'ai répondu (message n° 2).
Je ne vois nulle part ce que tu as fait de cette réponse.

**Nagel Tha** · 20/09/2017, 13h01

Code :

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

1
2
3
4
5
6
 
Sub test()
Workbooks("FS000116 FWDMOUNT.xls").Worksheets(16).Range("K8").Activate 'erreur 1004 et erreur 438 sans la méthode
Workbooks("FS000116 FWDMOUNT.xls").Worksheets(16).Range("K8").Select
Selection.Value = Replace(expression:=cell.Value, Find:="$T$10:$U$500;2;", Replace:="$M$2:$U$500;3;", compare:=vbTextCompare)
End Sub

Il ne prend même pas cet instruction et avec la doc je ne vois pas pourquoi

**Pierre Fauconnier** · 20/09/2017, 13h04

Jacques,

La méthode Replace d'un objet Range fonctionne sur les formules sans problèmes (elle ne fonctionne d'ailleurs que sur les formules, le LookIn étant implicitement xlFindLookIn.XlFormulas). Il n'est donc a priori pas nécessaire de passer par une boucle pour modifier une partie de formule qui est identique sur une plage de cellules.

Il faudrait que l'auteur de la discussion teste la fonction Replace, sinon, on va se perdre en conjectures sur 250 messages. Je rappelle que le demandeur lui-même a précisé que son code fonctionnait jusqu'à vendredi dernier. Il serait intéressant qu'il investigue pour voir ce qui a changé vendredi. Et je reste persuadé que pour ma part, c'est quelque chose dans le chemin recherché qui a bougé.

Mais comme on n'a de réponse de sa part ni sur le test avec la fonction, ni sur ces éventuelles investigations, nous y allons tous de notre petite idée sans que cela puisse déboucher sur une solution, sauf hasard...

Ici, nous en sommes à 29 messages, sans avoir eu à aucun moment la certitude que le texte cherché était bien présent. Or, sans avoir entendu parler de bug tant sur la méthode que sur la fonction, si l'une et l'autre ne remplacent pas, ben c'est qu'elles n'ont pas trouvé à remplacer, me semble-t-il...

**Pierre Fauconnier** · 20/09/2017, 13h14

Désolé. Au temps pour moi.

Avec la fonction Replace, tu dois évidemment utiliser expression:=cell.FormulaLocal puisque tu modifies la formule (en local pour ne pas avoir à modifier les ; en ,). La méthode Replace ne nécessite pas ces adaptations.

**Nagel Tha** · 20/09/2017, 13h23

Envoyé par unparia

Ton tout premier message parle de remplacements de sous-chaines dans des formules.
Et c'est sur cette base que je t'ai répondu (message n° 2).
Je ne vois nulle part ce que tu as fait de cette réponse.

Eh bien le lien complet est une sous-chaîne, la chaîne complète étant une formule =RECHERCHEV(...;'LIEN';...).

Avec la méthode .Replace, je cherche à modifier le lien et uniquement le lien. J'ai utilisé .SpecialCells(xlCellTypeFormulas) sans constater de différence. Un morceau du code que j'ai posté le contient d'ailleurs. Tu ne m'as pas donné d'autres indications...

**Nagel Tha** · 20/09/2017, 13h29

Envoyé par Pierre Fauconnier

Jacques,

La méthode Replace d'un objet Range fonctionne sur les formules sans problèmes (elle ne fonctionne d'ailleurs que sur les formules, le LookIn étant implicitement xlFindLookIn.XlFormulas). Il n'est donc a priori pas nécessaire de passer par une boucle pour modifier une partie de formule qui est identique sur une plage de cellules.

Il faudrait que l'auteur de la discussion teste la fonction Replace, sinon, on va se perdre en conjectures sur 250 messages. Je rappelle que le demandeur lui-même a précisé que son code fonctionnait jusqu'à vendredi dernier. Il serait intéressant qu'il investigue pour voir ce qui a changé vendredi. Et je reste persuadé que pour ma part, c'est quelque chose dans le chemin recherché qui a bougé.

Mais comme on n'a de réponse de sa part ni sur le test avec la fonction, ni sur ces éventuelles investigations, nous y allons tous de notre petite idée sans que cela puisse déboucher sur une solution, sauf hasard...

Ici, nous en sommes à 29 messages, sans avoir eu à aucun moment la certitude que le texte cherché était bien présent. Or, sans avoir entendu parler de bug tant sur la méthode que sur la fonction, si l'une et l'autre ne remplacent pas, ben c'est qu'elles n'ont pas trouvé à remplacer, me semble-t-il...

Le texte est bien présent : je le copie de la chaîne à rechercher (Ctrl+C) directement dans mon code (que j'ai adapté pour récupérer les infos depuis une plage).

De plus je fais les tests suivants : F8 et Debug.Print. Les deux indiquent que toutes les variables sont correctement transmises à Replace.

J'ai également testé 9 combinaisons possibles d'arguments de Replace pour voir l'effet de ces variables et il est nul.

Désolé de rester bloquer si longtemps mais mon problème ne me semble pas trivial sinon je n'aurais même pas eu besoin de poster ma question.

J'ai commencé à me documenter sur .Find et à faire des tests et pour l'instant en combinant .Find et la fonction replace je n'ai pas plus de succès

**Pierre Fauconnier** · 20/09/2017, 13h44

Tu peux mettre le classeur?

**Nagel Tha** · 20/09/2017, 13h48

Voilà où j'en suis : j'ai utilisé le pas à pas et le Debug.Print qui montrent que les valeurs sont bien transmises à la méthode find qui ne les trouvent pas...

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
Option Explicit
Dim folder As Variant 'Folder containing all the Workbooks
Dim Continue As Integer
Dim filename As String 'filename contains folder name and file extension concatenated into one string
Dim OpenF As Boolean
Dim destinationWorkbook As Workbook 'destinationWorkbook will change at each iteration of while
Dim x As Integer, y As Integer
'Dim ToReplace As String, By As String
Dim ToReplace(1 To 8) As String 'bad parts of links
Dim By(1 To 8) As String 'to replace by the parts contained in this string
    Public Sub Update_HYPERLINKS_InAllWorkbooksInFolder()
 
    Application.DisplayAlerts = False 'Disables popup warnings
 
    Dim OpenAnother As Integer
    OpenAnother = 7 '7 is the integer value of vbNo (No button of MsgBox)
    Dim Tableau As Variant: Tableau = ThisWorkbook.Worksheets(1).Range("A2:B15").Value
    Dim k As Integer, l As Integer
    k = 1
    For k = 1 To 8
    ToReplace(k) = Tableau(k, 1)
    Debug.Print ToReplace(k)
    Next k
    l = 1
    For l = 1 To 8
    By(l) = Tableau(l, 2)
    Debug.Print By(l)
    Next l
 
    OpenF = False
    'Do
 
    OpenF = OpenFilesInFolder
 
    While OpenF = True And Len(filename) <> 0
 
        Set destinationWorkbook = Workbooks.Open(folder & filename)
        UnprotectFiles
        'ReplaceStrings
        MultipleSearch
        ProtectFiles
        destinationWorkbook.Close True
 
        filename = Dir()  ' Get next matching file
    Wend
 
    'OpenAnother = MsgBox("Do you want to update another folder?" & Chr(10) & Chr(10) & "Yes to Open a new folder - No to Quit the application", _
    'vbQuestion + vbYesNo, "Updating another folder")
 
    'Loop While OpenAnother = 6 '6 is the integer value of vbYes (Yes button of MsgBox): here the code will be repeated if OpenAnother = 6 (yes)
 
End Sub
Sub MultipleSearch()
    Application.FindFormat.Clear
    Dim SearchInRange As Range
    x = 1
    For x = 1 To Worksheets.Count
    Set SearchInRange = destinationWorkbook.Worksheets(x).Range("A1:Z300")
 
    Dim cell As Range
    y = 1
    For y = 1 To 8
    Debug.Print ToReplace(y)
    Set cell = SearchInRange.Find(ToReplace(y), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
 
    ' If not found then exit
    If cell Is Nothing Then
        Debug.Print "Not found"
 
    Else
 
    ' Store first cell address
    Dim firstCellAddress As String
    firstCellAddress = cell.Address
 
    ' Find all cells containing Elli
    Do
        Debug.Print "Found: " & cell.Address
        Set cell = SearchInRange.FindNext(cell)
        cell.FormulaLocal = Replace(expression:=cell.FormulaLocal, Find:="$T$10:$U$500;2;", Replace:="$M$2:$U$500;3;", compare:=vbTextCompare)
    Loop While firstCellAddress <> cell.Address
End If
Next y
Next x
End Sub

**Nagel Tha** · 20/09/2017, 13h52

Envoyé par Pierre Fauconnier

Tu peux mettre le classeur?

Les voici. Mon code a changé depuis le début car je l'ai compartimenté et j'ai défini une plage pour les valeurs à remplacer

**Pierre Fauconnier** · 20/09/2017, 14h06

Je n'ai pas lu tout le code...

Tu charges Tableau avec 14 lignes (2 à 15), puis tu charges ToReplace et By sur moins de lignes...

En pas à pas, tu passes sur la ligne cell.FormulaLocal = Replace(expression:=cell.FormulaLocal, Find:="$T$10:$U$500;2;", Replace:="$M$2:$U$500;3;", compare:=vbTextCompare)?

La déclaration de variables publiques ne facilitent évidemment pas le travail...

Je ne comprends pas pourquoi tu utilises SearchInRange.FindNext(cell) avant de remplacer, puisque tu es sur la bonne cellule avant le FindNext. La première cellule trouvée ne sera donc jamais modifiée.

**Nagel Tha** · 20/09/2017, 14h20

Envoyé par Pierre Fauconnier

Je n'ai pas lu tout le code...

Tu charges Tableau avec 14 lignes (2 à 15), puis tu charges ToReplace et By sur moins de lignes...

Je me disais que les gens voudraient sûrement renseigner d'autres valeurs. Pour se restreindre à une plage identique il faudrait que les cellules contenant du texte rayé soient exclues. C'est possible de faire ça ?

Envoyé par Pierre Fauconnier

En pas à pas, tu passes sur la ligne cell.FormulaLocal = Replace(expression:=cell.FormulaLocal, Find:="$T$10:$U$500;2;", Replace:="$M$2:$U$500;3;", compare:=vbTextCompare)?

La déclaration de variables publiques ne facilitent évidemment pas le travail...

Je ne comprends pas pourquoi tu utilises SearchInRange.FindNext(cell) avant de remplacer, puisque tu es sur la bonne cellule avant le FindNext. La première cellule trouvée ne sera donc jamais modifiée.

Je vais essayer sans le findnext.

Merci pour tes remarques

Après avoir mis FindNext en commentaire il ne trouve pas plus

**Marc-L** · 20/09/2017, 14h27

Bon ben encore une fois c'est vraiment tout bête : il suffit juste de ne pas oublier le B-A-BA du VBA ‼

Test effectué sur une version 2003 française : création d'un code via l'Enregistreur de macro
d'une opération Rechercher / Remplacer de SOMME par MAX par exemple, en manuel aucun souci …
Retour des formules en SOMME puis lancement de la macro enregistrée : échec, les formules ne passent pas en SOMME
et en fait c'est normal car comme c'est inscrit partout sur le Web en interne VBA et les formules restent en anglais
quelque soit la langue de la version locale d'Excel (française, espagnole, danoise, swalili, klingon, …).

Donc il suffit d'adapter le code avec la formule en anglais, dans cet exemple juste remplacer SOMME par SUM
et là le code fonctionne à merveille ! Ce qu'a du reste effectué DocMarti dans le post #22 (";" en ",") …

Astuce pour voir la formule en français (en fait peu importe la langue de la version locale d'Excel) traduite
en anglais pour la cellule A1 par exemple : coté VBE dans la fenêtre Exécution entrer ?[A1].formula et valider …

___________________________________________________________________________________________________________

Merci de cliquer sur

en bas à droite de chaque message ayant aidé puis sur

pour clore cette discussion …

**Nagel Tha** · 20/09/2017, 14h35

Envoyé par Marc-L

Bon ben encore une fois c'est vraiment tout bête : il suffit juste de ne pas oublier le B-A-BA du VBA ‼

Test effectué sur une version 2003 française : création d'un code via l'Enregistreur de macro
d'une opération Rechercher / Remplacer de SOMME par MAX par exemple, en manuel aucun souci …
Retour des formules en SOMME puis lancement de la macro enregistrée : échec, les formules ne passent pas en SOMME
et en fait c'est normal car comme c'est inscrit partout sur le Web en interne VBA et les formules restent en anglais
quelque soit la langue de la version locale d'Excel (française, espagnole, danoise, swalili, klingon, …).

Donc il suffit d'adapter le code avec la formule en anglais, dans cet exemple juste remplacer SOMME par SUM
et là le code fonctionne à merveille !

Astuce pour voir la formule en français (en fait peu importe la langue de la version locale d'Excel) traduite
en anglais pour la cellule A1 par exemple : coté VBE dans la fenêtre Exécution entrer ?[A1].formula et valider …

___________________________________________________________________________________________________________

Merci de cliquer sur

en bas à droite de chaque message ayant aidé puis sur

pour clore cette discussion …

C'est exactement comme cela que j'ai fait pour obtenir le code de rechercher/remplacer. Sauf que je ne touche pas à la fonction et les seules sous-chaînes que j'essaye de changer font partie du lien (M:\....)