Fonction Replace fonctionne seulement en dehors des formules

Version imprimable

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

19/09/2017, 19h32
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...

Bonjour.

Remplace

Code:

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

PAR

Code:

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

20/09/2017, 09h38
Nagel Tha
Citation:
Envoyé par Docmarti

Bonjour.

Remplace

Code:

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

PAR

Code:

1 2 ToReplace(4) = "$T$10:$U$500,2" By(4) = "$M$2:$U$500,3"
Merci pour l'astuce je vais essayer :)
20/09/2017, 09h45
Nagel Tha

Citation:

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 ?

J'ai adapté ce code qui ne trouve rien dans mes 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
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

20/09/2017, 12h26
Pierre Fauconnier

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...
20/09/2017, 12h54
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.

Code:

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

20/09/2017, 13h04
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...
20/09/2017, 13h14
Pierre Fauconnier

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.
20/09/2017, 13h23
Nagel Tha

Citation:

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...
20/09/2017, 13h29
Nagel Tha

Citation:

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
20/09/2017, 13h44
Pierre Fauconnier

Tu peux mettre le classeur?

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:

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

20/09/2017, 13h52
Nagel Tha

2 pièce(s) jointe(s)

Citation:

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
20/09/2017, 14h06
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...

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.
20/09/2017, 14h20
Nagel Tha

Citation:

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 ?

Citation:

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
20/09/2017, 14h27
Marc-L

Pour la méthode Replace :

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 …

___________________________________________________________________________________________________________

:arrow: Merci de cliquer sur :plusser: en bas à droite de chaque message ayant aidé puis sur :resolu: pour clore cette discussion …
20/09/2017, 14h35
Nagel Tha

Citation:

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 …

___________________________________________________________________________________________________________

:arrow: Merci de cliquer sur :plusser: en bas à droite de chaque message ayant aidé puis sur :resolu: 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:\....)
20/09/2017, 14h38
Marc-L

Citation:

Envoyé par Marc-L

Ce qu'a du reste effectué DocMarti dans le post #22 (";" en ",") …

La ponctuation compte aussi car elle est aussi différente dans les formules en anglais …

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