[VBA] Remplacer par, avec tableau correspondance

[Anthonymctm]

Bonjour le forum,

Je vous sollicite parceque je n'ai pas trouvé de réponse sur le net 😕

J'aimerai automatiser une extraction et un traitement que je fais actuellement manuellement.

J'en suis à la partie remplacement.
Dans une feuille à part "famille", j'ai un tableau de correspondance des termes que je souhaite remplacer :

Modèle	Libellé
01	Matière première
02	Elément de mécano-soudure
03	Elément d'assemblage mécanique
04	Elément manufacturé / PSF
05	Sous-traitance (Prestation)
06	Outillage
07	Opération
08	Produit-fini
09	Article temporaire
10	Frais généraux
11	Pied de document

Et je cherche le moyen plus propre d'effectuer un remplacement en utilisant ce tableau.
Plutôt que faire 10 x

Selection.Replace What:="01", Replacement:="Matière première", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

voilà, merci 🙂

 

[sylvanu]

Bonjour,
Ca ressemble quand même pas mal à une boucle, comme par exemple :

Sub Remplace()
Dim i As Integer, Modèle As String, Libellé As String
For i = 1 To 11
    Modèle = Cells(i, 1)
    Libellé = Cells(i, 2)
    Selection.Replace What:=Modèle, Replacement:=Libellé, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Next i
End Sub

en supposant votre tableau en A1:B11

 

[Anthonymctm]

Bonjour,
Ca ressemble quand même pas mal à une boucle, comme par exemple :

Sub Remplace()
Dim i As Integer, Modèle As String, Libellé As String
For i = 1 To 11
    Modèle = Cells(i, 1)
    Libellé = Cells(i, 2)
    Selection.Replace What:=Modèle, Replacement:=Libellé, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Next i
End Sub

en supposant votre tableau en A1:B11

Bonjour Sylvanu,

Merci pour ton aide.
J'ai réussi en faisant

numcol = Application.IfError(Application.Match("Famille niv1", Rows(1), 0), 0)

    For j = 1 To 11
    Modèle = Sheets("famille").Cells(j + 1, 1)
    Libellé = Sheets("famille").Cells(j + 1, 2)
    Columns(numcol).Replace What:=Modèle, Replacement:=Libellé, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Next j

Parceuqe j'avais aussi besoin de selectionner une colonne precise.

J'aimerai utiliser le même principe de boucle pour convertir des colonnes en nombre l'une après l'autre (on peut pas en convertir plusieurs d'un coup).
J'ai tenté ça :

 For h = 7 To 12
    Columns(h).TextToColumns Destination:=Range(h), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
  Next h

initialement j'avais ça (sorti de l’enregistreur de macro) :

Columns("G:G").TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

Edit : je crois avoir réussi en supprimant simplement la destination

 

[sylvanu]

Range(h) ne marche pas ( Range(7) est une erreur de syntaxe)
Je l'ai remplacé par : Range(Cells(1, h), Cells(1, h)), Il n'y a plus d'erreur même s'il doit y avoir une écriture plus simple.

 Sub essai()
 For h = 7 To 12
        Columns(h).TextToColumns Destination:=Range(Cells(1, h), Cells(1, h)), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
  Next h
 End Sub

 

[Anthonymctm]

Range(h) ne marche pas ( Range(7) est une erreur de syntaxe)
Je l'ai remplacé par : Range(Cells(1, h), Cells(1, h)), Il n'y a plus d'erreur même s'il doit y avoir une écriture plus simple.

 Sub essai()
For h = 7 To 12
        Columns(h).TextToColumns Destination:=Range(Cells(1, h), Cells(1, h)), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
  Next h
End Sub

J'ai carrément enlevé le terme destination et ça fonctionne.
Tu pense que c'est source à emmerde ?

Après tout mon code est hyper long a fonctionner 😕

 

[sylvanu]

Je ne sais pas. Moi si ça marche avec les Cells, je laisserais. 🙂
C'est long ?
Qu'est ce qu'est censé faire cette macro ?

 

[chris]

Bonjour à tous

Faisable par PowerQuery intégré à Excel

Poste un exemple de fichier

 

[Anthonymctm]

Je t'ai joins le fichier, ya pas vraiment de données sensibles.

Après t’embête pas, si t'as pas trop le temps, le code fonctionne. C'est juste question d'apprentissage et d'optimisation c'est tout ^^

C'est pour faire un recap régulier des immobilisations en stock.

-J'extrais les données via un logiciel de GPAO et je fais un copier via l'applicaiton.
-Je test que le copier soit bien effectif pour éviter d'avoir des erreurs
-Je supprime les anciennes données et je colle en format texte pour bien avoir les code famille en "01" et nom "1"
-Je repositionne les colonnes dans un autre bien precis et je supprime les colonnes superflues
-L'extraction me met tous les nombre au dela de 1000 comme ça " XXXXX". Avec plusieurs espaces devant, donc je fais une conversion de plusieurs colonne.
-Ensuite les noms des familles sont en code 01,02,etc donc je fais un remplacer suivant le tableau.
-Ensuite comme la macro de repositionnement des colonnes ajoutes des colonnes, la plage de données du TCD est modifiée. Pour éviter ça je duplique toute les données sur un autre onglet et je me sers de cet autre onglet comme plage pour le TCD
-Je mets à jour le TCD

Voilà 😀

 

[sylvanu]

C'est tout ? 😀
Pas envie de rentré dans le code, d'autant qu'il me dit de suite : Erreur, pas de données copiées.

Cependant vous avez oublié le fameux mais Ô combien intéressant :
Application.ScreenUpdating = False

Mettez le après le Sub Coller(), ça va aller un peu, voire beaucoup plus vite.
( sur le module du post #4 je passe sur mon PC de 148ms à 19ms )

 

[Anthonymctm]

C'est tout ? 😀
Pas envie de rentré dans le code, d'autant qu'il me dit de suite : Erreur, pas de données copiées.

Cependant vous avez oublié le fameux mais Ô combien intéressant :
Application.ScreenUpdating = False

Mettez le après le Sub Coller(), ça va aller un peu, voire beaucoup plus vite.
( sur le module du post #4 je passe sur mon PC de 148ms à 19ms )

Il y est juste après toutes les déclarations
Et oui, pas de données copiées, ton presse papier est vide ^^'
Il faudrait copier les données de B1 à L20

 

[sylvanu]

Oups ! 🙄

 

[sylvanu]

Je ne vois pas comment "aller plus vite" avec votre structure.
Peut être avec Powerquery comme le suggère Chris au post #7.
Mais avec mon p'ti XL2007, je ne peux guère vous aider.

 

[Anthonymctm]

Je ne vois pas comment "aller plus vite" avec votre structure.
Peut être avec Powerquery comme le suggère Chris au post #7.
Mais avec mon p'ti XL2007, je ne peux guère vous aider.

Ok merci, on sait jamais, des fois un œil avisé comme le tiens a vite fait de voir une énormité.
Je clôture le topic, tant pis si c'est un peu long.
Merci pour ton aide !

 

[chris]

Bonjour à tous

A priori le fichier posté au #8 est le résultat après transformation

J'ai remis des 01, 02... puis fait la transformation via PowerQuery

A tester : Données, actualiser tout pour mettre à jour

Sur les 600 lignes c'est instantané mais à voir sur le cas réel

 

[Anthonymctm]

Bonjour à tous

A priori le fichier posté au #8 est le résultat après transformation

J'ai remis des 01, 02... puis fait la transformation via PowerQuery

A tester : Données, actualiser tout pour mettre à jour

Sur les 600 lignes c'est instantané mais à voir sur le cas réel

Merci de ton aide, je ne connais pas du tout PowerQuery 😳

Je crois avoir compris que je dois coller mes données sur extraction et ensuite actualiser le power query.

C'est une notion intéressante et puissante, je pense que j'essaierai de m'en servir pour moi même plus tard.
Pour le coup là ya aussi d'autre utilisateurs et je pense que c'est un peu plus source à erreurs pour eux 😀