Macro VBA remplacer une liste de mot par X

Ben OUI!!! je les veux ces trois lignes
et ensuite pour ABCbourse J'ai une surprise pour toi je la mettrais sur le post en question
je veux mes trois lignes !!!!!!!!!

En préambule, je précise dans des discussions pourtant récentes j'ai déjà posté de similaires démonstrations …

Avant d'attaquer un code, la question est y-a-t-il moyen via une fonctionnalité d'Excel ou une formule ?
Ici vu la pauvreté de la présentation initiale même avec l'information des espaces à la fin des mots du post #4
la réponse est oui ! Toutefois je précise mon protocole de tests : chaque liste commence en A1 dans chaque feuille.

Donc avant d'attaquer le code, il faut déjà tester une formule dans la première feuille de calculs.
Avec l'histoire des espaces, la fonction de feuille de calculs EQUIV s'impose, formule en C1 :

Code:

---

=SI(ESTNA(EQUIV(SUPPRESPACE(A1)&"*";Feuil2!$A$1:$A$2;0));"x";A1)

---

A recopier vers le bas jusqu'en C4 …

Sélectionner la cellule C1 puis côté VBE on regarde la formule en version native via la fenêtre Exécution :

Code:

---

?activecell.Formula

---

En validant cette commande, la formule en anglais apparaît : =IF(ISNA(MATCH(TRIM(A1)&"*",Feuil2!$A$1:$A$2,0)),"x",A1) …
Au passage, tout ceci est déjà dans un tutoriel de ce forum …

Maintenant le code va s'articuler autour de cette formule grâce à la puissante méthode Evaluate,
une première dédicace spéciale Patrick :

Code:

---

1 2 3

Sub Demo00() With Feuil1.Cells(1).CurrentRegion.Columns(1): .Value = .Parent.Evaluate("IF(ISNA(MATCH(TRIM(" & .Address & ")&""*""," & Feuil2.Cells(1).CurrentRegion.Columns(1).Address(External:=True) & ",0)),""x""," & .Address & ")"): End With End Sub

---

Là c'est sûr, un débutant fuit en courant et même pour un expérimenté côté lisibilité … :ptdr:

Code:

---

1 2 3 4 5

Sub Demo0() With Feuil1.Cells(1).CurrentRegion.Columns(1) .Value = .Parent.Evaluate("IF(ISNA(MATCH(TRIM(" & .Address & ")&""*""," & Feuil2.Cells(1).CurrentRegion.Columns(1).Address(External:=True) & ",0)),""x""," & .Address & ")") End With End Sub

---

Cela fait un peu moins peur mais ce n'est pas encore cela côté lisibilité, maintenance, …

Je préfère pour la lisibilité utiliser une constante pour la formule
- j'en profite pour rappeler de doubler les guillemets de la formule au sein de la chaîne -
dans laquelle les adresses fixes des plages de cellules seront remplacées par des #1 & #2
afin qu'elle évolue selon la dynamique des plages :

Code:

---

1 2 3 4 5 6

Sub Demo1a() Const F = "IF(ISNA(MATCH(TRIM(#1)&""*"",#2,0)),""x"",#1)" With Feuil1.Cells(1).CurrentRegion.Columns(1) .Value = .Parent.Evaluate(Replace$(Replace$(F, "#1", .Address), "#2", Feuil2.Cells(1).CurrentRegion.Columns(1).Address(External:=True))) End With End Sub

---

Côté lisibilité avec une ligne supplémentaire :

Code:

---

1 2 3 4 5 6 7

Sub Demo1b() Const F = "IF(ISNA(MATCH(TRIM(#1)&""*"",#2,0)),""x"",#1)" AD$ = Feuil2.Cells(1).CurrentRegion.Columns(1).Address(External:=True) With Feuil1.Cells(1).CurrentRegion.Columns(1) .Value = .Parent.Evaluate(Replace$(Replace$(F, "#1", .Address), "#2", AD)) End With End Sub

---

« C'est simple ! » comme il dit le p'tit gars de chez Renault !

Une variante en nettoyant les espaces dans la seconde feuille via la fonction SUPPRESPACE et
alors la fonction NB.SI est utilisable :

Code:

---

1 2 3 4 5 6 7 8 9 10

Sub Demo2a() Const F = "IF(COUNTIF(#2,TRIM(#1)),#1,""x"")" With Feuil2.Cells(1).CurrentRegion.Columns(1) .Value = Application.Trim(.Value) AD$ = .Address(External:=True) End With With Feuil1.Cells(1).CurrentRegion.Columns(1) .Value = .Parent.Evaluate(Replace$(Replace$(F, "#1", .Address), "#2", AD)) End With End Sub

---

Ou en nettoyant les deux feuilles :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

Sub Demo2b() Const F = "IF(COUNTIF(#2,#1),#1,""x"")" With Feuil2.Cells(1).CurrentRegion.Columns(1) .Value = Application.Trim(.Value) AD$ = .Address(External:=True) End With With Feuil1.Cells(1).CurrentRegion.Columns(1) .Value = Application.Trim(.Value) .Value = .Parent.Evaluate(Replace$(Replace$(F, "#1", .Address), "#2", AD)) End With End Sub

---

Pour conclure, penser Excel avant VBA permet aux débutants de s'en sortir plus facilement,
en tout cas pour ceux ayant une p'tite base côté Excel !

Voilà, voilà !

_________________________________________________________________________________________________________

Merci de cliquer sur :plusser: pour chaque message ayant aidé puis sur :resolu: pour clore cette discussion …

A1 patricktoulon :
A toujours te précipiter comme tu le fais, tu dérapes sans même t'en rendre compte.
Tu devrais lire/relire plus attentivement ce que j'ai écrit en mon 3) de mon message.
http://www.developpez.net/forums/d15...x/#post8686797
Sa lecture très attentive aurait dû te mettre la puce à l'oreille et éviter de commettre une erreur prévisible (que tu ne peux toutefois prévoir sans affiner l'analyse) , que je pointerai du doigt au retour du demandeur (qui a par ailleurs lui-même montré une autre carence d'analyse que je l'inviterai à corriger également).
Je ne tiens absolument pas à intervenir à nouveau sur ces tâtonnements successifs tant que le demandeur ne sera pas revenu. Il va déjà être assez "noyé" dans ce "tas" de bouts de code (pas l'idéal pour un débutant, ce genre de noyade)

Personnellement je me suis tenu à l'énoncé, à savoir un mot par cellule parfois avec un espace à la fin …

Bonjour Marc-L
Et tu es ce faisant très sage.
L'a moins été le demandeur, avec sa malheureuse formulation :

Citation:

---

et en plus je viens de voir que j'ai des espaces derrière les mots, y a t il moyen de dire que la cellule contient le mot et non qu'elle est égale ?

---

qui a eu pour résultat (on le voit bien et on le verra beaucoup mieux bientôt) de nombreux "égarements" trop hâtifs.
La solution finale VBA (une fois corrigée cette formulation) sera fort heureusement très simple et sans rien d'autre que CountIf.

re et moi les fomule c'est du walouh!!!

et merci pour la dédicacé :salo:

j'essai de décortiquer tout ca



Q 1° Const F = "IF(ISNA(MATCH(TRIM(#1)&""*"",#2,0)),""x"",#1)"

c'est quoi #1 et #2 je suppose que #1 c'est la cellule a modifier donc en l'occurrence chez moi "A3" en feuille 1 mais #2 pas compris moi le 2eme #1 je suppose que c'est le même au cas ou l'existence est positive
donc #2 connais pas

Q° 2 c'est quoi en fait alors "f" en terme de (typename )

Q° 3 comment fait tu pour cumuler les formule j'y arrive jamais même avec les outils la dessus il n'y a pas d'aide

Q° 4 evaluate lui il m'aime pas .ca marche jamais chez moi malgré toute tes démo passées et présentes ( erreur incompatible j'ai tout essayé ) c'est ca qui me rends fou

Q° 5 External:=True c'est qui celui la???

Q°6 si je comprends bien tu évalue F c'est donc un string

j'aimerais bien un jour que chez moi ca fonctionne ton evaluate

au passage chez moi marche pas !!!sur le gros pc W7 off 2007 mais sur le portable oui avec W10 off 2010 pourquoi?
j'ai changé juste la cells de départ chez moi c'est A3

Code:

---

1 2 3 4

With Feuil1.Cells(3, 1).CurrentRegion.Columns(1) .Value = .Parent.Evaluate(Replace$(Replace$(F, "#1", .Address), "#2", Feuil2.Cells(1).CurrentRegion.Columns(1).Address(External:=True))) End With End Sub

---

• R1 : comme expliqué entre Demo0 & Demo1a, #1 & #2 représentent les adresses des plages de cellules
(respectivement pour Feuil1 & Feuil2), voir le résultat de la chaîne à jour :

Code:

---

Debug.Print Replace$(Replace$(F, "#1", .Address), "#2", Feuil2.Cells(1).CurrentRegion.Columns(1).Address(External:=True))

---

La formule de calculs d'Excel pour une cellule de Feuil1 est appliquée en VBA à la colonne de Feuil1, voir R3 …

• R2 : Q6 ! Oui F est déclarée de facto en String par VBA, vérifiable dans la fenêtre des Variables locales durant l'exécution …

• R3 : la méthode Evaluate la comprend comme une formule matricielle …

• R4 : effectue déjà un test comme moi depuis A1 pour les deux feuilles sur un nouveau classeur …

• R5 : c'est un paramètre de la propriété Range.Address, voir l'aide VBA …
Avec ce paramètre c'est Feuil2!$A$1:$A$2 et sans c'est juste $A$1:$A$2 !
Comme la méthode Evaluate porte sur Feuil1, la plage de cellules de Feuil2 doit référencer cette Feuil2,
comme dans la formule de la feuille de calculs du départ …

• R6 : la plage commence aussi en A3 pour Feuil2 ? Sinon voir R4 …

re
OK
j'ai compris j'avais fait un debug déjà pour comprendre

mais evaluate marche toujours pas chez moi

erreur incompatible type

je précise que je travaille sur un fichier vierge donc aucunes interaction avec d'éventuelles autre macro ou variables

je viens d'aller refaire un tour chez boisgontier jacques et il donne des exemples simples et eux non plus ne fonctionnent pas chez moi toujours la même erreur

non c'est vraiment chez moi qu'il y a un truc que cloche

ca fait des mois!!!! que je viens et reviens sur ce truc

j'ai installé réinstaller office ,même formater complètement réinstaller intégralement rien n'y fait c'est pour ca que je m'énerve

bon ca marche pas mais tout de meme un espoir

exemple simple
celuicifonctionne pas (evaluate de mes deux!!!)

Code:

---

1 2 3 4 5

Sub essai() a = Evaluate("{1,2,3;4,5,6;7,8,9;10,11,12}") MsgBox UBound(a, 1) MsgBox UBound(a, 2) End Sub

---

'ou
celui ci fonctionne

Code:

---

1 2 3 4 5

Sub essai2() a = [{1,2,3;4,5,6;7,8,9;"aa","bb","cc"}] MsgBox a(3, 1) MsgBox a(4, 1) End Sub

---

Pas de souci de mon côté sous 2003 comme sous 2010.

J'attends qu'un portable rentre sous 2007 pour vérifier, peut-être en fin de semaine …

« Cela ne marche pas » : tu as un message d'erreur ?

re
Bonjour Marc

purée ca m'a tellement bouffer le mou jusqu'à 3 heure du Mat mais j'y suis arrivé

j'ai viré un bonne 15 aine de mise a jour office et l'une d'entre elle devait bloquer quelque chose maintenant j'ai mon tableau

et le pire c'est que se sont les première mises a jour qui foute le boxon puisque j'ai formaté et réinstallé il y a moins d'un mois après avoir rétrograder W10 /W7

et la rétrogradation ne c'était pas bien passé

bon content moi on va pouvoir avancer
;)

Citation:

---

J'attends qu'un portable rentre sous 2007 pour vérifier,

---

Fait (testé) . Fonctionne en Excel 2007

A patricktoulon : si la notation entre crochets, qui implique evaluate et affranchit des guillemets (intérieur implicitement considéré comme chaîne de caractères), c'est que sur ta machine, le mot réservé Evaluate a peut-être été accidentellement dévoyé un jour (dans un addin, ou ailleurs)
Essaye avec Application.Evaluate (passera peut-être avec cette précision)

re
Bonjour unparia
non je n'ai jamais utiliser un addin avec une fonction ou quoi que se soit d'autre avec "evaluate"

c'était bien les mises a jours qui me posait soucis j'avais même pas vu un sp3 pour office ( j'ai W updt) en auto même les facultatives j'ai modifier ce point et masquer ces mises a jour
car après les avoir enlevé il a pas fallu longtemps pour que l'on me les re propose
je les aient viré et même des macros qui fonctionnaient plus re fonctionnent

après diverses recherche il semblerait que ce sp3 ai causé beaucoup de petit soucis notamment avec des controls activX

i' me very happy

c'est bon maintenant j'ai bien les deux méthodes qui fonctionnent


je vais pouvoir me mettre aux formules de Marc qui piquent les yeux :oops:;)

Cela me rappelle récemment sur un ordinateur l'appel d'un ActiveX est en erreur en VBA
(sans modifier le code ayant pourtant fonctionné auparavant) mais sans souci en VBScript …

a la place de ta ligne evaluate
y aurait -il un moyen de le faire avec la 2 ° méthode avec les ("[]") a moins que evaluate de fasse pas que créer le tableau ???????

La notation entre crochets est l'écriture simplifiée de la méthode Evaluate, voir l'aide VBA.
Par exemple [{1,2,3}] est équivalent à Evaluate("{1,2,3}") …

Par contre quand il y a besoin d'une opération comme une concaténation par exemple, seule fonctionne Evaluate !
Exemple de la ligne de code n°10 du deuxième code de ce post.
(Voir aussi au passage le post n°20 utilisant une formule directement dans les cellules sans Evaluate …)

Le demandeur revient enfin, je n'avait pas reçu de mail de réponse donc je ne m'affolais pas.

Je viens donc de lire vos réponses : UN GRAND MERCI à vous !

Je vois ce que je peux faire avec toutes ses infos, je n'ai vraiment pas eu le temps de m'y plonger et reviens vers vous.

Encore Merci

Bonjour à tous

Merci à tous.

Comme je vous l’ai dit, je suis bleue de chez bleue.
J’ai fait une macro : pour supprimer des colonnes contenant des mots.
Seulement j’ai 10 feuilles avec des mots différents. (feuille 2)
Et des mots qui changent tous les mois (feuille 1)
C’est pourquoi je vous demande cette macro.

Supprimer des colonnes c’est facile,
Remplacer des mots par X le bout du monde pour moi, mes cours d'algo sont très très loin.

Bref, j’ai essayé :

* Macro de unparia mais ca ne fonctionne pas « Erreur de compilation : instruction incorrecte à l’intérieur d’une procéure »

Code:

---

1 2 3 4 5 6 7

Dim lader As Long, c As Range lader = Worksheets("Feuil2").Range("A" & Rows.Count).Row For Each c In Worksheets("Feuil1").Columns(1).SpecialCells(xlCellTypeConstants) If WorksheetFunction.CountIf(Worksheets("Feuil2").Range("A1:A" & lader), RTrim(c.Value) & "*") = 0 Then c.Value = "x" End If Next

---

* j’ai essayé : celle de de patricktoulon

Code:

---

1 2 3 4 5 6 7 8 9

Sub patrick2() Dim c As Range, dico As Object, t With Sheets(2): t = Join(Application.Transpose(.Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)).Value), "|"): End With Debug.Print t For Each c In Worksheets("Feuil1").Columns(3).SpecialCells(xlCellTypeConstants) If Not t Like "*" & c.Value & "*" And Not c Like "X" Then i = i + 1: Worksheets(1).Columns(3).Replace What:=c.Value, Replacement:="X" Next Cells(1, 4) = i & " replacement global" End Sub

---

Ca fonctionne mais mes colonnes ne sont pas placées ainsi et je ne sais pas modifier la macro
Feuille 1 mes mots sont sur la ligne 1 : en cellules G 1 à EN 1
Feuille 2 mes mots sont sur la colonne 2

* Marc-L : Tu m’as complètement pommée
J’ai pourtant essayé

Code:

---

=SI(ESTNA(EQUIV(SUPPRESPACE(A1)&"*";Feuil2!$A$1:$A$2;0));"x";A1)

---

Mais là non plus mes cellules ne sont pas aux endroits des formules.


MERCI
MERCI aussi à Eric4459? à jurassic pork ... et désolée si j'en oublie.

Comme préconisé dans les règles de ce forum (y a même une démo pour les moins doués !),
merci de baliser chaque code via l'icône # !


Sinon ce que l'on a proposé est au niveau de la présentation initiale :
commencer par tester avec une feuille correspondant donc à cette "présentation" …

Une fois que cela fonctionne avec la feuille telle que décrite dans la présentation initiale,
il est simple d'adapter formule ou code en fonction des données réelles.
Juste en lisant et en suivant le post #22 par exemple …

Bonsoir Marc-L

Désolée, mais je ne comprends rien qu'est ce que
"la présentation initiale" ?
"tester avec une feuille correspondant donc à cette présentation" ?
"le post #22" ?

Merci