XL 2021 Aléa entre borne et ne pas prendre en compte cellule vide

[candido57]

Bonjour,
je voudrais dans ma formule aléa entre borne qu'il ne prenne pas les cellule vide. Dans mon fichier feuille "Personne" colonne C, il prend en compte les cellule vides B14 à B16. et ne prend pas en compte les 3 derniers noms.
DE B13 à B16 je garde ces cases pour coller les valeurs éventuelles de N11 à N14.
Donc je souhaite que l'on prenne en compte de B7 à B13 et de B17 à B19.
En final, je en veux pas de 0 dans la colonne D et avoir tous les prénoms de la colonne C.
Si une personne est absente, je la retire de la liste et je la colle sous le tableau à partir de B27 pour qu'il ne prenne pas en compte les absent et lorsqu'ils reviennent , je les remets à partir de B17.
Je ne sais pas si c'est compréhensible. Peut-être avec le fichier ça sera mieux.

Merci pour votre aide

 

[piga25]

Bonjour,

Une possibilité,
Créer la liste complète sans les cellules vides (par exemple dans la même colonne en B50:B68) avec ce code :

Sub SansVide()
    With Sheets("Personne")
    Range("B7:B25").Copy
    ActiveWindow.SmallScroll Down:=38
    Range("B50").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("B50").Select
    End With
End Sub

Ensuite modifier votre formule ALEA.ENTRE.BORNES

=INDEX($B$50:$B$68;ALEA.ENTRE.BORNES(1;NBVAL($B$50:$B$68)))

Et la recopier vers le bas.

Voir fichier
Juste réalisé pour la première série de noms : Mardi matin (TOUS).

 

[candido57]

Bonjour,

Une possibilité,
Créer la liste complète sans les cellules vides (par exemple dans la même colonne en B50:B68) avec ce code :

Sub SansVide()
    With Sheets("Personne")
    Range("B7:B25").Copy
    ActiveWindow.SmallScroll Down:=38
    Range("B50").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("B50").Select
    End With
End Sub

Ensuite modifier votre formule ALEA.ENTRE.BORNES

=INDEX($B$50:$B$68;ALEA.ENTRE.BORNES(1;NBVAL($B$50:$B$68)))

Et la recopier vers le bas.

Voir fichier
Juste réalisé pour la première série de noms : Mardi matin (TOUS).

Bonjour,
Déjà un grand merci pour ton aide. J'ai essayé, mais lorsque tu modifie la liste en enlevant les absents et rajoutant les présents , ça ne fonctionne pas.
Dans ma macro, je copie de N13 à N18 et je colle à la suite ( En rouge) . J'aimerais coller les 4 cellules dans la colonnes B à la fin de la liste, la première ligne vide. Il faudrait rajouter quelque chose dans mon code.

Merci


'Coller les valeurs de N13 à N16

Sheets("Personne").Select

Range("N13:N16").Select

Selection.Copy

Range("G17:G20").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("B15:B18").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

 

[piga25]

Bonjour,
Je ne comprends pas trop.
Au départ vous vouliez qu'il n'y ai pas de 0 dans la liste colonne D.
Une solution dans le post #2 réalise cela, pour ce faire il faut créer une liste sans cellule vide, ce qui est fait en colonne B (B50 à B68). Donc à partir de là votre formule ALEA.ENTRE.BORNE fonctionne.
Cette liste B50 à B68 se réalise par rapport aux données situées en B7:B25 (liste pouvant avoir des cellules vides.

Ce qu'il faudrait connaître au départ c'est qu'elle liste prendre, vous parlé de N13 à N18 mais là il n'y a rien à copier!!!!!
Vous coller cela (rien) en G127:G20 puis la même chose (rien) en B15:B18

Vous pouvez mettre un fichier avec ce que vous attendez.

 

[candido57]

Bonjour,
Je ne comprends pas trop.
Au départ vous vouliez qu'il n'y ai pas de 0 dans la liste colonne D.
Une solution dans le post #2 réalise cela, pour ce faire il faut créer une liste sans cellule vide, ce qui est fait en colonne B (B50 à B68). Donc à partir de là votre formule ALEA.ENTRE.BORNE fonctionne.
Cette liste B50 à B68 se réalise par rapport aux données situées en B7:B25 (liste pouvant avoir des cellules vides.

Ce qu'il faudrait connaître au départ c'est qu'elle liste prendre, vous parlé de N13 à N18 mais là il n'y a rien à copier!!!!!
Vous coller cela (rien) en G127:G20 puis la même chose (rien) en B15:B18

Vous pouvez mettre un fichier avec ce que vous attendez.

Bonjour,

A la base mon fichier fonctionnait, tant que la liste de B7 à B25 se suivait, mais dès que j'avais un vide la formule renvoyait un 0. Dans la colonne N à partir de la cellule 11, je veux coller les valeurs de N11 à N14 car ces personnes s'intègrent dans la liste de B7 à B25 donc je m'avait réservé de B13 à B16.
Comme la liste en colonne N est aléatoire, ce n'est pas les même personnes qui sont coller.
Comme j'ai besoin que 4 personnes pour l'administratif, le reste vont dans la liste à partir de B13 à B16 et aussi dans les cellules G15 à G18. J'ai garder 4 cellules (en rouge) car je peux rajouter des personnes dans liste administratif. C'est ça sur ma dernière demande, si je rajoute des prénoms dans la liste de B7 à B25, je voulais coller les 4 cellules de la colonne N à partir de la première cellule vide.
Exemple j'ai rajouté 4 prénoms de B13 à B16 , je souhaite coller ma sélection à partir de B17

 

[piga25]

RE
Maintenant il n'y à plus de vide car B50:B68 remplace B7:B25 donc cela devrait fonctionner.
Dans toutes vos formules ALEA..... situées en colonnes C, H, M et R il n'y a plus aucun 0

 

[candido57]

Bonjour,
Déjà un grand merci pour ton aide. J'ai essayé, mais lorsque tu modifie la liste en enlevant les absents et rajoutant les présents , ça ne fonctionne pas.
Dans ma macro, je copie de N13 à N18 et je colle à la suite ( En rouge) . J'aimerais coller les 4 cellules dans la colonnes B à la fin de la liste, la première ligne vide. Il faudrait rajouter quelque chose dans mon code.

Merci


'Coller les valeurs de N13 à N16

Sheets("Personne").Select

Range("N13:N16").Select

Selection.Copy

Range("G17:G20").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("B15:B18").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Bonjour,
J'ai repris le fichier que vous m'avez proposé en #2. Ca fonctionne , j'avoue j'ai fait n'importe quoi au début, j'avais modifier des chose que je n'aurais pas du. Donc je les pris dès le début, mais lorsque je change ou rajoute des nom à partir de la cellule B17, la liste se modifie correctement à partir de B50 mais la formule en C7 se modifie, elle ne reste pas en B50:B68 >> B68 se modifie. Cela le fait lorsque j'ai rajouter ta macro en dessous après coller les valeurs N11:N15. pour la faire fonctionner avec mon bouton. Si je laisse ta macro en module 4 , elle ne se lance pas si j'appuis sur mon bouton qui est sur la feuille Planning.
Il faudrait que cette formule reste Figée =INDEX($B$50:$B$68;ALEA.ENTRE.BORNES(1;NBVAL($B$50:$B$68))) elle se modifie toute seule.
Elle se modifie par exemple comme ça =INDEX($B$50:$B$56;ALEA.ENTRE.BORNES(1;NBVAL($B$50:$B$56)))
Merci beaucoup
Bonne journée

 

[candido57]

Bonjour,

A la base mon fichier fonctionnait, tant que la liste de B7 à B25 se suivait, mais dès que j'avais un vide la formule renvoyait un 0. Dans la colonne N à partir de la cellule 11, je veux coller les valeurs de N11 à N14 car ces personnes s'intègrent dans la liste de B7 à B25 donc je m'avait réservé de B13 à B16.
Comme la liste en colonne N est aléatoire, ce n'est pas les même personnes qui sont coller.
Comme j'ai besoin que 4 personnes pour l'administratif, le reste vont dans la liste à partir de B13 à B16 et aussi dans les cellules G15 à G18. J'ai garder 4 cellules (en rouge) car je peux rajouter des personnes dans liste administratif. C'est ça sur ma dernière demande, si je rajoute des prénoms dans la liste de B7 à B25, je voulais coller les 4 cellules de la colonne N à partir de la première cellule vide.
Exemple j'ai rajouté 4 prénoms de B13 à B16 , je souhaite coller ma sélection à partir de B17

Je pense que ça vient de cette formule =INDEX($B$50:$B$68;ALEA.ENTRE.BORNES(1;NBVAL($B$50:$B$68;"")))
$B$50:$B$68 ça change suivant la longueur de la liste. Si la liste s'arrêté à B56 la formule change, mais je modifie la liste plus longue , elle reste à B56 , elle ne prend pas en compte jusqu'à la dernière valeur.

 

[piga25]

Bonjour,

Qu'elle est la liste qui est modifiée?
Celle située en B7:B25 ou celle qui correspond au résultat B50:B68

 

[candido57]

Bonjour,

Qu'elle est la liste qui est modifiée?
Celle située en B7:B25 ou celle qui correspond au résultat B50:B68

Lorsque je modifie la liste B7:B25 (je rajoute ou enlève un prénom), la liste en B50:B68 se modifie correctement, mais la formule alea entre borne se modifie malgré que les cellule sont figées

 

[piga25]

Bonjour,
Petite correction sur le fichier.
J'ai supprimé la macro qui recopiait la liste sans les cellule vide (c'est elle qui influençait sur la formule).
Elle a été remplacé par une formule matricielle qui fait exactement la même chose.
Cette liste ainsi réalisée (B50:B68) est devenue une liste dynamique nommée "Liste"
Normalement tout fonctionne même en ajoutant ou en enlevant des noms.

 

[candido57]

Bonjour,
Petite correction sur le fichier.
J'ai supprimé la macro qui recopiait la liste sans les cellule vide (c'est elle qui influençait sur la formule).
Elle a été remplacé par une formule matricielle qui fait exactement la même chose.
Cette liste ainsi réalisée (B50:B68) est devenue une liste dynamique nommée "Liste"
Normalement tout fonctionne même en ajoutant ou en enlevant des noms.

Oui un grand merci, ça fonctionne bien, mais en attendant j'ai cherché de mon coté et j'ai aussi trouvé une solution, regarde mon fichier.
En tous je garde aussi ta modification.
Merci beaucoup pour ton aide , c'était super sympa.

Bonne soirée