Concaténer des cellules non vides

Bonjour

Dans l'attente de réponses à d'autres questions, je cherche une solution pour concaténer plusieurs cellules non vides ensemble.

Dans le fichier joint, par exemple :

Dans "Progression 4eme" je voudrais :

Concaténer D5 avec E5 puis "retour de ligne" puis concaténer D6 avec E6 puis "retour de ligne" puis concaténer D7 avec E7.

L'ensemble ira dans la feuille "Progression en K5

Puis :

Concaténer D8 avec E8 puis "retour de ligne" puis concaténer D9 avec E9 puis "retour de ligne" puis concaténer D10 avec E10 puis retour de ligne puis concaténer D11 avec E11 .

L'ensemble ira dans la feuille "Progression en L5

Et ainsi de suite ... pour la zone D12 à E29 pour aller dans M5.

Et idem pour S002 et S003 ... jusqu'à S130 environ ...

Le même principe se fera pour les futures feuilles "Progression 6eme", "Progression 5eme", "Progression 3eme".

L'idéal c,'est que la concaténation ne se fasse qu'à la demande, ligne par ligne, (S001 à S008...) A voir ... Après il y aura un report dans Planning 4eme, mais je ne sais pas comment cela se fera, il faut que je réfléchisse ...

Merci pour les pistes

bonjour

vite supprime toutes les cellules fusionnées : ça cause des tonnes de problèmes par la suite

débrouille-toi autrement pour faire joli. Courage

va voir le doc joint au dernier onglet, sans macro

jmd a écrit :

bonjour

vite supprime toutes les cellules fusionnées : ça cause des tonnes de problèmes par la suite

Je pense que je n'ai pas trop de choix .. et je n'ai pas trop de formules à ces endroits ...

jmd a écrit :

débrouille-toi autrement pour faire joli. Courage

va voir le doc joint au dernier onglet, sans macro

"débrouille-toi autrement pour faire joli ?" C'est à dire ?

Sans macro, je sais faire, mais je ne te raconte pas la complexité ... Et puis tu n'as pas concatener les 3 lignes ensembles, et comment faire si les cellules sont vides etc ...

J'ai peur que ta piste soit très compliqué à mettre en oeuvre. Mais je vais y réfléchir ...

Merci

Bonjour,

Function ConcatPlageCelNonVides(Plage_Source As Range, Optional séparateur As String = ", ") As String
    Dim rep As String, c As Range
    Dim datas, lig As Long, col As Long, s As String
    datas = Plage_Source.Value
    For lig = 1 To UBound(datas, 1)
        s = ""
        For col = 1 To UBound(datas, 2)
            s = s & IIf(datas(lig, col) = "", "", séparateur & datas(lig, col))
        Next col
        If s <> "" Then
            ConcatPlageCelNonVides = ConcatPlageCelNonVides & IIf(ConcatPlageCelNonVides = "", "", Chr(10)) & Mid(s, Len(séparateur) + 1)
        End If
    Next lig
End Function

Exemples simple en K5:K6, exemple pour recopie incrémentée en L5

Cocher 'renvoyer à la ligne automatiquement' dans le format de cellule.

Fonction dans Module1

eric

Bonjour à vous

RIPcagou a écrit :

jmd a écrit :

bonjour

vite supprime toutes les cellules fusionnées : ça cause des tonnes de problèmes par la suite

Je pense que je n'ai pas trop de choix .. et je n'ai pas trop de formules à ces endroits ...

jmd a écrit :

débrouille-toi autrement pour faire joli. Courage

va voir le doc joint au dernier onglet, sans macro

"débrouille-toi autrement pour faire joli ?" C'est à dire ?

Sans macro, je sais faire, mais je ne te raconte pas la complexité ... Et puis tu n'as pas concatener les 3 lignes ensembles, et comment faire si les cellules sont vides etc ...

J'ai peur que ta piste soit très compliqué à mettre en oeuvre. Mais je vais y réfléchir ...

Merci

je voulais dire que sans fusions de cellules, il faut se débrouiller autrement pour faire de jolies présentations. On y arrive toujours.

note bien que les fusions que tu fais te causeront des problèmes un jour ou l'autre, car n'oublie pas qu'un bon fichier excel va durer des années et que tu feras des modifications de temps en temps. Pas de fusions c'est une manière de se préparer à un avenir simple.

concaténer 2 ou 6 cellules, c'est pareil, non ?

s'il y a des cellules vides, c'est sans importance

si tu veux un retour chariot

extrait du site Microsoft :

- Sélectionnez la cellule A3

- Entrez la formule correspondante suivante

=A1&CAR(10)&A2 (pour Excel pour Windows ou Windows NT)

=A1&CAR(13)&A2 (pour Excel pour Macintosh)

- Choisissez la commande Cellule du menu Format, et dans l'onglet

Alignement, cochez Renvoi à la ligne automatique (pour Excel 5.0 et 7.0)

et Format Position (pour Excel 3.0 et 4.0)

REMARQUES :

La formule suivante peut également être entrée.

=A1&""&A2

Prenez soin d'entrer entre les guillemets, la combinaison de touches

correspondante à la version d'Excel :

- avec Excel pour Windows ou Windows NT, appuyez simultanément sur les 2

touches ALT+ENTREE.

- avec Excel pour Macintosh, appuyez simultanément sur les 3 touches

OPTION+COMMANDE+ENTREE

pas de VBA (que je n'aime pas et qui ne m'aime pas non plus )

jmd a écrit :

Bonjour à vous

je voulais dire que sans fusions de cellules, il faut se débrouiller autrement pour faire de jolies présentations. On y arrive toujours.

note bien que les fusions que tu fais te causeront des problèmes un jour ou l'autre, car n'oublie pas qu'un bon fichier excel va durer des années et que tu feras des modifications de temps en temps. Pas de fusions c'est une manière de se préparer à un avenir simple.

concaténer 2 ou 6 cellules, c'est pareil, non ?

s'il y a des cellules vides, c'est sans importance

pas de VBA (que je n'aime pas et qui ne m'aime pas non plus )

S'il y a 2 cellules vides sur une même ligne et que je programme un retour chariot, il y aura une ligne vide que je ne veux pas.

Le problème, c'est que potentiellement il y aura 1800 cases à remplir, avec une zone source qui risque de changer au cours du temps ...

J'ai déjà fait de telles formules mais avec de la recopie cela se faisait sans trop de problème. (le fichier fait 12 Mo). Mais là, avec les changements futurs potentiels, je ne pense que que cela soit jouable. Après analyse, je pense que je dois passer par le VBA pour y arriver.

Donc j'apprends... C'est un défi, mais bon ...

Je suis conscient des problèmes de fusion (en VBA, cela cause manifestement qq problèmes avec les offset(x,x) ... Donc j'essaie de ne pas en abuser, mais parfois il n'y a pas de solution quand on veut un mise en page correcte.

Merci pour ton aide quand même.

eriiic a écrit :

Bonjour,

Function ConcatPlageCelNonVides(Plage_Source As Range, Optional séparateur As String = ", ") As String
    Dim rep As String, c As Range
    Dim datas, lig As Long, col As Long, s As String
    datas = Plage_Source.Value
    For lig = 1 To UBound(datas, 1)
        s = ""
        For col = 1 To UBound(datas, 2)
            s = s & IIf(datas(lig, col) = "", "", séparateur & datas(lig, col))
        Next col
        If s <> "" Then
            ConcatPlageCelNonVides = ConcatPlageCelNonVides & IIf(ConcatPlageCelNonVides = "", "", Chr(10)) & Mid(s, Len(séparateur) + 1)
        End If
    Next lig
End Function

Exemples simple en K5:K6, exemple pour recopie incrémentée en L5

Cocher 'renvoyer à la ligne automatiquement' dans le format de cellule.

Fonction dans Module1

eric

Ok : étant novice, je viens de découvrir cette possibilité de créer des fonctions nouvelles !!!

Pourrais tu m'expliquer en détail la ligne :

=ConcatPlageCelNonVides(DECALER('Progression 4eme'!$D$8:$E$11;;(LIGNE()-5)*2);" : ")

(j'ai remplacé le séparateur "-" par " : ". Est-il possible de le faire directement dans le code ? J'ai essayé, mais j'ai eu un message d'erreur.

DECALER, je connais mais la suite m'est opaque (xxxxx ;;(LIGNE()-5)*2); xx).

Par contre, cela change mon projet .. Peut être que tu peux me renseigner. Ce que je voudrais au final c'est :

Dans la feuille "planning 4eme". Si je tape en E12 "S002" alors, je veux que le contenu de la cellule L6 de la feuille "progressions"

se copie en F16 (par exemple) MAIS, MAIS je veux pouvoir éditer le texte ensuite librement et l'adapter... donc je ne peux pas passer par une fonction, une recherche horizontale ...

Aurais tu une idée ?

Merci

Pourrais tu m'expliquer en détail la ligne :

=ConcatPlageCelNonVides(DECALER('Progression 4eme'!$D$8:$E$11;;(LIGNE()-5)*2);" : ")

1er reflex sur une fonction que tu ne connais pas : l'aide excel

Ligne() (qui retourne le n° de ligne où est la fonction sert à calculer un offset en colonne de la référence $D$8:$E$11

Une ligne plus bas et la ref est décalée de 2 colonnes à droite.

C'est totalement indépendant du fait que tu utilises une fonction personnalisée. Tu aurais (presque) aussi bien pu écrire =MIN(DECALER('Progression 4eme'!$D$8:$E$11;;(LIGNE()-5)*2);" : ")

Est-il possible de le faire directement dans le code ?

Oui et non

Oui (autrement et pas dans une fonction) et non (c'est une fonction)

MAIS, MAIS je veux pouvoir éditer le texte ensuite librement et l'adapter...

En L6 ?

Si c'est ça je ne vois pas comment la mise à jour sur l'autre feuille en E12 pourrait se faire. C'est impossible de deviner ligne et colonne.

Quoique...

eric

eriiic a écrit :

1er reflex sur une fonction que tu ne connais pas : l'aide excel

Ligne() (qui retourne le n° de ligne où est la fonction sert à calculer un offset en colonne de la référence $D$8:$E$11

Une ligne plus bas et la ref est décalée de 2 colonnes à droite.

C'est totalement indépendant du fait que tu utilises une fonction personnalisée. Tu aurais (presque) aussi bien pu écrire =MIN(DECALER('Progression 4eme'!$D$8:$E$11;;(LIGNE()-5)*2);" : ")

J'ai a peu près compris ...mais si je comprends bien, dans ma feuille progression, si a une moment je décide d'insérer 1 ligne au dessus de mon tableau général, toutes mes formules sont fausses, puisque la ligne 12 devient ligne 13 .. et donc elle ira chercher la plage T8:U11. Cela ne me plait pas trop, car je ne connais pas la forme exacte de mon tableau final. En plus, on sera plusieurs à l'utiliser, donc je crains le pire ... C'est pour cela que les fonctions me font peur dans ce projet.

J'aurais plutôt vu une macro, qui se déclenche quand je fais un double clic sur la case ou il y a S002 (dans progression 4eme) et qui donc va mettre la "concatenation" des cellules là ou je veux ...

En fait, je ne sais même pas si cette feuille "Progressions" est utile.

Oui et non

Oui (autrement et pas dans une fonction) et non (c'est une fonction)

Si tu le dis, je te crois

En L6 ?

Si c'est ça je ne vois pas comment la mise à jour sur l'autre feuille en E12 pourrait se faire. C'est impossible de deviner ligne et colonne.

Quoique...eric

Non pas en L6.

En fait, mon objectif est le suivant : La feuille "progression 4eme" est une feuille où je prépare ma progression THEORIQUE mais non affinée.

La feuille "Planning 4eme", elle, doit décrire mes séances réelles, Non seulement pour la 4B, mais aussi pour la 4D qui sera à droite avec le même style de colonnes.

L'idée, c'est que, en E12 si je tape S002, excel va chercher :

*la concaténation de F5:G7 (de progression 4eme) pour la mettre en F16 (par exemple),

*la concaténation de F8:G11 (de progression 4eme) pour la mettre en F17 (par exemple),

*la concaténation de F12:G15 (de progression 4eme) pour la mettre en F18 (par exemple),

Et après, j'affine le texte ..., je change des détails, je rajoute des commentaires perso ... etc ... pour refléter le vrai contenu de ma scéance ...

En fait la feuille "Progressions" (avec un s) est quasiment indépendante ... elle peut même ne pas exister ..

Vois tu ?

Merci

RIPcagou a écrit :

jmd a écrit :

Bonjour à vous

je voulais dire que sans fusions de cellules, il faut se débrouiller autrement pour faire de jolies présentations. On y arrive toujours.

note bien que les fusions que tu fais te causeront des problèmes un jour ou l'autre, car n'oublie pas qu'un bon fichier excel va durer des années et que tu feras des modifications de temps en temps. Pas de fusions c'est une manière de se préparer à un avenir simple.

concaténer 2 ou 6 cellules, c'est pareil, non ?

s'il y a des cellules vides, c'est sans importance

pas de VBA (que je n'aime pas et qui ne m'aime pas non plus )

S'il y a 2 cellules vides sur une même ligne et que je programme un retour chariot, il y aura une ligne vide que je ne veux pas.

Le problème, c'est que potentiellement il y aura 1800 cases à remplir, avec une zone source qui risque de changer au cours du temps ...

J'ai déjà fait de telles formules mais avec de la recopie cela se faisait sans trop de problème. (le fichier fait 12 Mo). Mais là, avec les changements futurs potentiels, je ne pense que que cela soit jouable. Après analyse, je pense que je dois passer par le VBA pour y arriver.

Donc j'apprends... C'est un défi, mais bon ...

Je suis conscient des problèmes de fusion (en VBA, cela cause manifestement qq problèmes avec les offset(x,x) ... Donc j'essaie de ne pas en abuser, mais parfois il n'y a pas de solution quand on veut un mise en page correcte.

Merci pour ton aide quand même.

pour le fun, voici comment supprimer les lignes vides en concaténation voir fichier joint

si tu sais "tirer vers le bas" des formules, crée ton fichier avec une seule formule, enregistre le tirage vers le bas en macro

enregistre ton fichier avec la formule du haut et la macro : fichier hyper léger

tu joues la macro, le fichier est rempli mais tu ne l'enregistres jamais sous la forme remplie. Léger.

Bonjour,

mais si je comprends bien, dans ma feuille progression, si a une moment je décide d'insérer 1 ligne au dessus de mon tableau général, toutes mes formules sont fausses, puisque la ligne 12 devient ligne 13 ..

Tout à fait. C'était pour te montrer comment faire pour ne pas à avoir à saisir toutes les formules une par une dans une colonne mais tu peux toujours saisir toutes les formules une à une comme en K.

Et éventuellement tu peux remplacer le 5 dans la formule par son calcul : EQUIV("A.R";$E:$E;0)+1

Note quand même qu'en macro c'est pire. Elles supportent beaucoup encore bien une feuille qui n'est pas figée et c'est beaucoup plus difficile à modifier en cas d'évolution si ce n'est pas prévu à l'avance.

En fait la feuille "Progressions" (avec un s) est quasiment indépendante ... elle peut même ne pas exister ..

Vois tu ?

Je vois surtout que si elle n'existe pas ta demande n'a plus lieu d'être.

Mais bon, c'était un exercice intéressant...

La mise à jour se fait en temps réel, sans formules donc.

J'ai fait essayant d'être assez souple au niveau restrictions. En théorie la macro doit s'adapter à qq changements dans les feuilles mais vu que c'est assez complexe c'est sans garantie totale, si tu pouvais t'abstenir....

Régles à respecter pour qu'elle s'y retrouve, sinon adapter le code :

- noms des feuilles concernées :

La partie gauche est "Progression ", la partie droite ("4ème") ne doit pas avoir d'espace, et se retrouver dans Progressions"!3:3 pour retrouver la colonne de la classe

- feuilles 'Progression xxxx'

Sélectionner toutes les cellules (D5:S15, D19:S29, etc) entrainant une mise à jour et nommer la plage Prog

En colonne 1 de la cellule modifiée, dans la plage fusionnée, je dois trouver une des 4 rubriques (Cours, ...) toujours orthographiée pareil.

En remontant à partir de la cellule modifiée je dois trouver une plage fusionnée avec "Sxxx". Ce Sxxx doit se retrouver dans Progressions"!D:D

Je te laisse tester. Si tu veux faire apparaitre la concaténation dans Progressions, revalide une cellule dans 'Progression 4ème'. J'en juste fait 2 pour tester.

eric

Merci

J'aime beaucoup ce que tu as fait ...

En fait j'étais partie dans l'idée de cette feuille progressions comme étant une feuille de transition, dans laquelle j'aurais été cherché le fruit des concaténations pour les reporter dans les cellules de la feuille "Planning 4eme"

Mais penses tu que mon véritable objectif soit programmable.

En fait, mon objectif est le suivant : La feuille "progression 4eme" est une feuille où je prépare ma progression THEORIQUE mais non affinée.

La feuille "Planning 4eme", elle, doit décrire mes séances réelles, Non seulement pour la 4B, mais aussi pour la 4D qui sera à droite avec le même style de colonnes.

L'idée, c'est que, en E12 si je tape S002, excel va chercher :

*la concaténation de F5:G7 (de progression 4eme) pour la mettre en F16 (par exemple),

*la concaténation de F8:G11 (de progression 4eme) pour la mettre en F17 (par exemple),

*la concaténation de F12:G15 (de progression 4eme) pour la mettre en F18 (par exemple),

Et après, j'affine le texte ..., je change des détails, je rajoute des commentaires perso ... etc ... pour refléter le vrai contenu de ma scéance ...

Merci

Ca aurait été mieux que tu expliques ton but final dès le début.

Je ne vais pas tout refaire, tu masqueras Progressions...

Par formule. J'ai dû ajouter 4eme en B1. Tu peux copier-coller F7:F9 où tu veux sur la feuille du moment qu'il y a Sxxx 4 lignes au-dessus et 1 colonne à gauche.

eric

eriiic a écrit :

Ca aurait été mieux que tu expliques ton but final dès le début.

Je ne vais pas tout refaire, tu masqueras Progressions...

Je l'avais expliqué dans mon mail de hier, à la fin (tout à l'heure, je n'ai fait qu'un copier coller).

eriiic a écrit :

Par formule. J'ai dû ajouter 4eme en B1. Tu peux copier-coller F7:F9 où tu veux sur la feuille du moment qu'il y a Sxxx 4 lignes au-dessus et 1 colonne à gauche.

eric

En fait, c'est vraiment dans la feuille Planning 4eme que je dois éditer librement les champs... Je vais donc essayer de comprendre ton code pour l'adapter ....

Merci

ok.

Garde plutôt l'existant et sert-toi en pour une nouvelle macro dans Planning 4eme.

eric

Bonjour,

Regarde si c'est ça que tu voulais.

J'avoue que je commence à m'y perdre dans tes explications.

La partie droite du nom d'onglet doit être la classe (4eme)

eric

eriiic a écrit :

Bonjour,

Regarde si c'est ça que tu voulais.

J'avoue que je commence à m'y perdre dans tes explications.

La partie droite du nom d'onglet doit être la classe (4eme)

eric

C'est génial ... je regarde le match, tu bosses pour moi, je vais me coucher, et le matin, le travail s'est fait tout seul ... C'est dommage que je ne sache pas ou tu habites, sinon, je t'enverrais des chocolats

Mais bon, pour l'instant, il faut que je l'adapte ... parce que j'aimerais bien au final, me passer complètement de la feuille progressions.

En fait, il faut que j'arrive à ce que la feuille "Planning 4eme" aille chercher directement les infos dans "Progression 4eme".

Ca après il faurdra que la feuille "Planning 5eme" aille chercher directement les infos dans "Progression 5eme" et ainsi de suite

Bonjour Eriiic

Je te remercie pour le temps que tu as passé.

Comme j'ai complètement repensé ma feuille "Planning 4eme", j'ai été obligé de me plonger dans ton code et je t'avoue qu'étant néophyte, je n'ai pas tout compris ...

Je me rends compte aussi que je vais devoir rechanger les noms des feuilles pour les raccourcir, mais je ne sais pas comment encore.

Donc j'ai un peu retravailler le tout à ma sauce et j'ai mis le code dans chacune des feuilles (c'est plus simple pour moi). Certes, il n'est pas aussi poussé que le tien, mais il me faut des choses simples pour éventuellement y apporter des modifications quand je vais l'utiliser concrètement.

Pourrais tu s'il te plait regarder ce que j'ai fait (feuille Planning 4eme) et me dire si globalement çà le fait et si je n'ai pas oublié des choses ...

Merci

Bonjour,

Je ne vois aucun code dans les feuilles ou dans thisworkbook.

Donc rien à voir avec ce que j'avais fait et qui fonctionnait.

Quand on travaille en vba on réfléchit avant et seulement après on commence à coder.

Tu sais quoi : tu testes et tu corriges jusqu'à ce que tu aies ce que tu veux.

eric

Hello

Le code est dans les feuilles Planning 4eme et Planning 3eme ...

Je sais que l'on doit réfléchir avant et coder après.

Mais le problème c'est que d'une part, je ne sais pas encore précisément la structure de mes feuilles ... et que je ne sais pas ce qui possible de faire en codage.

A la base, mon projet est beaucoup plus grand, et ne dépends pas d'excel ... Mais comme je ne connais aucun langage et que je n'ai pas 20000 heures à y passer, des copains programmateur m'ont conseillé de le faire avec Excel (car cela s'y prête) et éventuellement, une fois que j'aurais bien défini on projet, je pourrais peut-être aller plus loin ...

De plus, on sera plusieurs à utiliser ce fichier. Ce que moi je nomme Chapitres 4eme, ma collègue voudra peut-être l'appeler CH4eme. Donc je dois aussi en tenir compte ...

Bref, ce qui est sûr, c'est que cela me plait ... et que ce forum est une mine d'or. Il y a plein de passionnés comme toi, pour aider les néophytes comme moi, et c'est cool ...

Merci pour tout ...