XL 2021 Tentatives de modifier aléatoirement l'ordre dans une colonne de cellules, le nombre de cellules concernées pouvant varier.

[MATLEA]

Bonjour chers experts,

J'ai une question concernant l'élimination des cellules vides dans une colonne de données après application d'une formule de randomisation mais avec un nombre d'entrées pouvant varier. Voir fichier essai joint.

Merci encore pour vos conseils et solutions

 

[sylvanu]

Bonjour à tous deux,
@Dranreb,
Je pense que votre solution VBA du post #8 est préférable. 🙂
Imaginons une liste en TDS de 100 mots. On veut l'updater par une nouvelle liste de 50 mots.
Si l'utilisateur l'efface simplement et remet sa nouvelle liste, alors se baser sur la profondeur du TDS ne marchera pas il sera toujours de 100.
Modifier le VBA posera moins de soucis à Matlea qui ne semble pas maitriser les TDS. ( sans vous offusquer Matlea 😉)

 

[Dranreb]

Normalement si vous avez sélectionné votre plage A4:A67 pour saisir le second paramètre de la formule et si elle est couverte par la totalité des lignes d'un tableau Excel, il aurait du prendre une référence de la forme Tableau1[Colonne1]
Info: je propose souvent deux petites formes pour ajouter ou supprimer des lignes d'un tableau Excel.
Un tutoriel par des exemples en tableaux dans la feuille "Fonction Hasard" de ce classeur

 

[MATLEA]

Bonjour à tous deux,
@Dranreb,
Je pense que votre solution VBA du post #8 est préférable. 🙂
Imaginons une liste en TDS de 100 mots. On veut l'updater par une nouvelle liste de 50 mots.
Si l'utilisateur l'efface simplement et remet sa nouvelle liste, alors se baser sur la profondeur du TDS ne marchera pas il sera toujours de 100.
Modifier le VBA posera moins de soucis à Matlea qui ne semble pas maitriser les TDS. ( sans vous offusquer Matlea 😉)

Bonsoir Sylvanu et Dranreb,

Comme je l'ai déjà indiqué sur ce forum à plusieurs reprises je ne suis pas un spécialiste d'Excel dont j'utilisais jusqu'à il y a peu les fonctions de base uniquement car je n'avais pas besoin de plus professionnellement. Il y a quelques mois j'ai décidé pour des raisons non professionnelles de me lancer dans la création de fichiers pour gérer des manifestations sportives (concours de boules). Grâce à vous tous et notamment Dranreb j'ai développé un système qui fonctionne mais avec quelques interventions humaines nécessaires que je cherche maintenant à supprimer. C'est le cas de la suppression de ces cellules vides lors de l'application de la formule hasard. Votre argumentaire ci-dessus Sylvanu est du chinois pour moi et VBA comme TDS restent des mystères donc je suis loin de m'offusquer du commentaire. Il semblerait que la solution passe par la création d'un tableau excel comme le suggère Dranreb.
Le fichier joint est une nouvelle tentative de ma part, pas totalement satisfaisante comme vous le verrez pour atteindre mon objectif. Vos avis et solutions seront appréciés.
Bonne soirée

 

[Dranreb]

1) — Supprimez les lignes du tableau dont la Colonne1 est vide.
2) — En Tableau2[Colonne2] :

=Hasard(LIGNE()-LIGNE(Tableau2[#En-têtes]);[Colonne1];Tutu)

3) — Si vous avez besoin d'ajouter un nouveau nom, tapez le en dessous du tableau: celui-ci s'agrandira automatiquement d'une ligne et la formule suivra.

 

[MATLEA]

1) — Supprimez les lignes du tableau dont la Colonne1 est vide.
2) — En Tableau2[Colonne2] :

=Hasard(LIGNE()-LIGNE(Tableau2[#En-têtes]);[Colonne1];Tutu)

3) — Si vous avez besoin d'ajouter un nouveau nom, tapez le en dessous du tableau: celui-ci s'agrandira automatiquement d'une ligne et la formule suivra.

Bonjour Dranreb
En réponse à vos commentaires #19 du 2 septembre voir le fichier joint. Merci de me dire où se situe maintenant le problème.
Bonne journée

 

[Dranreb]

Bonjour.
Il n'y a pas à vider des données dans un tableau. Au lieu de cela les lignes qui n'ont plus lieu d'être doivent être supprimées, et les nouvelles données doivent être tapées dans des lignes supplémentaires ajoutées. Je peux vous écrire des macros qui suppriment et ajoutent une ligne facilement si vous voulez.

 

[job75]

Bonjour à tous,

Il me semble que la fonction VBA de Dranreb complique les choses, avec cette macro c'est relativement simple :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim pas&, LO As ListObject, P As Range, mem
pas = 2 'modifiable
Set LO = Target(1).ListObject
If Not LO Is Nothing Then
    Set P = LO.Range
    If Target.Address = P(1).Address Then
        Cancel = True
        If P.Columns.Count = 1 Then MsgBox "Le tableau doit avoir au moins 2 colonnes...": Exit Sub
        mem = P.Columns(1) 'mémorise les valeurs
        Application.ScreenUpdating = False
        P.Columns(2).Offset(1).ClearContents 'vide la 2ème colonne
        P.Sort P(1), xlAscending, Header:=xlYes 'tri de la 1ère colonne
        P(2, 2).Formula = "=OFFSET(" & P(2, 1).Address & "," & pas & "*(ROW()-" & P.Row + 1 & "),)"
        P.Columns(2) = P.Columns(2).Value 'supprime les formules
        P.Columns(2).Replace 0, "", xlWhole 'supprime les valeurs zéro
        With P.Resize(Application.CountA(P.Columns(2)))
            .Columns(1) = "=RAND()" 'ALEA()
            .Sort .Columns(1), Header:=xlYes 'tri aléatoire
        End With
        P.Columns(1) = mem 'remise en l'état initial
    End If
End If
End Sub

Faire un double-clic sur la 1ère en-tête du tableau structuré.

Avec pas = 1 tous les éléments de la 1ère colonne sont récupérés.

A+

 

[MATLEA]

Bonjour à tous,

Il me semble que la fonction VBA de Dranreb complique les choses, avec cette macro c'est relativement simple :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim pas&, LO As ListObject, P As Range, mem
pas = 2 'modifiable
Set LO = Target(1).ListObject
If Not LO Is Nothing Then
    Set P = LO.Range
    If Target.Address = P(1).Address Then
        Cancel = True
        If P.Columns.Count = 1 Then MsgBox "Le tableau doit avoir au moins 2 colonnes...": Exit Sub
        mem = P.Columns(1) 'mémorise les valeurs
        Application.ScreenUpdating = False
        P.Columns(2).Offset(1).ClearContents 'vide la 2ème colonne
        P.Sort P(1), xlAscending, Header:=xlYes 'tri de la 1ère colonne
        P(2, 2).Formula = "=OFFSET(" & P(2, 1).Address & "," & pas & "*(ROW()-" & P.Row + 1 & "),)"
        P.Columns(2) = P.Columns(2).Value 'supprime les formules
        P.Columns(2).Replace 0, "", xlWhole 'supprime les valeurs zéro
        With P.Resize(Application.CountA(P.Columns(2)))
            .Columns(1) = "=RAND()" 'ALEA()
            .Sort .Columns(1), Header:=xlYes 'tri aléatoire
        End With
        P.Columns(1) = mem 'remise en l'état initial
    End If
End If
End Sub

Faire un double-clic sur la 1ère en-tête du tableau structuré.

Avec pas = 1 tous les éléments de la 1ère colonne sont récupérés.

A+

Bonjour Job75,

En effet cette macro marche très bien et j'ai fait pas = 1 et tous les éléments ont été récupérés.
Pourriez-vous dire à un néophyte comme moi comment copier cette macro dans un autre fichier?
Merci

 

[job75]

Pourriez-vous dire à un néophyte comme moi comment copier cette macro dans un autre fichier?

Le fichier doit être enregistré sous le format .xlsm.

Clic droit sur l'onglet => Visualiser le code et y coller la macro du post #22.