Somme suivant la couleur de fond et un numéro de repère

Bonjour,

Je sais que ce sujet est souvent débattu mais je n'ai pas trouvé de solution pour mon cas:

J'ai un tableau représentant un planning avec des personnels appartenant a différent repère (col A) ( repère allant de 1 à 19 dans l' exemple).

je cherche a afficher automatiquement le nombre de personne disponible pour chaque jour par repère.( a la fin des colonnes)

ceux ci sont représentés par des cases sans couleur (en semaine) et grisé en WEND dans le planning.

j'ai deux types de calcul à faire:, un pour prendre en compte les filtres activer et l'autre pour un global (même si filtre activé)

Pouvez vous m'aider, je sèche depuis un mois

si joint un fichier simplifier :

les couleurs dans le planning sont mises grâce a des fonction vba.

je vous remercie .

Bonjour JMFF et

Regarde en bas de la page, si tu ne trouves pas ton bonheur

Bonjour,

comme je vois que tu manipules bien les formules, je te propose juste de combiner avec la formule =Couleur(___) que j'ai mise en indiquée en Feuille2

nota : je ne connais pas la version XL2017, et c'est d'autant plus paradoxal que ton fichier est un xls !

Bonjour jmff,

Salut Bruno , Steelson (décidément, il y en a qu'on croise souvent ces temps-ci!)

Un essai avec une fonction personnalisée; la même pour les deux tableaux de résultats (le dernier argument précise s'il faut prendre en compte -ou non- les lignes visibles uniquement)

Attention tout de même à ne pas augmenter le volume à traiter, de manière déraisonnable

U. Milité a écrit :

fonction personnalisée

Bonjour tout le monde,

Merci pour cette réactivité

Cela s'approche énormément de ce que je veux néanmoins j'ai quelque petites question et problème.

Si j'ai a peu près compris le code , cela veux dire ça ?

Function nbPrésents(plage As Range, repère As Range, filtreActivé As Boolean)

' Désactivation de la mise à jour de l'écran :

Application.ScreenUpdating = False

'Coupe le texte d'une cellule en plusieur lorsque le mot " " est présent. chaque éléments coupsé est numéroté de 0 à ...

tabRep = Split(repère, " ")

For Each c In plage

'selection de la première cellule, de la première ligne pour comparer (repère)

Select Case Cells(c.Row, 1)

'ce repère corespond au repère de référence coupé et est convertis en nombre entier( ex:1 correspond à "de 1 à 3")

Case CInt(tabRep(1)) To CInt(tabRep(3))

' si la couleur de la cellule est vide , wend ou jjour férié alors

If c.Interior.ColorIndex = -4142 Or c.Interior.ColorIndex = 37 Or c.Interior.ColorIndex = 15 Then

' si filtre activé est à faux

If c.EntireRow.Hidden = False Then

'ajouter 1 au compteur des filtré

cptVis = cptVis + 1

' sinon 1 au compteur global

Else

cpttot = cpttot + 1

End If

End If

End Select

Next c

' resultat suivant filtre ou pas

nbPrésents = IIf(filtreActivé, cptVis, cpttot + cptVis)

' Réactivation de de la mise à jour de l'écran

Application.ScreenUpdating = True

End Function

Mais du coup j'ai 2 problèmes

si je ne modifie qu'une seule case, il n'y a pas de mis à jour du résultat ( il faut en faire minimum 2)

et si j'utilise des mises en forme via VBA cela me met une erreur #valeur! je suppose du coup que j'ai un mauvais format dans ma facon de mettre la couleur ?

Merci beaucoup de votre aide j'avoue c'est dure de débuter en VBA

Si cela peut être utile, je vous un exemple de remplissage des couleurs.

Excuser moi de la façon de programmer il y a sans doute plus simple.

[quote][/ 'Désactive la protection MDP ADU

ActiveSheet.Unprotect (MDP)

'Autoriser la selection d'une ligne uniquement

If Selection.Rows.Count <> 1 Then

MsgBox ("Selection de plusieurs lignes interdite")

Exit Sub

Else

'Saisi du motif

Dim Motif As String

Motif = InputBox("Saisir la position du personnel", "Stage", "Type de stage")

''Test si le motif n'est pas vide et activation croix

If Motif = "" Then

MsgBox ("Mise à jour annulée ou Position du personnel non-remplit")

Exit Sub

Else

' Désactivation de la mise à jour de l'écran :

Application.ScreenUpdating = False

' effacement des cellule de la selection

Selection.Clear

'mise en page forme cellule

Selection.Font.Bold = False

Selection.Font.Size = 8

Selection.Borders(xlEdgeTop).Weight = xlThin 'haut

Selection.Borders(xlEdgeBottom).Weight = xlThin 'bas

Selection.Borders(xlEdgeLeft).Weight = xlThin 'gauche

Selection.Borders(xlEdgeRight).Weight = xlThin 'droite

Selection.Borders(xlInsideVertical).LineStyle = xlNone 'intérieur

'mise en page texte cellule

Selection.HorizontalAlignment = xlCenter

Selection.VerticalAlignment = xlCenter

Selection.ReadingOrder = xlContext

'colorisation de la case

With Selection.Interior

.ColorIndex = 45

.Pattern = xlSolid

End With

'Dévérouiller case

Selection.Locked = False

'insertion du motif au bon endroit

Dim Nombre As Integer

Dim Nombrepair As Integer

''Compte de nombre de cases selectionnées

Nombre = Selection.Cells.Count

'' Detecte si la selection est paire

Dim Pair As Integer

Dim lngTemp As Long

lngTemp = CLng(Right(CStr(Nombre), 1))

Pair = (lngTemp And 1) = 0

'' choisie le centre de la selection pour ecrire

If Pair = -1 Then

Nombrepair = Nombre / 2

Selection.Cells(1, Nombrepair) = Motif

Else

Nombrepair = ((Nombre - 1) / 2) + 1

Selection.Cells(1, Nombrepair) = Motif

End If

' Réactivation de de la mise à jour de l'écran

Application.ScreenUpdating = True

End If

End If

'Active la protection MDP ADU

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingHyperlinks:=True, AllowSorting:=True, AllowFiltering:=True, Password:=MDP

End Sub][quote]

Au temps pour moi, cela fonctionne en fait avec mes mise en forme par vba mais c'est la mise à jour qui ne se fait que lorsqu'il y a un rafraichissement.

j'ai peur que la formule soit gourmande aussi non?

merci beaucoup en tout cas

Bonjour,

Je ne sais pas pourquoi tu as ajouté un Application.ScreenUpdating = False dans le code d'une fonction

Ce que ton propre code ne dit pas, c'est ce qui déclenche l'exécution dudit code (une procédure événementielle ? Si oui, laquelle ?)

Il aurait mieux valu laisser la macro que tu as écrite dans le fichier que tu as déposé: on y aurait vu plus clair.

Sans doute qu'en utilisant un alignement horizontal "centré sur plusieurs colonnes", tu te serais facilité le travail de mise en forme de tes "Motifs" (centrer le texte dans la sélection, et bordures intérieures non visibles)

Je ne savais pas que cela ne marchais pas pour une fonction, j'essayais de faire accélérer le calcul car cela est très lent.

si tu as une solution

pour le déclenchement effectivement c'est juste par l'appuis sur un bouton.

Merci pour la mise en forme, je prend note et je vais tenter de corriger cela.

Je voulais pas vous encombrer de toute mes informations dont certaine sont un peu privé mais si besoin je peu le donner complet

Re,

Je ne savais pas que cela ne marchais pas pour une fonction

j'essayais de faire accélérer le calcul car cela est très lent. Si tu as une solution

Ça "en jette" sans doute un peu moins ... à toi de voir où tu mets tes priorités

U. Milité a écrit :

Ça "en jette" sans doute un peu moins ... à toi de voir où tu mets tes priorités

pas forcément si la MFC donne la valeur de police = valeur de fond (éventuellement sur les cellules sauf la première, mais un code pas un mot complet !

Salut Steelson

Quand je disais ça, je pensais surtout à utiliser les fonctionnalités natives, plutôt qu'utiliser des "boutons magiques".

Ceci dit, il n'en reste pas moins qu'avec tous les calculs à réaliser (selon les fameux "repères", en comptant distinctement selon que la liste soit filtrée ou non, etc) le temps de calcul risque d'être impacté, j'en ai peur !

Bonjour,

Au cas où la chose serait encore utile, j'ai changé d'approche, pour gagner en temps de calcul:

• j'ai ajouté un bouton (puisque c'est ce que tu utilisais précédemment) il déclenche le calcul dans les deux tableaux, mais tu peux faire en sorte qu'avant les calculs, certaines de tes manips se fassent également. Attention si on change les valeurs de la plage F15:AJ140, sans cliquer sur le bouton, le calcul n'est pas mis à jour
• je n'inscris, comme évoqué hier, qu'un code ('V' ou 'D') dans les cellules, mais je l'inscris pour l'ensemble des cellules de la plage concernée (on s'évite ainsi de tenir compte des couleurs de remplissage).
• une Mise en Forme Conditionnelle pour les jours de w-e, une autre pour les fériés (voir plage nommée -à compléter- en Feuil2)
• des MFC également liées aux différents codes
• si le clic sur le bouton met à jour les calculs, l'activation d'un filtre, quant à elle ne provoque pas de re-calcul ... je n'ai pas trouvé d'événement lié à l'activation des filtres ... j'ai donc "triché" (je l'avoue honteusement ) en vérifiant, à chaque recalcul de la feuille, si le nombre de lignes visibles se modifiait (je stocke l'info en AM1). Si oui, j'exécute la procédure.
• il reste encore des choses à régler ou modifier ... voyons si tu reviens dire ce que tu en penses!

PS: je ne suis pas un chaud partisan (comme bien d'autres) des cellules fusionnées ... mais je le suis moins encore pour ce qui est des titres d'une liste de données

Bonjour,

Tout d'abord merci de continuer a chercher une solution ,

En fait le problème de remplir quelque chose d'identique dans chaque cellule est que du coup on ne peut afficher une phrase dans un ensemble de cellule comme je le faisais précédemment elle sera forcément coupé a cause de la cellule d'a coté .

J'ai malheureusement besoin de lire le texte en entier

En fait si je pouvait mettre dans la première cellule une phrase et que dans les suivantes une lettre ou même la même phrase mais que seule la première case s'affiche en entier au centre de toute les cases sélectionner, je n'aurais pas de problème car après on peut utiliser les formule natives d'Excel.

Donc si par hasard cela est possible je prend la façon de faire car j'ai cherché très longtemps avant d'en venir au couleur.

merci encore

U. Milité a écrit :

PS: je ne suis pas un chaud partisan (comme bien d'autres) des cellules fusionnées ...

moi non plus, mais on peut aussi utiliser le centrage sur plusieurs colonnes

Bonjour,

Salut Steelson

Steelson a écrit :

on peut aussi utiliser le centrage sur plusieurs colonnes

@jmff: en annexe, j'ai fait ce que tu demandes ... mais on a de nouveau un délai d'attente au moment du recalcul (moins long qu'avec la fonction, mais il y en a un !)

Tu appelleras la procédure "nbPrésents" depuis ta propre Sub qui inscrit et met en forme les "Motifs".

N'oublie pas de conserver ce que j'ai ajouté dans le module de la Feuil1, ainsi que le contenu de la cellule AM1, si tu veux que le calcul se mette à jour, au moment où tu modifies les critères de filtre !

Je pense que je me suis mal exprimer ,

si tu arrive a mettre un texte dans chaque cellule mais qu'apparaisse qu'un seul de ces textes centré sur toute les colonnes sélectionner , le calcul je sais le faire après.

En fait comme si c'était fusionné et centré mais en réalité il y a quelque chose d'écrit dans chaque cellule.

( ou sinon j'ai pas compris tes derniers changement )

jmff a écrit :

si tu arrive a mettre un texte dans chaque cellule mais qu'apparaisse qu'un seul de ces textes centré sur toute les colonnes sélectionner

Re, encore

jmff a écrit :

( ou sinon j'ai pas compris tes derniers changement )

Teste ma dernière proposition en n'appliquant qu'une couleur de remplissage à une plage (comme tu le faisais dans ton fichier initial, si j'ai bien compris), sans ajouter de texte dans un premier temps, puis clique sur le bouton et vérifie si les résultats sont conformes.

Si oui, on verra ensuite comment afficher du texte "à cheval" sur ces mêmes cellules