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

Créer une discussion
Répondre
  • Initiateur de la discussion Initiateur de la discussion MATLEA
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
M

MATLEA

XLDnaute Occasionnel
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
 

Pièces jointes

Solution
job75
Bonjour MATLEA, le forum,

Bien sûr on peut utiliser une macro Worksheet_Change :
VB: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pas&, LO As ListObject, P As Range, Q As Range, mem, h&
pas = 1 'modifiable
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
For Each LO In Me.ListObjects
    Set P = LO.Range
    If P.Columns.Count = 1 Then
        MsgBox "Le tableau " & LO.Name & "' doit avoir au moins 2 colonnes..."
    Else
        Set Q = Columns(P.Column - 4).Cells '4ème colonne à gauche
        Set Q = Range(Q(P.Row), Q(Rows.Count).End(xlUp))
        If Q.Row = P.Row Then
            If Not LO.DataBodyRange Is Nothing Then LO.DataBodyRange.Delete xlUp 'RAZ
            P(2...
Trier par date Trier par votes
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 😉)
 
Vote positif 0 Vote négatif
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
 

Pièces jointes

Dernière édition:
Vote positif 0 Vote négatif
sylvanu à dit:
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 😉)
Cliquez pour agrandir...
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
 

Pièces jointes

Vote positif 0 Vote négatif
1) — Supprimez les lignes du tableau dont la Colonne1 est vide.
2) — En Tableau2[Colonne2] :
Code: 
=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.
 
Dernière édition:
Vote positif 0 Vote négatif
Dranreb à dit:
1) — Supprimez les lignes du tableau dont la Colonne1 est vide.
2) — En Tableau2[Colonne2] :
Code: 
=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.
Cliquez pour agrandir...
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
 

Pièces jointes

Vote positif 0 Vote négatif
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.
 
Vote positif 0 Vote négatif
Bonjour à tous,

Il me semble que la fonction VBA de Dranreb complique les choses, avec cette macro c'est relativement simple :
VB: 
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+
 

Pièces jointes

Vote positif 0 Vote négatif
job75 à dit:
Bonjour à tous,

Il me semble que la fonction VBA de Dranreb complique les choses, avec cette macro c'est relativement simple :
VB: 
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+
Cliquez pour agrandir...
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
 
Vote positif 0 Vote négatif
Bonjour MATLEA, le forum,

La suppression des zéros dans la 2ème colonne n'est pas une bonne solution car il peut y avoir des valeurs sous le tableau.

Utilisez plutôt cette macro :
VB: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim pas&, LO As ListObject, P As Range, mem, h&
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
        h = Application.CountA(P.Columns(1))
        h = Application.Ceiling((h - 1) / pas, 1) + 1
        If h < P.Rows.Count Then P.Rows(h + 1).Resize(P.Rows.Count - h).ClearContents
        With P.Resize(h)
            .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
Et marquez ce post comme solution.

A+
 

Pièces jointes

Vote positif 0 Vote négatif
job75 à dit:
Vous avez mis comme solution le post #23.

Or ce post est votre post ce n'est pas lui qu'il faut cocher.

Cochez mon post #22 pour que ça ait un sens.
Cliquez pour agrandir...
ok
job75 à dit:
Bonjour MATLEA, le forum,

La suppression des zéros dans la 2ème colonne n'est pas une bonne solution car il peut y avoir des valeurs sous le tableau.

Utilisez plutôt cette macro :
VB: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim pas&, LO As ListObject, P As Range, mem, h&
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
        h = Application.CountA(P.Columns(1))
        h = Application.Ceiling((h - 1) / pas, 1) + 1
        If h < P.Rows.Count Then P.Rows(h + 1).Resize(P.Rows.Count - h).ClearContents
        With P.Resize(h)
            .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
Et marquez ce post comme solution.

A+
Cliquez pour agrandir...

Cher job75

merci beaucoup. Compétence, réactivité, suivi, commentaires clairs et didactiques. Très impressionnant, félicitations.
 
Vote positif 0 Vote négatif
MATLEA à dit:
ok


Cher job75

merci beaucoup. Compétence, réactivité, suivi, commentaires clairs et didactiques. Très impressionnant, félicitations.
Cliquez pour agrandir...
Bonjour Job75,

Votre macro marche très bien. Je vous en remercie à nouveau.

J’ai cependant une question concernant l’exploitation des résultats.

On voit très bien dans la colonne H (fichier joint) que les cellules avec données ont été regroupées dans le haut de la colonne, les cellules vides étant en bas. Ces cellules restent vides en colonne H mais affichent « 0 » si on constitue une nouvelle colonne (colonne J) reprenant (fonction =) les données de la colonne H.

Serait-il possible que ces cellules restent vides. J’ai ainsi noté qu’avec la fonction par exemple =J4&" "&J5&" " qui permet de constituer une nouvelle cellule avec les données de 2 cellules distinctes (ici toto5 toto8) son application sur les cellules J15 et J16, vides dans la colonne H génère ici la cellule L5 comprenant 2 « 0 ».

Par contre si on l’applique sur 2 cellules vides comme j19 et j20, la cellule générée L6 est vide.

Ma question est de savoir si les cellules résiduelles sans données à l’intérieur du tableau comme J15 et J16 par exemple pourraient être reprises informatiquement vides et ne pas afficher « 0 ».

Merci et bien cordialement
 

Pièces jointes

Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

M
  • Résolu(e)
XL 2021 Verrouillage de cellules après exécution d'une macro
2
Réponses
21
Affichages
524
TooFatBoy
TooFatBoy
A
  • Résolu(e)
Microsoft 365 cumuler des valeurs de cellules dont l'emplacement peut varier dans des tableaux
Réponses
4
Affichages
571
auloin
A
T
  • Question Question
Microsoft 365 Faire la somme de cellules d'une colonne ayant le même nom dans une autre colonne.
Réponses
6
Affichages
232
Thibox
T
libellule85
  • Question Question
Microsoft 365 Connaitre le nombre de mardi, mercredi etc.. dans une colonne dynamique
Réponses
7
Affichages
322
Gégé-45550
Gégé-45550
P
  • Question Question
XL 2019 Bloquer la saisie dans une colonne sous condition
Réponses
1
Affichages
576
sylvanu
sylvanu
L
  • Question Question
XL 2016 TCD qui compte le nombre de oui et non dans une colonne
Réponses
2
Affichages
642
laeti95
L
T
  • Résolu(e)
XL 2016 problème d'apparition automatique non voulue d'une série de date dans une colonne de tableau
Réponses
3
Affichages
477
therse
T
P
  • Question Question
Microsoft 365 suite de chiffres dans colonne
Réponses
11
Affichages
910
job75
job75
Z
  • Résolu(e)
Microsoft 365 Calcul d'un nombre de valeurs dans plusieurs cellules
Réponses
5
Affichages
533
Zeorymex
Z
A
  • Question Question
XL 2016 Verrouiller automatiquement la colonne précédente après avoir modifier une cellule de la colonne suivante
Réponses
0
Affichages
469
arnoldbrice19
A
X
  • Résolu(e)
XL 2016 Souci pour mettre un nombre dans une cellule en croisant colonne et ligne.
2
Réponses
16
Affichages
1 K
Xender
X
C
  • Question Question
XL 2021 case à cocher dans ListView impacte une cellule de feuille
Réponses
1
Affichages
885
fanch55
fanch55
C
  • Question Question
Microsoft 365 trouver le nombre de référence unique dans une colonne en fonction de critères de deux autres colonnes
Réponses
4
Affichages
927
R@chid
R@chid
T
  • Question Question
XL pour MAC Recherche de mots clefs dans une cellule Texte
Réponses
2
Affichages
1 K
Tsr75a
T
D
  • Résolu(e)
Autres Décomposer un nombre dans les cellules suivantes
Réponses
4
Affichages
1 K
mapomme
mapomme
C
  • Question Question
XL pour MAC Sélection plage de cellules avec variable dans macro
Réponses
3
Affichages
1 K
Staple1600
Staple1600
D
  • Question Question
XL 2019 Compter le nombre de 1 dans une colonne donnée
Réponses
6
Affichages
684
job75
job75
I
  • Question Question
Autres Une petite correction d'un code qui rempli les cellules vides
Réponses
7
Affichages
886
TooFatBoy
TooFatBoy
M
  • Question Question
XL 2013 VBA-Envoyer par email "Plage de cellules collée dans un nouveau classeur en gardant formules de certaines cellules
Réponses
2
Affichages
989
MONTREAL2020
M
S
  • Question Question
XL 2016 Cellule change de couleur si la valeur existe dans une autre cellule d'un autre onglet
Réponses
12
Affichages
1 K
sum01
S
Retour