[VBA] Remplacer par, avec tableau correspondance

Anthonymctm · 29 Juin 2020

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

VB:

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

voilà, merci

sylvanu · 29 Juin 2020

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

VB:

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

sylvanu · 29 Juin 2020

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

VB:

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 · 29 Juin 2020

sylvanu à dit:

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

VB:

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

VB:

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 :

Code:

 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) :

Code:

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 · 29 Juin 2020

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.

VB:

 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 · 29 Juin 2020

sylvanu à dit:
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.

VB:

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 · 29 Juin 2020

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 · 29 Juin 2020

Bonjour à tous

Faisable par PowerQuery intégré à Excel

Poste un exemple de fichier

Anthonymctm · 29 Juin 2020

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 · 29 Juin 2020

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 · 29 Juin 2020

sylvanu à dit:
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 · 29 Juin 2020

Oups !

sylvanu · 29 Juin 2020

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 · 29 Juin 2020

sylvanu à dit:
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 · 29 Juin 2020

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 · 30 Juin 2020

chris à dit:
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

[VBA] Remplacer par, avec tableau correspondance

Anthonymctm

XLDnaute Occasionnel

sylvanu

sylvanu

XLDnaute Barbatruc

Anthonymctm

XLDnaute Occasionnel

sylvanu

XLDnaute Barbatruc

Anthonymctm

XLDnaute Occasionnel

sylvanu

XLDnaute Barbatruc

chris

XLDnaute Barbatruc

Anthonymctm

XLDnaute Occasionnel

Pièces jointes

sylvanu

XLDnaute Barbatruc

Anthonymctm

XLDnaute Occasionnel

sylvanu

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

Anthonymctm

XLDnaute Occasionnel

chris

XLDnaute Barbatruc

Pièces jointes

Anthonymctm

XLDnaute Occasionnel

Discussions similaires