Créer une formule "Concaténer" en VBA

Version imprimable

Bonjour à la communauté.
Voilà, je cherche une solution à ce que je pense être un ".formula".
J'ai des centaines de lignes (colonne A), et je cherche à concaténer les colonnes de 27 (AA) ) à 36 (AJ). Mais en faisant une formule, dans le style "Concatener" de la même ligne...
Mais je dois recréer cette formule pour beaucoup de ligne (j'ai misé sur 5000).
J'avais donc pensé à un .Formula, mais je ne m'en sors pas...
J'ai donc écris ce petit bout de code (que je vais devoir agrandir à l'avenir, car c'est du Excel<>AutoCAD).
Voici ce bout de code :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Sub ReinitExcel() NomFeuille = ActiveSheet.name For L = 2 To 5000 SelectFormule = "" For Col = 27 To 36 SelectFormule = SelectFormule & "A" & Trim(Str(Col)) & ";" Next SelectFormule = Left(Trim(SelectFormule), Len(Trim(SelectFormule)) - 1) SelectFormule = "=Concatener(" & SelectFormule & ")" Selection.Formula = SelectFormule Debug.Print "SelectFormule fini = " & SelectFormule ActiveWorkbook.Worksheets(NomFeuille).Range(SelectFormule).Select.Formula = SelectFormule Next End Su
Si quelqu'un a une réponse, une astuce, une explication... Je suis preneur...
Bien à toi, la communauté.
Denis...

16/12/2021, 06h14
Pierre Fauconnier

1 pièce(s) jointe(s)

Salut.

En XL 365, tu as la fonction JOINDRE.TEXTE qui permet de passer une plage... Soit en Excel, soit en VBA (TEXTJOIN)...

Pièce jointe 610287

Mon billet sur le sujet: https://www.developpez.net/forums/bl...embler-textes/

Salut Pierre, et merci pour ton aide.
Mais j'aimerais que seules les lignes concernées contienne une formule, car je dois gérer le reste ailleurs.
Je n'ai donc pas le choix, car tout ce que je fais, c'est en VBA, et je dois continuer de le faire.
Déjà parce-que j'aime ça ;) , et pour que l'utilisateur ne se prenne pas la tête avec les formules...
J'ai donc fais évoluer mon code, qui donne ceci maintenant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
    NomFeuille = ActiveSheet.name
    Lign = 2
    Do While Sheets(NomFeuille).Cells(Lign, 27).Value <> ""
        SelectFormule = ""
        'Je créé la liste des cellules à concatener selon la 'Lign' en cours
        SelectFormule = "=Concatenate(" & "AA" & Trim(Str(Lign)) & ";"
        SelectFormule = SelectFormule & "AB" & Trim(Str(Lign)) & ";"
        SelectFormule = SelectFormule & "AC" & Trim(Str(Lign)) & ";"
        SelectFormule = SelectFormule & "AD" & Trim(Str(Lign)) & ";"
        SelectFormule = SelectFormule & "AE" & Trim(Str(Lign)) & ";"
        SelectFormule = SelectFormule & "AF" & Trim(Str(Lign)) & ";"
        SelectFormule = SelectFormule & "AG" & Trim(Str(Lign)) & ";"
        SelectFormule = SelectFormule & "AH" & Trim(Str(Lign)) & ";"
        SelectFormule = SelectFormule & "AI" & Trim(Str(Lign)) & ";"
        SelectFormule = SelectFormule & "AJ" & Trim(Str(Lign)) & ")"
        Debug.Print "SelectFormule : " & SelectFormule
        Worksheets(NomFeuille).Range("A" & Trim(Str(Lign))).Formula = SelectFormule
        Lign = Lign + 1
    Loop
    Selection.Columns.AutoFit

Pour aider, voici le résultat du Debug.Print :

Code:

SelectFormule : =Concatenate(AA2;AB2;AC2;AD2;AE2;AF2;AG2;AH2;AI2;AJ2)

18/12/2021, 00h42
Alex020181

1 pièce(s) jointe(s)

Bonjour,

Un truc dans ce goût là ?

Pour le cas où tu n'aies pas 365.

Il te suffit de compléter la valeur des variables de ligne et colonne de début et fin (qui pourraient être automatisées par la suite).

Le premier code te met les valeurs de la concaténation en A. Cela t'évite à passer par les formules mais en contre partie tu perds la partie de remise à jour si les cellules concaténées changent de valeur.
Les autres codes te mettent les formules de 2 façons (juste une question de syntaxe entre les 2 formules).

A toi de voir réellement ton besoin entre les 3 codes (qui sont encore optimisables).

Teste et dis nous.

Tu tagues 365, donc pourquoi ne pas utiliser =TEXTJOIN("",true,aa2:aj2) comme tu utilises CONCATENATE()?

Si tu veux pouvoir fonctionner sans 365, ta solution me semble pertinente. Perso, je travaillerais plutôt avec une boucle, mais chacun sa manière
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Sub Test() Dim r As Range Dim Formule As String Formule = "concatenate(" For Each r In Range("aa2:aj2") Formule = Formule & r.Address & ";" Next Formule = Left(Formule, Len(Formule) - 1) & ")" Debug.Print Formule End Sub
Résultat du Debug.print: concatenate($AA$2;$AB$2;$AC$2;$AD$2;$AE$2;$AF$2;$AG$2;$AH$2;$AI$2;$AJ$2)

Bonjour à tous, et merci pour votre aide.
Voilà, j'ai réécris mon code en faisant un mixte de vos propositions. J'ai donc ça maintenant :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Sub Test() Dim r As Range Dim Formule As String Lign = 2 Formule = "=concatener(" For Each r In Range("aa" & Lign & ":aj" & Lign) Formule = Formule & r.Address & ";" Next Formule = Left(Formule, Len(Formule) - 1) & ")" Debug.Print Formule ActiveSheet.Range("A" & Lign).Formula = Formule End Sub
Mais la ligne "ActiveSheet.Range("A" & Lign).Formula = Formule" (ligne 11), ça plante avec une erreur "1004".
Je ne comprend pas pourquoi, lorsque je colle dans la cellule le résultat du Debug.Print, il n'y a pas de problème...

20/12/2021, 22h22
Jean-Philippe André

Salut,

soit tu passes par Formula et tu dois passer par les noms de fonctions en anglais + le caractère "," pour séparer tes paramètres,
soit tu passes par FormulaLocal et tu peux passer par les noms de fonctions en francais + le caractère ";" pour séparer tes paramètres.

Bien sûr, si tes utilisateurs travaillent avec des versions autres que francaises, ca ne fonctionnera pas.
21/12/2021, 00h09
Alex020181

Jean-Philippe a raison.
Pour .......formula
- utilise la "," et pas les ";" dans ta construction de formule en VBA
- et utilise les noms de fonction en anglais.

Ce qui te donnera

Code:

=Concatenate(AA2,AB2,AC2,AD2,AE2,AF2,AG2,AH2,AI2,AJ2)

Évidement si tu copies colles telle quelle cette formule dans une cellule tu auras une erreur mais quand c'est VBA qui l'insère c'est bon.

À l'inverse pour ...... formulalocal il faut

Code:

=Concatener(AA2;AB2;AC2;AD2;AE2;AF2;AG2;AH2;AI2;AJ2)
21/12/2021, 00h43
DenisHen

Bonjour Alex, et merci pour ton aide.
Mais j'ai essayé "=concatenate", et j'ai le même résultat...
21/12/2021, 00h47
Jean-Philippe André

Peut on avoir ta dernière version de code stp ?

J'aimerai voir comment tu as interprété mes suggestions...
21/12/2021, 00h53
DenisHen

Bonsoir Jean-Philippe,
Mon code est celui que j'ai posté cet après-midi...
Il ne fonctionne ni avec les ";" ni avec les ","...
21/12/2021, 01h52
Alex020181

À 18h44 ?

Normal. Il te manque les guillemets sur ta ligne d'insertion de formule.

Exemples simples

Code:

Range("A2").formula="=1+1"

Donc pour toi

Code:

Range("A2").formula="=concatenete(AA2,AB2)"

Pour insérer des guillemets dans une chaîne de texte tu as là aussi au moins 2 façons. Sinon par défaut Excel considère que tu clos ta chaîne.
Soit en cumulant les guillemets "" (en gros le premier sert d'échappement) pour prendre en compte le second dans la chaîne.
Soit en passant par son code chr(34) --> de mémoire c'est 34 mais à vérifier.

Tu n'as pas mis les guillemets autour de ta formule

Code:

ActiveSheet.Range("A" & Lign).Formula = chr(34) & Formule & chr(34)

Ensuite tu autofill vers le bas pour décaler ta formule si tu n'as pas fais de boucle.
21/12/2021, 07h51
Pierre Fauconnier

Citation:

Envoyé par DenisHen

Bonsoir Jean-Philippe,
Mon code est celui que j'ai posté cet après-midi...
Il ne fonctionne ni avec les ";" ni avec les ","...

tu n'as bien appliqué les conseils.

Si tu utilises "Concatener", tu dois utiliser FormulaLocal et le ;. Si tu utilises formula, tu dois utiliser Concatenate et la , ...

Dans le code que tu as donné, tu fais un mix des deux et donc, ça plante.

Je rappelle que, sauf exercice scolaire ou besoin de travailler avec plusieurs versions d'Excel, ton tag [XL-365] te permet d'utiliser TEXTJOIN(AA2:AJ2) (avec Formula) sans devoir faire de boucle et te préoccuper du séparateur. Pourquoi faire simple lorsque l'on peut faire compliqué? ;)

Bonjour à tous et merci pour votre aide.
Voici la dernière version de mon code, qui fonctionne maintenant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
Sub Test()
  Dim r As Range
  Dim Formule As String
  Lign = 2
  Formule = "=concatener("
  For Each r In Range("aa" & Lign & ":aj" & Lign)
    Formule = Formule & r.Address & ";"
  Next
  Formule = Left(Formule, Len(Formule) - 1) & ")"
  Debug.Print Formule
  ActiveSheet.Range("A" & Lign).FormulaLocal = Formule
End Sub

Et merci encore à vous, j'ai appris beaucoup...

Bonjour,
L'utilisation de la propriété FormulaLocal est toujours dangereux

Voici une autre approche avec Join et l'esperluette (&) comme opérateur de concaténation

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub t()
  Const FirstColumn As Integer = 27 ' Colonne AA
  Const LastColumn As Integer = 36  ' Colonne AJ
  Dim Nc As Integer
  Dim c As Integer
  '
  Nc = LastColumn - FirstColumn ' Nombre de colonnes à traiter
  ReDim ad(Nc)
  For c = 0 To Nc
    ad(c) = Cells(2, c + FirstColumn).Address
  Next
  ' Ecriture de la formule
  ActiveSheet.Range("A" & 2).Formula = "=" & Join(ad, "&")
End Sub

22/12/2021, 15h27
DenisHen

Bonjour Philippe et merci pour ta réponse...
Mais elle est bien au-delà de mon niveau, même si j'admire ton code, je ne pourrais jamais, si j'en ai besoin, le réécrire pour mes besoin...
Mais je reste bouche bé devant une telle logique appliquée...
Merci encore...
Denis...
22/12/2021, 16h08
Philippe Tulliez
Bonjour Dénis,

Citation:

Mais elle est bien au-delà de mon niveau, même si j'admire ton code, je ne pourrais jamais, si j'en ai besoin, le réécrire pour mes besoin...

Cette procédure n'a rien d'exceptionnelle.
C'est une simple boucle qui rempli une variable tableau et la fonction Join renvoie une chaîne de caractères qui est le résultat de la jonction des éléments du tableau séparés par un caractère, ici l'esperluette (&)

Exemple
Le code ci-dessous renverra la chaine "Janvier;Février;Mars".
L'utilisant d'une variable tableau et la fonction Join me semble bien plus simple que de construire la chaîne en utilisant Left, Len et &
Ceci étant dit, comme l'a écrit Pierre, que je salue, et étant donné que tu as tagué 365, il serait plus simple d'utiliser TEXTJOIN basé sur le même principe mais il est possible que le code doit être utilisé sur plusieurs versions
Code:

1 2 3 4 5 Sub t() Dim Trimestre As Variant Trimestre = Array("Janvier", "Février", "Mars") Debug.Print Join(Trimestre, ";") End Sub
A lire éventuellement Utiliser les variables tableaux en VBA Excel
22/12/2021, 16h30
DenisHen

Bonjour Philippe.
Je ne sais pas si tu connais le Lisp (moi, un peu), mais Join est un peu le MapCar du Lisp...
Du coup, je comprends mieux...
Il va falloir que je me renseigne aussi sur le Const, que je ne connais absolument pas...
Encore merci...
22/12/2021, 16h53
Philippe Tulliez
Bonjour,

Citation:

Il va falloir que je me renseigne aussi sur le Const, que je ne connais absolument pas...

Const est la déclaration d'une constante suivi de sa valeur
Au lieu de Const FirstColumn As Integer = 27, j'aurais pu écrire
Code:

1 2 Dim FirstColumn As Integer FirstColumn = 27
A lire éventuellement Utiliser les variables en VBA Excel et tout particulièrement le chapitre VI. Les instructions Const et Enum
22/12/2021, 16h56
DenisHen

Donc, Const est un Integer ?

il faut faire simple
Code:

1 2 3 4 5 6 7 8 9 10 sub concat() dim colonne , ligne as integer For ligne = 27 to 36 For colonne = 27 to 36 sheets("Feuil").cells(ligne, colonne) = sheets("Feuil").cells(ligne, colonne - 1 ) & sheets("Feuil").cells(ligne, colonne) Next colonne Next ligne End Sub
Citation:

Envoyé par DenisHen

Bonjour Philippe et merci pour ta réponse...
Mais elle est bien au-delà de mon niveau, même si j'admire ton code, je ne pourrais jamais, si j'en ai besoin, le réécrire pour mes besoin...
Mais je reste bouche bé devant une telle logique appliquée...
Merci encore...
Denis...

22/12/2021, 17h32
Philippe Tulliez

Bonjour,

Citation:

Donc, Const est un Integer ?

Non, c'est la constante nommée FirstColumn qui est un Integer.Tout est bien expliqué dans le tutoriel que j'ai référencé
22/12/2021, 17h50
DenisHen

Bonsoir à vous.
Mach1974 :
J'ai découvert que "dim colonne , ligne as integer" faisait colonne comme Variant et ligne comme integer.
Mais ça ne me pose aucun problème, avec nos PC de maintenant, nous ne sommes plus à quelques octets près...
Philippe :
J'ai lu des centaines de tutos sur le super site que nous utilisons... Je commence à me perdre, il y a tellement de solution pour un seul et même problème...
Mais je vous remercie, vous êtes d'une grande aide...
Encore merci à vous...
Denis...