XL 2013 Supprimer des sauts de ligne vierge d'une cellule qui contient une formule

[ANTONY34200]

Bonjour,
j'ai créé un planning sous excel, pour ma femme qui reprend une PME en sécurité privée, ou je pense avoir déjà bien bossé dessus.
Je ne suis pas un expert, mais j'ai créé ce planning en consultant pas mal de forum. Le planning est fonctionnel, MAIS, car il y a un mais, j'aimerais y apporter quelques améliorations.
Le fichier joint, comporte 3 onglets (paramètre, général et agt 5)

L'onglet général, est en fait, le planning général, et c'est la que toutes les informations sont saisies.

L'onglet AGT 5, est le planning individuel de l'agent. Des cellules D8 à D39 ont une formule qui appelle le nom du site SI l'agent est planifié dans le planning général, pareil pour les heures de début (F8 à F39) et les heures de fin (G8 à G39).

Mon soucis est que j'aimerais, dans les cellules D8 à D39, F8 à F39, et G8 à G39, quand l'agent est planifié, les sauts de lignes inutiles ou vierges (dans les cellules)soient supprimées, sinon les cellules sont bien trop grande.

J'aimerais aussi savoir s'il est possible de classer par ordre chronologique d'heure de début (dans la ligne) et que les sites et les horaires correspondent.

Je ne suis pas expert, mais je pense toucher un peu, mais la... je bloque. j'ai cherché un peu partout une piste, mais je n'ai rien trouvé.
Si quelqu'un pouvait m'aider un peu ... se serai avec plaisir.
A bientôt, et merci d'avance.

 

[sylvanu]

Bonsoir Anthony,
Ca, c'est de la formule ! 33 lignes pour une formule. Je pense que le VBA aurait été plus simple.

Pour le point 1 :
Le saut de lignes est dû aux &CAR(10)&" " qui sont présent à chaque fois quelque soit le résultat Sierreur.
Il faut l'intégrer après la condition SI vrai, comme ça il n'y a un saut qu'à chaque condition remplie. ( voir PJ )
On remplace :

=SUPPRESPACE(SIERREUR(SI(CHERCHE($D$5;GENERAL!E7);GENERAL!$E$4;"");"")
&CAR(10)&" "&SIERREUR(SI(CHERCHE($D$5;GENERAL!J7);GENERAL!$J$4;"");"")
....

par

=SUPPRESPACE(SIERREUR(SI(CHERCHE($D$5;GENERAL!E7);GENERAL!$E$4&CAR(10)&" ";"");"")
&SIERREUR(SI(CHERCHE($D$5;GENERAL!J7);GENERAL!$J$4&CAR(10)&" ";"");"")
....

Pour le point 2, je ne vois pas à part passer par du VBA.

 

[ANTONY34200]

Bonjour Sylvanu et merci pour ta réponse.
Je sais tres bien qu'une formule à 33 lignes, c'est lourd, mais j'ai fais comme j'ai pu.
Le VBA, je ne connais pas et je t’avouerais que ça me fait un peu peur ... mais si on m'explique comment faire, je suis preneur ...
J'ai testé le fichier que tu m'as renvoyé, c'est presque ce que je souhaitais. Là, si je ne renseigne pas le dernier site du planning général, ma cellule du planning de l'agent reste toujours aussi grande ...
Si je passe en VBA, mon fichier serai-t-il moins lourd ?? et pourrais m'aider a comprendre la VBA.

 

[sylvanu]

Re,
Je n'ai modifié que la cellule D9.
Si vous modifiez les cellules F9 et G9, cela devrait fonctionner; La cellule reste haute car quelque part il y maints Car(10).
Par exemple si dans mon fichier vous supprimez les formules en F9 G9 et réduisez en automatique la ligne 9 alors elle se réduit bien.
Pour le VBA il y a moult sites pour s'y mettre. mais là en partant de 0 la marche serait haute.
Sur XLD il y a des tutos pour le VBA : https://www.excel-downloads.com/media/categories/vba.4/

 

[ANTONY34200]

En fait, je viens de tester, dans la dernier ligne de la formule a la place de car(10), j'ai mis car(13) et ça me réduit bien mes cellules ...
Je vous joint mon fichier pour que vous puissiez voir ...

 

[sylvanu]

Just for the fun.
Il y a un léger décalage sur F9 entre les affichage d'heures. C'est inesthétique.
En supprimant Supprespace et en remplaçant CAR(10)&" " par seulement CAR(10) et le dernier CAR(13) par CAR(10) on supprime le défaut.

 

[ANTONY34200]

c'est vraiment super !! je vous remercie grandement ...
concernant l'ordre chronologique, pourriez vous me mettre sur la piste avec le VBA

 

[sylvanu]

Just for the fun !
La formule en D9 a été transcrite en VBA avec une fonction perso que j'ai mis en E9 pour essai. La syntaxe est : =Antony($D$5;1)

Function Antony(N, Out)
' Out servira plus tard pour le renvoie de un résultat parmi plusieurs.
Nom = LCase(N)          ' Le nom tout en minuscules pour éviter erreurs comparaison.
Chaine = ""             ' Init chaine de sortie
For i = 1 To 33         ' Pour les 33 cellules
    If LCase(Sheets("GENERAL").Cells(7, 5 * i)) = Nom Then              ' Si la cellule contient le nom
        Chaine = Chaine & Sheets("GENERAL").Cells(4, 5 * i) & Chr(10)   ' On ajoute le site
    End If
Next i
Antony = Chaine         ' On renvoie le résultat
End Function

C'est un peu plus simple comme ça non ?
Après ça va se compliquer;
Pour FGH ça sera du même accabit, par contre pour trier il va falloir synchroniser les 3 formules en DFG pour que tout tombe bien l'un devant l'autre.
( Pour ouvrir l'éditeur VBA, faire ALT + F11 )

 

[ANTONY34200]

En gros, maintenant il faut que j'ouvre l'éditeur VBA et que je fasse cette formule pour toutes les cases ??

 

[sylvanu]

Non, ce n'est pas si simple.
Ça le serait si vous ne vouliez pas l' ordre chronologique car dans ce cas les formules seraient indépendantes.
Avec un tri par heure, il faut que tous les résultats soient liées de façon que toujours Site1 Debut1 et Fin1 soient en face.
Je regarde, et vous fait signe quand j'ai trouvé.

 

[ANTONY34200]

c'est super sympa. Merci

 

[sylvanu]

Un essai en PJ. Mais comme dit, ça s'est un peu complexifié.
je n'ai modifié que les cellules DFG 9 et 10.
Juste pour voir. Commentaires et syntaxe dans la fonction.

 

[ANTONY34200]

Merci, c'est bien ça que je voulais. Effectivement, les codes VBA semble complexe.
pourquoi les formules dans les cellules D9 et D10 sont identique ?? idem pour F9 et F10, et G9 et G10 ... je ne me l'explique pas ...
jevais essayer de faire les cellules D11, F11, et G11 voir si j'ai compris le systeme.
Maintenant, si je dois créer un nouvel AGT6 en dupliquant l'onglet, est ce que ca fonctionnera ?? ou je devrais tout refaire le code VBA ??

 

[ANTONY34200]

sans vouloir abuser, pourriez vous faire la ligne 11 et me noter ou vous l'avez rajoute pour le site, les heurs de début et les heures de fin dans l'éditeur VBA

 

[ANTONY34200]

J'ai compris ... j'ai fais l'intégralité du planning général ...