Fonction Replace fonctionne seulement en dehors des formules

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

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

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

Tu peux mettre le classeur?

**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:\....)

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

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 …

**Nagel Tha** · 20/09/2017, 15h08

Envoyé par Marc-L

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

J'ai essayé les virgules. Cela ne fonctionne pas plus. Ta remarque me fait me demander pourquoi j'ai pu effectuer des remplacements contenant des point-virgules avant...

**Marc-L** · 20/09/2017, 15h12

Juste comparer la formule locale - française par exemple - (FormulaLocal)
avec la formule native (toujours en anglais) en interne (Formula) …

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

Envoyé par Marc-L

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

Sauf que cela, c'est pour la fonction replace. Ici, depuis le début, on parle de méthode Replace dans laquelle on ne doit pas parler anglais, et si on cherche/remplace avec cette méthode (équivalent de ctrl+f ou ctrl+h), on ne doit pas remplacer les ; par des ,

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

Non comme pourtant signalé il s'agit bien de la méthode Replace correspondant à la fonctionnalité d'Excel
Rechercher / Remplacer et non pas à la fonction texte VBA du même nom !

Comme aussi indiqué après avoir testé (ok version 2003), tout texte / ponctuation à remplacer
dans une formule doit être converti en anglais, d'où l'intérêt de l'affichage via Formula …

Fonction Replace fonctionne seulement en dehors des formules [XL-2007]

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager