Discussion :

[DenisHen]

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 :

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

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...

[Pierre Fauconnier]

Salut.

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




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

[DenisHen]

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 :

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

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

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

[Alex020181]

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.

[Pierre Fauconnier]

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 :

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

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)

[DenisHen]

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 :

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

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...

[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.

[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 formule :

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

=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 formule :

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

=Concatener(AA2;AB2;AC2;AD2;AE2;AF2;AG2;AH2;AI2;AJ2)

[DenisHen]

Bonjour Alex, et merci pour ton aide.
Mais j'ai essayé "=concatenate", et j'ai le même résultat...

[Jean-Philippe André]

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

J'aimerai voir comment tu as interprété mes suggestions...

[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 ","...

[Alex020181]

À 18h44 ?

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

Exemples simples

Code :

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

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

Donc pour toi

Code :

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

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 :

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

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.

[Pierre Fauconnier]

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é?

[DenisHen]

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

Code :

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

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...

[Philippe Tulliez]

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 :

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

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

[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...

[Philippe Tulliez]

Bonjour Dénis,

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 :

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

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

[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...

[Philippe Tulliez]

Bonjour,

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 :

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

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

[DenisHen]

Donc, Const est un Integer ?