Concaténer suivant un critère variable
Bonjour à tous,
Je suis face à un problème pour concaténer 4 cellules suivant un critère.
Dans mon exemple (fichier ci-joint), j'ai mon critère qui début en F13 qui s'incrémente de 7 en 7. Et pour chaque critère, j'ai 4 cellules (G18-H18-I18 et J18) que je veux concaténer.
Sauf que je ne trouve pas ma solution pour concaténer une cellule + 7 lignes du style :
=concatener(G(i+7);H(i+7);I(i+7);J(i+7))
G, H, I, J étant des colonnes
Je ne sais pas si cela est clair mais voici le fichier excel qui parlera mieux que moi.
Merci,
Fred
Bonjour Paritec,
Merci pour la réponse rapide, c'est exactement ça que je voulais !
Il va me falloir quelques minutes pour déchiffrer ta macro
Fr3d
Re Fred fr3d le forum
Elle est d’une simplicité enfantine tu verras
A plus
Papou
Bonjour Fr3d, paritec,
Je te retourne ton fichier modifié (c'est une solution différente de celle de paritec) :
À l'ouverture du fichier, la cellule active est K18, et elle est vide : aucune valeur ni formule (c'est idem pour K25 et K32).
Ctrl t ➯ travail effectué ; regarde la formule de K18, puis celle de K25 et K32.
(j'ai proposé ma solution surtout car je pensais que tu voulais des formules, pas des valeurs)
Alt F11 pour voir la macro, puis revenir sur Excel.
dhany
Merci pour ta réponse !
En fait, après je dois convertir ces valeurs car dans mon cas actuel, j'ai des valeurs comme ceci dans les 4 cellules à concaténer :
Cellule G18 :
" - PIECE_1 - PIECE_2 - PIECE_3 - PIECE_4"
Cellule H18 :
" - PIECE_11 - PIECE_21 - PIECE_31 - PIECE_41"
Cellule I18 :
" - PIECE_12 - PIECE_22 - PIECE_32 - PIECE_42"
Cellule J18 :
" - PIECE_13 - PIECE_23 - PIECE_33 - PIECE_43"
Et mes manipulations qui viennent sont de convertir ces valeurs pour supprimer les espaces et les tirets pour former un tableau comme ceci :
PIECE_1 TOTO0
PIECE_2 TOTO0
PIECE_3 TOTO0
PIECE_4 TOTO0
PIECE_11 TOTO1
PIECE_21 TOTO1
PIECE_31 TOTO1
PIECE_41 TOTO1
PIECE_12 TOTO2
PIECE_22 TOTO2
PIECE_32 TOTO2
PIECE_42 TOTO2
PIECE_13 TOTO3
PIECE_23 TOTO3
PIECE_33 TOTO3
PIECE_43 TOTO3
Voilà mon but final mais ça je vais chercher un peu
Pour de ce qui est des 2 macros proposés, j'ai compris que ce symbole "&" est bien mieux en VBA que "CONCATENER"
Bonjour Fr3d,
Tu a écrit :Voilà mon but final mais ça je vais chercher un peu
Comme ton message est de 08:45 et qu'il est 16:30, je suis sûr que tu as largement eu le temps de chercher ! un peu, beaucoup, passionnément... ! si, si, tu sais : comme les pâquerettes dont on enlève les pétales un par un !
Aussi, comme l'heure tourne, je te propose ce fichier Excel :
À l'ouverture du fichier, regarde ce qui est noté en B2 et C2 ; fais Ctrl e ; vu ?
(et tu peux utiliser le filtre sur ce tableau pour n'en faire ressortir qu'une partie)
NB : Le code VBA de la 2ème macro n'utilise pas la colonne K
Si besoin, tu peux demander une adaptation.
Merci de me donner ton avis.
dhany
Merci, c'est exactement ce que je voulais au final
Bah en fait, j'ai pas eu le temps car j'étais au travail (Même si cette macro me servira pour un travail).
Je vais essayer de comprendre tout ça quand même, cela pourrait me servir plus tard
Si besoin, tu peux demander une adaptation.
Merci de me donner ton avis.
dhany
Salut dhany,
J'aimerai comprendre un peu ta macro car je ne connais pas toutes les fonctionnalités en VBA.
Tbl = Split(.offset(,j),"-")
Cela veut dire que tu créés une variable Tbl qui décale suivant la variable j par le séparateur "-" ?
Le reste, je ne comprend vraiment pas ^^
Bonjour Fr3d,
Je vais t'expliquer toute cette partie de mon code VBA (car tu verras que tout est lié) :
For i = 13 To DLig 'Boucle de la ligne n° 13 à la Dernière ligne trouvée
Bloc = Cells(i, 6)
If Bloc <> "" Then
With Cells(i + 5, 7)
For j = 0 To 3
Tbl = Split(.Offset(, j), "-")
For k = 0 To UBound(Tbl)
chn = Trim$(Tbl(k))
If chn <> "" Then
Cells(lig, 2) = chn: Cells(lig, 3) = Bloc: lig = lig + 1
End If
Next k
Next j
End With
End If
Next iD'après ce que j'avais déjà mis en commentaire, tu sais que DLig est la dernière ligne utilisée, selon la colonne F ; dans l'exemple du fichier, en colonne F, c'est la ligne 27 (même si elle fait partie de la fusion F27:F32) ; ainsi, c'est bien 27 et pas la ligne 32 !
Donc : For i = 13 To DLig ➯ i va de 13 à 27 ; toute la suite est ce qui se passe pour la 1ère valeur 13 :
Bloc = Cells(i, 6) : on met dans la variable Bloc (qui est une chaîne de caractères) la valeur de la cellule Cells(i, 6) ; comme i vaut 13, il s'agit de la cellule en ligne 13 et colonne 6, donc F13 ➯ Bloc = "TOTO0"
If Bloc <> "" Then : on fait la suite seulement si Bloc n'est pas une chaîne vide ; donc comme i va de 13 à 27, ça permet d'éviter le traitement qui va suivre si la cellule de ces lignes, pour la colonne F, est vide.
With Cells(i + 5, 7) : comme i vaut 13, ligne i + 5 = 13 +5 = 18 et colonne 7, donc c'est la cellule G18, qui est, pour la ligne 18, la 1ère des 4 cellules contenant des pièces.
For j = 0 To 3 : comme il va falloir lire 4 cellules à partir de G18, et que j va servir d'offset (décalage) depuis cette cellule G18, j va de 0 à 3 (et non pas de 1 à 4).
Tbl = Split(.Offset(, j), "-") : attention, pour .Offset(, j) remarque bien la virgule, et qu'il n'y a rien entre la parenthèse gauche et cette virgule ! ça veut dire que par rapport au G18 du With qui précède, c'est la même ligne 18 ; et c'est la colonne G avec un décalage colonne j de 0 à 3 ➯ les 4 cellules lues seront bien : G18 ; H18 ; I18 ; J18
NB : .Offset(, j) est pareil que .Offset(0, j) ; simplement, le 1er est plus court à écrire et à lire.
Quand j = 0, c'est donc G18 ; et Tbl = Split(.Offset(, j), "-") = Tbl = Split([G18], "-")
Le Split fait qu'en utilisant le caractère séparateur indiqué, ici un tiret "-" on « décompose » le contenu de G18, dont chaque élément est placé dans la variable Tbl (de type Variant implicite) à partir de l'indice 0.
Ainsi, comme G18 contient " - PIECE_1 - PIECE_2 - PIECE_3 - PIECE_4", on se retrouve avec :
Tbl(0) = " ", car c'est l'espace qui est AVANT le 1er tiret
Tbl(1) = " PIECE_1 "
Tbl(2) = " PIECE_2 "
Tbl(3) = " PIECE_3 "
Tbl(4) = " PIECE_4"On a donc bien séparé chaque élément de G18.
For k = 0 To UBound(Tbl) : UBound est l'abréviation de UpperBound : indice élevé de Tbl, ici 4 ; donc avec k de 0 à 4, on va bien lire successivement chaque élément de Tbl ; et pour chacun :
chn = Trim$(Tbl(k)) : Trim$ enlève les espaces à gauche et à droite de Tbl(k), donc chn sera successivement : "" ; "PIECE_1" ; "PIECE_2" ; "PIECE_3" ; "PIECE_4" ; attention : note bien que pour Tbl(0), chn est une chaîne de caractères vide ! c'est pourquoi y'a ensuite ce test pour l'éviter : If chn <> "" Then
Cells(lig, 2) = chn: Cells(lig, 3) = Bloc: lig = lig + 1 : sur la ligne lig, ça écrit en colonne B un élément (non vide) de Tbl ; et juste à droite en colonne C, ça écrit Bloc, c'est-à-dire "TOTO0".
Avec une explication aussi longue et détaillée, tu comprends pourquoi ma réponse à beaucoup tardé !
dhany
Cells(lig, 2) = chn: Cells(lig, 3) = Bloc: lig = lig + 1: sur la ligne lig, ça écrit en colonne B un élément (non vide) de Tbl ; et juste à droite en colonne C, ça écrit Bloc, c'est-à-dire "TOTO0".
J'ai un peu de mal avec ce détail-ci. C'est peut être tout bête mais est ce que ça revient à faire ceci :
Cells(lig, 2) = chn
Cells(lig, 3) = Bloc
lig = lig + 1
En gros le ":" sert à tout écrire sur la même ligne de code ? Ou cela à une autre fonction ?
J'ai une autre question sur les variables que tu as déclarées.
Pourquoi mets-tu des "&" ou "$" ?
Dim Tbl, Bloc$, chn$, DLig&, lig&, i&, j As Byte, k As Byte: lig = 3
Bonjour Fr3d,
Tu a écrit :En gros le ":" sert à tout écrire sur la même ligne de code ?
Tout à fait exact ! en gros, et en détail aussi !
⚠ Quand je dis qu'il n'a pas d'autre fonction, c'est dans ce contexte ; car par exemple, le ":" sert aussi pour une référence de plage : Range("B5:G20") écrit en notation abrégée [B5:G20] ; le ":" peut aussi être utilisé dans les "Case" d'un bloc "Select Case .. End Select" ou encore pour une étiquette de branchement (Label) ; voir l'aide VBA pour plus d'infos.
Tu a écrit :Pourquoi mets-tu des "&" ou "$" ?
Ce sont des caractères de déclaration de type de variable :
Dim Bloc$ équivaut à Dim Bloc As String
Dim DLig& équivaut à Dim DLig As Long
Il y a aussi "%" pour Integer ; "!" pour Single ; "#" pour Double ; "@" pour Currency ; mais y'en a pas pour Byte (par exemple).
As-tu remarqué que Tbl n'a pas de caractère de déclaration de type ? ni de type déclaré avec "As" ? ce n'est pas un oubli !
Une variable déclarée avec Dim sans indication de type est par défaut, implicitement, du type Variant ;
c'est donc la même chose que si j'avais mis explicitement : Dim Tbl As Variant
et c'est ce qu'il faut pour une variable qui doit recevoir des éléments séparés par Split()
dhany
Ah okkkkkk là j'ai tout compris
J'avais quelques bases (Vues à l'école) mais jamais pratiqué le VBA pour mon travail jusqu'à maintenant
Un gros merci pour tes explications, je vais les garder précieusement
Bonjour,
Je relance le sujet.
Je dispose d'un fichier de communes, avec notamment leurs heures d'ouverture.
Il y a une ligne par jour et plage d'ouverture, et 4 colonnes : jour début, jour fin, heure début, heure fin.
J'ai réussi à concaténer les quatre colonnes en gardant le format avec la formule :
=TEXTE(Tableau2[@début];"j")&" - "&TEXTE(Tableau2[@fin];"j")&" - "&TEXTE(Tableau2[@début3];"hh:mm")&" à "&TEXTE(Tableau2[@fin4];"hh:mm")
Jusqu'ici tout va bien, j'ai obtenu une colonne B avec le numéro INSEE de la Commune, et une colonne X avec les plages d'ouverture.
Maintenant, pour une commune, il y a autant de lignes que de plages d'ouverture.
Mon besoin est de concaténer toutes les cellules obtenues précédemment de la colonne X.
Pour au final avoir une ligne par commune, et toutes les plages d'ouvertures concaténées dans la case X correspondant à la Commune.
Il faut donc concaténer toutes les cases sur un critère : un numéro INSEE identique. Ce numéro est en colonne B.
Donc, la logique, c'est :
si B2=B1, alors on concatène X1 & X2
si B3=B2 (=B1), alors on concatène X1, X2 & X3.
si Bn=Bn+1, alors on concatène X1, X2, ..., Xn+1
On s'arrête pour Bn<>Bn+1.
Et on relance le cycle pour la commune suivante, c'est à dire pour le numéro INSEE suivant.
Les solutions VBA que vous évoquez sont du chinois pour moi. Il faudrait que je m'y mette...
En attendant, je suis preneur d'un peu d'aide !
J'ai essayé d'uploader un extrait de mon fichier Excel, mais je ne trouve pas le moyen de le faire.
Merci