Fonction Replace fonctionne seulement en dehors des formules

Version imprimable

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

20/09/2017, 14h40
Pierre Fauconnier

Citation:

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

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 …
20/09/2017, 14h59
Pierre Fauconnier

En fait, il y a eu tellement de tests que l'on ne sait plus ce qui a été essayé ni dans quelle version, et je me suis moi-même un peu emmêlé les pinceaux entre le CTRL+F (en français) et le range.replace (en anglais).

Ce que je viens de constater dans le fichier, c'est que les cellules qui contiennent les formules sont fusionnées (l'étaient-elles avant vendredi dernier?).

Le Find/Replace doit donc tenir compte de cette fusion de cellule (dans Excel comme dans VBA).
20/09/2017, 15h01
Marc-L

On peut éviter Find avec la méthode Replace adaptée aux formules en anglais …

J'ai évoqué Find après avoir constaté l'échec du remplacement en français sans avoir eu
le temps de tester un peu plus même si cela aurait dû faire tilt dans ma p'tite caboche !
20/09/2017, 15h08
Nagel Tha

Citation:

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

Citation:

Envoyé par Pierre Fauconnier

En fait, il y a eu tellement de tests que l'on ne sait plus ce qui a été essayé ni dans quelle version, et je me suis moi-même un peu emmêlé les pinceaux entre le CTRL+F (en français) et le range.replace (en anglais).

Ce que je viens de constater dans le fichier, c'est que les cellules qui contiennent les formules sont fusionnées (l'étaient-elles avant vendredi dernier?).

Le Find/Replace doit donc tenir compte de cette fusion de cellule (dans Excel comme dans VBA).

Oui. Ça c'est une constante : ce n'est pas moi qui ai édité ces fiches. Beaucoup de cases sont fusionnées mais pas toutes

Cependant la recherche se fait par ligne donc elle n'est pas sensible à la fusion à priori non ?
20/09/2017, 15h12
Marc-L

Juste comparer la formule locale - française par exemple - (FormulaLocal)
avec la formule native (toujours en anglais) en interne (Formula) …
20/09/2017, 15h19
Pierre Fauconnier

@Marc: Ca aurait dû tilter dans ma tête aussi... ;)

Cela étant, nous ne sommes pas plus avancés puisque malgré ces remplacements, ça ne fonctionne pas alors qu'il semblait que ça fonctionnait avant sans les remplacements (avec les ; et les cellules fusionnées).

En tout cas, cela me conforte dans l'idée qu'il serait de loin préférable de ramener les données dans le fichier local puis de travailler avec la table locale. De plus, en remplaçant les RECHERCHEV par des INDEX/EQUIV, on ne devrait pas modifier l'index de colonne comme on le fait ici. Je parie que sur le temps que tous ces essais, infructueux qui plus est, ont été effectués, les modifications pour importer les données et travailler en local seraient effectuées.

Je le dis et je le redis: travailler avec des formules qui lient des classeurs entre eux, c'est aller au casse-pipe à coup sûr.
20/09/2017, 15h26
Pierre Fauconnier

Au passage, je ne comprends pas bien pourquoi il faut répéter ces remplacements. Les plages sources vers lesquelles les RECHERCHEV pointent migrent toutes seules de U2:U500 vers T10:T500 qu'il faut se casser le c*** à les modifier régulièrement pour les faire repointer vers U?

Il n'y aurait pas un (sérieux) problème de conception à la base?

Bonsoir,

Comme j'aime bien explorer, même si ce n'est pas conventionnel (Senseï Marc qu'en penses tu ?) et que je n'ai pas l'habitude de faire du remplacer dans des formules
(car lorsque la conception est bien pensée, nul besoin de faire des changements), je suis parti vers un autre horizon pour de l'exploration.

Ceci dit Nagel Tha, ton code, que j'ai survolé à l'air bien compliqué pour faire ces remplacements dans des formules.
Surtout que les noms des classeurs sont déjà connus; il suffirait de vérifier l'existence de ceux-ci dans le chemin spécifier afin de faire les modifications …
PS : quasi 100 lignes de codes => je n'ai pas regardé dans le détails

Pour le fun et l'exploration j'ai fait un petit test avec une variable tableau (A1 =1 pour le début, ce n'est pas l'indication des lignes de 1 à 10 mais bien des chiffres) :

1 A K =RECHERCHEV(A1;$A$1:$B$10;2;0)

2 B L =RECHERCHEV(A2;$A$1:$B$10;2;0)

3 C M =RECHERCHEV(A3;$A$1:$B$10;2;0)

4 D N =RECHERCHEV(A4;$A$1:$B$10;2;0)

5 E O =RECHERCHEV(A5;$A$1:$B$10;2;0)

6 F P =RECHERCHEV(A6;$A$1:$B$10;2;0)

7 G Q =RECHERCHEV(A7;$A$1:$B$10;2;0)

8 H R =RECHERCHEV(A8;$A$1:$B$10;2;0)

9 I S =RECHERCHEV(A9;$A$1:$B$10;2;0)

10 J T =RECHERCHEV(A10;$A$1:$B$10;2;0)
Code:

1 2 3 4 5 6 7 8 9 10 11 Sub Test()Dim VA, i&, j& VA = Feuil1.UsedRange.FormulaLocal For i = LBound(VA) To UBound(VA) For j = LBound(VA, 2) To UBound(VA, 2) If InStr(VA(i, j), "=") = 1 Then VA(i, j) = Replace(VA(i, j), "$A$1:$B$10;2;0", "$A$1:$C$10;3;0") End If Next Next Feuil1.UsedRange.FormulaLocal = VA End Sub
PS : J'ai tenté avec un For Each … mais je n'ai pas réussi
Tout ce qui est texte ou chiffre le reste, les formules se modifient bien

Une façon possible pour du remplacer (c'est juste à titre d'exemple (bâclé) pour montrer le principe - je ne dis pas que c'est la meilleure soution) :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 Sub test2()Dim Formule$, Trouve, Remplace, T, i As Byte ' , k as Byte Formule = "PourModifierMaFormule!Outillage'!$D$10:$T$90;17;" Trouve = Array("folder", "Outillage'!$M$10:$U$500;3;", "Outillage'!$T$10:$U$500;3;", "Outillage'!$M$10:$U$500;2;", "Outillage'!$M$10:$U$500;3;", "Outillage'!$M$2:$U$500;2;", "Outillage'!$D$10:$T$90;17;") Remplace = Array("M:\", "Outillage'!$M$2:$U$500;3;", "Outillage'!$M$2:$U$500;3;", "Outillage'!$M$2:$U$500;3;", "Outillage'!$M$2:$U$500;3;", "Outillage'!$M$2:$U$500;3;", "Outillage'!$B$1:$I$65536;8;") i = 0 ' on supprime si on utilise For k For Each T In Trouve ' OU => For k = 0 to Ubound(Trouve) If InStr(Formule, T) > 0 Then ' If InStr(Formule, Trouve(k)) Then Formule = Replace(Formule, Trouve(i), Remplace(i)) ' Formule = Replace(Formule, Trouve(k), Remplace(k)) End If i = i + 1 ' on supprime si on utilise For k Next Debug.Print Formule End Sub
PS : j'ai fait ma 1ère contrib, n'hésitez pas à la voir et me faire un retour ;) ICI

21/09/2017, 06h02
Nagel Tha

Arigato gozaïmasu Ryu. Je vais explorer ta proposition dans la journée :)

Je vous remercie tous pour votre patience et pour vos suggestions.

C'est vrai que mon code est assez compliqué car d'un côté j'essaye de penser à tout et de l'autre côté je ne connaissais pas VBA avant de tomber sur ce problème. De plus dans le domaine de la programmation je suis assez léger car ma spécialité est le Génie Physique et non l'informatique donc je n'ai que quelques bases (langage C, CVI et LabView ainsi que html, css et javascript en autodidacte comme le VBA). Il s'agit donc de mon plus gros code. Aussi je vous remercie encore de votre patience.

J'espère notifier d'ici peu mon problème comme résolu. Je ne manquerai pas de vous faire part de la solution le cas échéant
21/09/2017, 10h30
Marc-L

Citation:

Envoyé par RyuAutodidacte

Comme j'aime bien explorer, même si ce n'est pas conventionnel (Senseï Marc qu'en penses tu ?) et que je n'ai pas l'habitude de faire du remplacer dans des formules

Salut Ryu !

Senseï est un peu trop à mon niveau, juste un explorateur aimant montrer des chemins de traverse à d'autres explorateurs !

Quand j'ai eu besoin de remplacer au sein de formules (cas non prévu ou le concepteur n'ayant pas maitrisé son sujet)
je l'ai toujours effectué directement au sein de la feuille de calculs sans code via Rechercher / Remplacer car c'est ponctuel;
je n'ai jamais eu un tel cas de 300 classeurs à corriger !

Si la célérité est recherchée alors souvent mieux vaut éviter une boucle et privilégier les fonctionnalités d'Excel, même en VBA …

S'assurer que ToReplace(y)et By(y) correspondent a la propriete Formula et non FormulaLocal. Donc pas de ; comme separateur dans la formule.

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
Sub MultipleSearch()
    Application.FindFormat.Clear
    Dim SearchInRange As Range
 
    For x = 1 To Worksheets.Count
 
Set SearchInRange = destinationWorkbook.Worksheets(x).Range("A1:Z300")
 
'Pour modifier seulement les cellules contenant une formule
Set SearchInRange = SearchInRange.SpecialCells(xlCellTypeFormulas)
 
Dim test1 As String
Dim cell As Range
For y = 1 To 8
test1 = ""
 
Set cell = SearchInRange.Find(ToReplace(y), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
 
If cell Is Nothing Then
 MsgBox y & " Non trouve avec FIND" & vbLf & ToReplace(y)
End If
 
SearchInRange.Replace what:=ToReplace(y), replacement:=By(y), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
 
Next y
Next x
End Sub

21/09/2017, 16h36
Nagel Tha

Mon problème est résolu. Merci à tous !

Je suis revenu à la version de départ non compartimentée que mon ami informaticien a testée et j'ai gardé le minimum de code. La méthode .Replace est dans le Sub principal (le fait qu'elle ne le soit plus dans la version compartimentée posait peut-être problème) et j'ai remplacé les , par des ; dans mes chaînes. Cela fonctionne à la perfection. Merci :)

Salut,

Je n'ai pas pu faire de synthèse rapidement car j'ai eu un emploi du temps assez chargé mais je n'ai pas oublié car il me tenait à coeur que cette discussion puisse servir à d'autres. Voici mes fonctions :

Fonction de remplacement des chaînes dans tous les classeurs d'un dossier avec boucle sur chaque feuille :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Function ReplaceStrings()
    Application.ReplaceFormat.Clear 'makes sure there will be no format parameter in the replace method
    'the x for loop is for repeating .Replace in all the worksheets
    'the y for loop is for replacing all the strings in the table
    x = 1
    y = 1
    For x = 1 To Worksheets.Count
        For y = 1 To k
            Worksheets(x).Cells.Replace What:=ToReplace(y), Replacement:=By(y), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
        Next y
    Next x
    Application.ReplaceFormat.Clear
End Function

Documentation méthode Range.Replace (msdn)

Fonction qui récupère les chaînes à remplacement et de substitution dans une plage définie (ici "A11:B60") :

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
 
Function DefineToReplaceBy()
 
Dim Table As Variant: Table = ThisWorkbook.Worksheets(1).Range("A11:B60").Value 'table containing the strings
Dim LastReplacement As Integer: LastReplacement = ThisWorkbook.Worksheets(1).Range("A11:B60").SpecialCells(xlCellTypeLastCell).Row - 11 'maximum number of replacements
 
ThisWorkbook.Worksheets(1).Range("A11:B60").NumberFormat = "@"
'first while loop counts till the last string is reached
    k = 1
    While VarType(Table(k, 1)) <> vbEmpty And VarType(Table(k, 1)) <> vbNull And k < ThisWorkbook.Worksheets(1).Range("A11:B60").SpecialCells(xlCellTypeLastCell).Row
        k = k + 1
    Wend
 
    LastReplacement = k - 1 'the loops will stop at the last string
 
'second while loop sets each ToReplace(k) to Table(k,1) from k = 1 to k = LastReplacement (from first string to last string to replace)
    k = 1
    While VarType(Table(k, 1)) <> vbEmpty And VarType(Table(k, 1)) <> vbNull And k <= LastReplacement
        ToReplace(k) = Table(k, 1)
        k = k + 1
    Wend
 
'third and last while loop do the same for By(k)
    k = 1
    While VarType(Table(k, 2)) <> vbNull And k <= LastReplacement
        If VarType(Table(k, 2)) = vbEmpty Then Table(k, 2) = "" 'any empty cell will be converted into an empty string
        By(k) = Table(k, 2)
        k = k + 1
    Wend
End Function

Attention, si vous faîtes du hardcoding il faudra faire attention à remplacer les ; par des , si vous cherchez une chaîne dans une formule (dont les arguments sont séparés par une virgule dans le système international/anglo-saxon). Ne pas faire cette adaptation conduit à un fonctionnement imprévisible.

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

1	A	K	=RECHERCHEV(A1;$A$1:$B$10;2;0)
2	B	L	=RECHERCHEV(A2;$A$1:$B$10;2;0)
3	C	M	=RECHERCHEV(A3;$A$1:$B$10;2;0)
4	D	N	=RECHERCHEV(A4;$A$1:$B$10;2;0)
5	E	O	=RECHERCHEV(A5;$A$1:$B$10;2;0)
6	F	P	=RECHERCHEV(A6;$A$1:$B$10;2;0)
7	G	Q	=RECHERCHEV(A7;$A$1:$B$10;2;0)
8	H	R	=RECHERCHEV(A8;$A$1:$B$10;2;0)
9	I	S	=RECHERCHEV(A9;$A$1:$B$10;2;0)
10	J	T	=RECHERCHEV(A10;$A$1:$B$10;2;0)