Discussion :

[slakhdine]

Bonjour à tous .
Je vous adresse vu la situation délicate dans j'en suis ,je serai très reconnaissant si vous pourriez me donner un coup de main.
je vous explique mon soucis : j'ai un ficher qui contient des client avec leurs informations (Anonymisées) dans la feuille DATA .et sur la feuille SMS une table contient pour chaque cas le message que je dois personnalisé pour chaque client et le mettre dans une cellule a la fin du tableau des client ( "DATA") plus précisément la colonne T de la feuille "DATA" . j'ai fait une macro test avec juste le premier message pour le premier cas "RELANCE 1" c à d tous les client qui ont sur la colonne P de la feuille "DATA" RELANCE 1 il faut les construire un message comme le modèle sur la cellule B1 de la feuille "SMS" et pareil pour les autres cas. mais malheureusement la macro elle n’exécute pas ,ils me disent objet requis je ne sais pas d'où ç'a vient.
merci par avance pour votre aide.
ci joint le fichier sur le quel je travail et la macro se trouve sur la feuille 3 "SMS"

[ARTURO83]

Bonjour,

Vous pouvez utiliser la formule suivante en colonne T1 et à tirer vers le bas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SIERREUR(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(INDEX(SMS!$A:$B;EQUIV($P1;SMS!$A:$A;0);2); "(Colonne C)";C1;1);"(Colonne D)";D1;1);"(Colonne B)";B1;1);"(Colonne M)";M1;1);"(Colonne Q)";Q1;1);"")

Ou en VBA, qui crée la formule et fige les valeurs sur l'ensemble de la colonne T

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub Form()
    Dim DerLig As Long
    Application.ScreenUpdating = False
    DerLig = [A10000].End(xlUp).Row
    Range("T1:T" & DerLig).FormulaR1C1 = "=IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INDEX(SMS!C1:C2,MATCH(RC16,SMS!C1,0),2), ""(Colonne C)"",RC[-17],1),""(Colonne D)"",RC[-16],1),""(Colonne B)"",RC[-18],1),""(Colonne M)"",RC[-7],1),""(Colonne Q)"",RC[-3],1),"""")"
    Range("T1:T" & DerLig).Value = Range("T1:T" & DerLig).Value
End Sub

Cdlt

[slakhdine]

Merci @ARTURO83 pour ta réactivité. Pour mes données elles doivent être mises à jour à chaque fois parce que c'est des données que j'extrais depuis SQL Server. Est-ce que ton code vba va bien marcher dans ce cas ?

[MarcelG]

Bonjour,

Personnellement, dès que le nombre de parenthèses sur une ligne de code, ou dans la formulation d'une cellule, dépasse 3, j'ai la tête qui tourne.
Mieux vaut, à mon avis, utiliser une valeur qui se modifie sur plusieurs lignes de code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
With c
   .Formula = "laformule" 
   .Value = .Value
   .Replace What:="toto", Replacement:="tata"
   .Replace What:="maman", Replacement:="papa"
End With

On peut tout aussi bien utiliser le langage VBA pour définir la 1ère valeur

Pour la méthode Replace affectée à un objet Range, voir cet espace

[ARTURO83]

En réponse au post 3: est ce que ton code vba il va bien marcher dans ce cas ? OUI

[slakhdine]

J'ai regardé ta formule et j'ai remarqué qu'elle fait exactement ce que je voulais merci beaucoup. Mais juste un petit bémol c'est que pour la colonne R elle m'affiche pas la valeur à sa place c'est dû à ma faute parce que j'ai pas précisé d'où extraire le numéro de téléphone, je t'explique en fait j'ai modifié le message à personnaliser et j'ai mis la colonne B de la feuille MAPPING c'est de là qu'il faut extraire le numéro mais selon une condition si le nom de l’éditeur qui se situe dans la colonne qui se trouve dans la colonne O de la feuille DATA correspond au nom d’éditeur dans la colonne A sur la feuille MAPPING. Je sais que je t'ai dérangé un peu avec mes demandes merci infiniment pour tes réponses
voici le fichier à jour

[Pierre Fauconnier]

Salut.

Dans ce genre de situations, j'utilise une fonction qui se trouve dans ma boite à outils, et à laquelle je passe la chaine et un tableau de valeurs de remplacement. Comme Marcel (Hello), je n'apprécie que modérément les substitue(substitue(substitue(substitue(substitue(substitue(substitue( et je préfère alors travailler avec une fonction générique.

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
Public Function ReplaceStrings(Source As String, Parameters) As String
  Dim i As Long
 
  ReplaceStrings = Source
  For i = LBound(Parameters) To UBound(Parameters) Step 2
    ReplaceStrings = Replace(ReplaceStrings, Parameters(i), Parameters(i + 1), 1, -1, vbTextCompare)
  Next i
End Function
 
Sub Test()
  Dim s As String
 
  s = "Bonjour {firstname}, ton identifiant est {id}"
  s = ReplaceStrings(s, Array("{firstname}", "Pierre", "{id}", "Nounours"))
  Debug.Print s
End Sub

[ARTURO83]

Bonjour à tous,

Evidemment les propositions de MarcelG et Pierre FAUCONNIER sont excellentes et justifiées. N'y voyez pas là de l'obstination mais bon, comme j'avais commencé avec une formule excel et comme cela convenait à notre ami slakhdine et que par la même occasion il demandait une petite modification, je lui livre quand même, histoire de ne pas avoir passé du temps pour rien.

=SIERREUR(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(INDEX(SMS!$A:$B;EQUIV($P4;SMS!$A:$A;0);2); "(Colonne C)";C4;1);"(Colonne D)";D4;1);"(Colonne B)";INDEX(MAPPING!$A:$B;EQUIV($O4;MAPPING!$A:$A;0);2);1);"(Colonne M)";M4;1);"(Colonne Q)";Q4;1);"")
Cdlt

[Pierre Fauconnier]

Si ça doit être utilisé dans une feuille de calcul (ça semble être le cas mais je n'y avais pas fait attention), je préfère alors aussi la solution par formule, même si elle imbrique des Substitue (voir ma signature à propos du VBA dans Excel ).

Par contre, il me semblerait alors intéressant de travailler avec des tables de données plutôt que sur l'ensemble des colonnes de la feuille (=> ne pas utiliser A:B comme référence pour les Index-Equiv). Les tables de données et les références structurées amènent confort, lisibilité, facilité d'écriture de formules, sécurité et fiabilité dans le classeur (voir mon tuto à ce sujet).

[slakhdine]

Bonjour à tous,

Evidemment les propositions de MarcelG et Pierre FAUCONNIER sont excellentes et justifiées. N'y voyez pas là de l'obstination mais bon, comme j'avais commencé avec une formule excel et comme cela convenait à notre ami slakhdine et que par la même occasion il demandait une petite modification, je lui livre quand même, histoire de ne pas avoir passé du temps pour rien.

=SIERREUR(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(INDEX(SMS!$A:$B;EQUIV($P4;SMS!$A:$A;0);2); "(Colonne C)";C4;1);"(Colonne D)";D4;1);"(Colonne B)";INDEX(MAPPING!$A:$B;EQUIV($O4;MAPPING!$A:$A;0);2);1);"(Colonne M)";M4;1);"(Colonne Q)";Q4;1);"")
Cdlt

Merci @ARTURO83 pour le temps que vous consacrez pour me répondre . votre réponse a été très utile pour moi merci encore une fois et merci également à monsieur Pierre et Marcel pour vos réponses.
bien cordialement.

[slakhdine]

bonjour tout le monde , il me reste juste un petit soucis simple c'est de retourner a la ligne j'ai appliquer l'instruction suivante " _ &" mais elle ne marche pas je crois c'est parce dans la formule que j'ai intergré dans ma macro il y a plusieurs de double crochets du coup le compilateur il comprend pas d'où ça commence la ligne et jusqu’à où ça termine. merci par avance.
voici ma formule final que je souhaite l'integrer dans le code

=SIERREUR(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(INDEX(SMS!$A:$B;EQUIV($P1;SMS!$A:$A;0);2); "(Colonne C)";C1;1);"(Colonne D)";D1;1);"(Colonne B)";B1;1);"(Colonne M)";SI(M1="";N1;M1);1);"(Colonne Q)";Q1;1);"(Colonne B de la feuille MAPPING)";INDEX(MAPPING!$A:$B;EQUIV($O1;MAPPING!$A:$A;0);2);1);"")

ci dessous le code que je viens de mettre à jour mais un message 404 d'erreur qui se répète malheureusement.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
Sub Form()
    Dim DerLig As Long
    Application.ScreenUpdating = False
    DerLig = [A10000].End(xlUp).Row
    Range("T1:T" & DerLig).FormulaR1C1 = "=IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INDEX(SMS!$A:$B,MATCH($P1,SMS!$A:$A,0),2), ""(Colonne C)"",C1,1),""(Colonne D)"",D1,1),""(Colonne B)"",B1,1),""(Colonne M)"",IF(M1="",N1,M1),1),""(Colonne Q)"",Q1,1),""(Colonne B de la feuille MAPPING)"",INDEX(MAPPING!$A:$B,MATCH($O1,MAPPING!$A:$A,0),2),1),"")"
    Range("T1:T" & DerLig).Value = Range("T1:T" & DerLig).Value
End Sub

cordialement.

[ARTURO83]

Bonjour,

La formule en VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 Range("T1:T" & DerLig).FormulaR1C1 = "=IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INDEX(SMS!C1:C2,MATCH(RC16,SMS!C1,0),2), ""(Colonne C)"",RC[-17],1),""(Colonne D)"",RC[-16],1),""(Colonne B)"",INDEX(MAPPING!C1:C2,MATCH(RC15,MAPPING!C1,0),2),1),""(Colonne M)"",RC[-7],1),""(Colonne Q)"",RC[-3],1),"""")"

Cdlt

[MarcelG]

Bonjour,

J'aurais 2 remarques à formuler
- La méthode Formulalocal permet une écriture dans le langage de l'utilisateur. Elle est donc plus souple à coder.
- Devant une telle formule, un collage de valeur me semblerait opprtun

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
With c
 .Value = .Value
End With

[slakhdine]

Bonjour,

La formule en VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 Range("T1:T" & DerLig).FormulaR1C1 = "=IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INDEX(SMS!C1:C2,MATCH(RC16,SMS!C1,0),2), ""(Colonne C)"",RC[-17],1),""(Colonne D)"",RC[-16],1),""(Colonne B)"",INDEX(MAPPING!C1:C2,MATCH(RC15,MAPPING!C1,0),2),1),""(Colonne M)"",RC[-7],1),""(Colonne Q)"",RC[-3],1),"""")"

Cdlt

merci pour votre retour .en fait j'ai rajouter des conditions sur la formule initiale du coup voici la dernière version est ce que c'est possible de me la tester svp parce que elle marche pas chez moi.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
Sub Form()
    Dim DerLig As Long
    Application.ScreenUpdating = False
    DerLig = [A10000].End(xlUp).Row
    Range("T1:T" & DerLig).FormulaR1C1 = "=IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INDEX(SMS!C1:C2,MATCH(RC16,SMS!C1,0),2), ""(Colonne C)"",RC[-17],1),""(Colonne D)"",RC[-16],1),""(Colonne B)"",RC[-18],1),""(Colonne M)"",IF(RC[-7]="""",RC[-6],RC[-7]),1),""(Colonne Q)"",RC[-3],1),""(Colonne B de la feuille MAPPING)"",INDEX(MAPPING!C1:C2,MATCH(RC15,MAPPING!C1,0),2),1),"""")"
    Range("T1:T" & DerLig).Value = Range("T1:T" & DerLig).Value
End Sub

merci par avance .

[Pierre Fauconnier]

Hello

Pas d'accord pour le formulalocal. Si on utilise par la suite une autre version linguistique, ça plante. Perso, je pense qu'en VBA, on écrit en VBA et pas dans un mix "International/Local". Ce n'est que mon avis...

Pour le collage valeur, il est fait en fin de course. Je trouve justement que c'est une très bonne méthode qui est bien plus rapide qu'une boucle pour placer les valeurs...

(Pas d'attaque personnelle, hein, juste mon constat technique )

Bonjour,

J'aurais 2 remarques à formuler
- La méthode Formulalocal permet une écriture dans le langage de l'utilisateur. Elle est donc plus souple à coder.
- Devant une telle formule, un collage de valeur me semblerait opprtun

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
With c
 .Value = .Value
End With

[slakhdine]

Bonjour,

J'aurais 2 remarques à formuler
- La méthode Formulalocal permet une écriture dans le langage de l'utilisateur. Elle est donc plus souple à coder.
- Devant une telle formule, un collage de valeur me semblerait opprtun

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
With c
 .Value = .Value
End With

bonjour. MarcelG et merci pour votre réponse c'est possible de me préciser où je dois rajouter l'expression de collage de valeur de la formule ?
cdlt

[Pierre Fauconnier]

bonjour. MarcelG et merci pour votre réponse c'est possible de me préciser où je dois rajouter l'expression de collage de valeur de la formule ?
cdlt

C'est fait par cette ligne en fin de process

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("T1:T" & DerLig).Value = Range("T1:T" & DerLig).Value

C'est là tout l'intérêt de travailler avec la formule sur l'ensemble de la plage!

Il faut faire attention à bien comprendre les solutions qui te sont données par les uns et les autres, car la solution de Marcel ne s'applique pas dans ton contexte où tu places la formule sur l'ensemble de la plage.

[MarcelG]

Salut Pierre,

Je le sais
Le choix Formula\Formulalocal fait parfois débat.

On peut donc utiliser Formula.

Par contre, je ne suis pas fan de l'écriture R1C1.

D'autre part, comme Philippe (salut à lui), j'opterais pour une déclaration constante de formule en début de procédure.
Celle-ci serait affectée ensuite à l'objet Range concerné.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Const la formule As String = "laformule"
 
With Range("lechamp")
  .Formula = laformule
  .Value = .Value
End With

Ceci en sachant que les arguments d'adresse (ligne et colonne), s'ils ne sont pas absolus, s'incrémentent automatiquement.

[ARTURO83]

A Slakhdine

Plutôt que d'envoyer votre code, je préférerai que vous disiez quelle est la condition supplémentaire que vous avez ajouté.

[Pierre Fauconnier]

Salut Marcel,

J'ai définitivement opté pour le Formula, qui est simple à rédiger lorsque l'on rédige la formule dans une cellule et qu'on utilise la fenêtre d'exécution pour récupérer la formule internationale.

Pour les autres remarques, je te rejoins entièrement, et je vais même jusqu'à créer la formule avec des bracketsValues que je remplace grâce à une fonction que j'ai donnée plus haut dans la discussion (message #7)... Ca évite de se mêler les pinceaux dans la concaténation de la formule