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

 

[MATLEA]

Bonjour MATLEA,

Les formules de liaison du type =H4 renvoient toujours 0 si H4 est vide.

Pour Remplacer 0 par le texte vide "" il faut utiliser =""&H4

ou encore =SI(ESTVIDE(H4);"";H4)

Pas besoin d'utiliser l'espace " ".

A+

merci beaucoup

 

[job75]

Le sujet de ce fil c'est la question posée au post #1 et pas celle posée au post #29 qui est vraiment accessoire.

Donc il vaut mieux marquer le post #27 comme solution de ce fil.

 

[MATLEA]

Le sujet de ce fil c'est la question posée au post #1 et pas celle posée au post #29 qui est vraiment accessoire.

Donc il vaut mieux marquer le post #27 comme solution de ce fil.

Bonsoir job75

J'ai une question concernant l'actualisation d'un tableau excel après exécution de votre macro.
Fichier joint.
Merci et bonne soirée

 

[job75]

Bonjour MATLEA, le forum,

Je ne comprends pas ce que vous voulez faire.

Peut-être mettre à jour simultanément les 2 tableaux structurés ?

Dans ce cas il ne faut pas utiliser une macro Worksheet_BeforeDoubleClick mais celle-ci :

Sub MAJ_Aleatoire()
Dim pas&, LO As ListObject, P As Range, mem, h&
pas = 1 'modifiable
Application.ScreenUpdating = False
For Each LO In ActiveSheet.ListObjects
    Set P = LO.Range
    If P.Columns.Count = 1 Then
        MsgBox "Le tableau '" & LO.Name & "' doit avoir au moins 2 colonnes..."
    Else
        mem = P.Columns(1) 'mémorise les valeurs
        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
Next
End Sub

Elle est affectée à un bouton.

A+

 

[MATLEA]

Bonjour MATLEA, le forum,

Je ne comprends pas ce que vous voulez faire.

Peut-être mettre à jour simultanément les 2 tableaux structurés ?

Dans ce cas il ne faut pas utiliser une macro Worksheet_BeforeDoubleClick mais celle-ci :

Sub MAJ_Aleatoire()
Dim pas&, LO As ListObject, P As Range, mem, h&
pas = 1 'modifiable
Application.ScreenUpdating = False
For Each LO In ActiveSheet.ListObjects
    Set P = LO.Range
    If P.Columns.Count = 1 Then
        MsgBox "Le tableau '" & LO.Name & "' doit avoir au moins 2 colonnes..."
    Else
        mem = P.Columns(1) 'mémorise les valeurs
        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
Next
End Sub

Elle est affectée à un bouton.

A+

Bonjour job75,

Merci encore pour votre dévouement et vos efforts pour trouver des solutions à mes questions.

Je ne vois pas en exécutant votre nouvelle macro la mise à jour que l'on pouvait espérer. Le tableau 6 avait été initialement réalisé avec des cellules vides en H7 et H8. Si la saisie manuelle extemporanément de données dans le tableau 4, à savoir Toto4 et Toto5 en H7 et H8 respectivement entraine bien leur transfert dans le tableau 5, la MAJ n'est par contre pas réalisée dans le tableau 6, ce qui était l'objet de ma question.
Par ailleurs la précédente macro réunissait après tirage aléatoire les cellules avec données dans le haut du tableau les séparant des cellules vides qui se regroupaient en fin de tableau ce qui était l'objet initial de ma question (nous n'abordons pas ici la question des "0" versus cellules vides qui est réglé grâce à votre perspicacité).
Avec la nouvelle macro cellules avec données et cellules vides ne sont plus discriminées, ce qui est gênant.

Merci et bien cordialement.

 

[MATLEA]

Bonjour job75,

Merci encore pour votre dévouement et vos efforts pour trouver des solutions à mes questions.

Je ne vois pas en exécutant votre nouvelle macro la mise à jour que l'on pouvait espérer. Le tableau 6 avait été initialement réalisé avec des cellules vides en H7 et H8. Si la saisie manuelle extemporanément de données dans le tableau 4, à savoir Toto4 et Toto5 en H7 et H8 respectivement entraine bien leur transfert dans le tableau 5, la MAJ n'est par contre pas réalisée dans le tableau 6, ce qui était l'objet de ma question.
Par ailleurs la précédente macro réunissait après tirage aléatoire les cellules avec données dans le haut du tableau les séparant des cellules vides qui se regroupaient en fin de tableau ce qui était l'objet initial de ma question (nous n'abordons pas ici la question des "0" versus cellules vides qui est réglé grâce à votre perspicacité).
Avec la nouvelle macro cellules avec données et cellules vides ne sont plus discriminées, ce qui est gênant.

Merci et bien cordialement.

Bonjour job75,

Merci encore pour votre dévouement et vos efforts pour trouver des solutions à mes questions.

Je ne vois pas en exécutant votre nouvelle macro la mise à jour que l'on pouvait espérer. Le tableau 6 avait été initialement réalisé avec des cellules vides en H7 et H8. Si la saisie manuelle extemporanément de données dans le tableau 4, à savoir Toto4 et Toto5 en H7 et H8 respectivement entraine bien leur transfert dans le tableau 5, la MAJ n'est par contre pas réalisée dans le tableau 6, ce qui était l'objet de ma question.
Par ailleurs la précédente macro réunissait après tirage aléatoire les cellules avec données dans le haut du tableau les séparant des cellules vides qui se regroupaient en fin de tableau ce qui était l'objet initial de ma question (nous n'abordons pas ici la question des "0" versus cellules vides qui est réglé grâce à votre perspicacité).
Avec la nouvelle macro cellules avec données et cellules vides ne sont plus discriminées, ce qui est gênant.

Merci et bien cordialement.

Non je veux que le tableau structuré soit mis à jour avec les nouvelles données saisies dans le tableau amont. Le tableau structuré est d'abord créé sans Toto4 et Toto5 dans le tableau 4. Si je les rajoute dans un second temps dans ce tableau 4 je voudrais que le tableau structuré soit mis à jour avec ces 2 nouvelles données, le tout en séparant cellules avec données et cellules vides.
Est-ce plus clair exposé ainsi?
Cordialement

 

[job75]

Si je comprends bien il vous suffit de faire manuellement un Copier-Collage spécial-Valeurs de J4:J14 vers L4:L14.

Mais quel est l'intérêt de toute cette gymnastique ?

 

[MATLEA]

Si je comprends bien il vous suffit de faire manuellement un Copier-Collage spécial-Valeurs de J4:J14 vers L4:L14.

Mais quel est l'intérêt de toute cette gymnastique ?

Il s’agit en fait de gérer les concours de pétanque et de la façon la plus automatisée possible.

Une des formules utilisées pour les concours est de faire jouer 3 parties à chaque équipe ; Le nombre d’équipes est variable et celles-ci sont soit constituées à l’avance (2 joueurs décident de jouer ensemble) ou tirer au sort. A la 1ère partie les équipes sont opposées 2 par 2 aléatoirement. Les vainqueurs vont s’opposer 2 par 2 dans la seconde partie après un tirage aléatoire, idem pour les perdants. En partie 3 c’est la même chose, confrontations entre vainqueurs de 2 parties, entre vainqueurs d’une seule ou entre perdants des 2. Les scores sont analysés pour déterminer un classement sur la base des parties gagnées ainsi que des points faits (partie gagnée à 13) moins ceux laissés faire.

Avec l’aide des experts du forum (notamment Dranreb) j’ai développé une véritable usine à gaz en plusieurs étapes qui fonctionne quand le nombre d’équipes engagées est égal au nombre d’équipes prévues dans la conception du fichier. Par exemple si 128 équipes se sont engagées pour un concours prévu pour 128 mon programme fonctionne bien. Si par contre le nombre d’équipes engagées est inférieur les cases vides interfèrent et le tableau des confrontations est déséquilibré, nécessitant une intervention manuelle pour rétablir un tableau correct pour que chaque compétiteur ait un adversaire.

Grâce à vous j’ai presque solutionné le problème avec le tableau structuré et la macro. Mais pour que cela puisse fonctionner il faut que le tableau structuré puisse se mettre à jour quand en amont les données dans le tableau qui a servi à constituer le tableau structuré ont changé.

Pour reprendre les exemples des Toto, si Toto4 et Toto5 sont ajoutés dans le tableau 4 il faudrait que le tableau structuré préalablement généré (sans Toto4 ni Toto5) soit automatiquement mis à jour avec ces 2 valeurs dans la colonne 1 pour ensuite être intégrés dans la colonne 2 où cellules avec valeurs doivent être regroupées en haut et cellules vides en bas.

Le tableau 5 n’a aucune valeur, je voulais seulement montrer que la fonction = incorporait Toto4 et Toto5 dans ce tableau alors que le tableau structuré 6 n’était pas mis à jour.

Si l’on arrive à régler ce problème et si vous êtes intéressé je veux bien vous envoyer à terme un fichier fonctionnel pour vous montrer l’usine à gaz que j’ai développée mais il me faut au préalable régler le problème de MAJ du tableau fonctionnel.

Un dernier point, c’est un défi intellectuel complètement gratuit que je me suis lancé, rendu possible grâce aux compétences et au dévouement des experts du forum que je remercie. C’est du loisir, il n’y a rien de vital.

PS Je serai non connecté à partir de maintenant jusqu'à ce soir.
Bonne journée et encore merci

 

[job75]

Bon je pense avoir compris, voyez le fichier joint et cette nouvelle macro :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim pas&, LO As ListObject, P As Range, Q As Range, mem, h&
pas = 1 '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
        On Error Resume Next
        Set Q = Application.InputBox("Sélectionnez la plage que vous voulez copier et coller :", "Copier en Colonne1", Type:=8)
        On Error GoTo 0
        Application.ScreenUpdating = False
        If Not Q Is Nothing Then
            If Not LO.DataBodyRange Is Nothing Then LO.DataBodyRange.Delete xlUp 'RAZ
            P(2, 1).Resize(Q.Rows.Count) = Q.Value 'copie les valeurs
            Set P = LO.Range
        End If
        mem = P.Columns(1) 'mémorise les valeurs
        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

Double-clic sur la 1ère en-tête Colonne1, E4 ou L3.

Dans l'InputBox pour E4 sélectionnez la plage A4:A14, pour L3 sélectionnez la plage H4:H14.

Tableau 2 et Tableau 5 sont tout à fait inutiles.

 

[MATLEA]

Bon je pense avoir compris, voyez le fichier joint et cette nouvelle macro :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim pas&, LO As ListObject, P As Range, Q As Range, mem, h&
pas = 1 '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
        On Error Resume Next
        Set Q = Application.InputBox("Sélectionnez la plage que vous voulez copier et coller :", "Copier en Colonne1", Type:=8)
        On Error GoTo 0
        Application.ScreenUpdating = False
        If Not Q Is Nothing Then
            If Not LO.DataBodyRange Is Nothing Then LO.DataBodyRange.Delete xlUp 'RAZ
            P(2, 1).Resize(Q.Rows.Count) = Q.Value 'copie les valeurs
            Set P = LO.Range
        End If
        mem = P.Columns(1) 'mémorise les valeurs
        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

Double-clic sur la 1ère en-tête Colonne1, E4 ou L3.

Dans l'InputBox pour E4 sélectionnez la plage A4:A14, pour L3 sélectionnez la plage H4:H14.

Tableau 2 et Tableau 5 sont tout à fait inutiles.

Bonjour job75 et merci pour vos efforts,

Cette nouvelle macro est un progrès en permettant la MAJ du tableau structuré en fonction des modifications du tableau amont, (tableau 1 colonne A ou tableau 4 colonne H).
Le problème pour moi est que son application nécessite une intervention manuelle (sélection des plages A4:A14 ou H4:H14 dans les inputboxes);
Je ne sais pas si c'est possible de trouver une alternative de façon à automatiser cette étape de façon à ce que chaque modification dans les tableaux 1 ou 4 entraine automatiquement la modification des tableaux structurés 3 ou 6. Le nombre de plages concernées dans les tableaux 1 ou 4 (ici 11, de 4 à 14) peut être défini en début (dépendant du nombre d'équipes dans le concours) et rester fixe tout le temps de la compétition;
N'hésitez pas à me dire la faisabilité ou pas de cette étape d'automatisation.
Bonne journée en vous renouvelant mes remerciements.

 

[job75]

Bonjour MATLEA, le forum,

Bien sûr on peut utiliser une macro Worksheet_Change :

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, 1).Resize(Q.Rows.Count) = Q.Value 'copie les valeurs
            Set P = LO.Range
        End If
        mem = P.Columns(1) 'mémorise les valeurs
        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
Next
Application.EnableEvents = True 'réactive les évènements
End Sub

Modifiez ou validez une cellule quelconque.

Les tableaux structurés Tableau 3 et Tableau 6 sont mis à jour à partir des données de la 4ème colonne à gauche.

A+

 

[MATLEA]

Bonjour MATLEA, le forum,

Bien sûr on peut utiliser une macro Worksheet_Change :

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, 1).Resize(Q.Rows.Count) = Q.Value 'copie les valeurs
            Set P = LO.Range
        End If
        mem = P.Columns(1) 'mémorise les valeurs
        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
Next
Application.EnableEvents = True 'réactive les évènements
End Sub

Modifiez ou validez une cellule quelconque.

Les tableaux structurés Tableau 3 et Tableau 6 sont mis à jour à partir des données de la 4ème colonne à gauche.

A+

Super, c'est exactement ce que je recherche.

Sur un nouveau fichier j'ai simplement du mal à générer une colonne 2 fonctionnelle (voir fichier joint, essai lundi).
Après avoir créé le tableau dynamique qui génère une colonne1 (débutant en H3) qui contient les cellules avec les données attendues , je génère une colonne2 (débutant en I3) en propageant à partir du point en bas à droite de la cellule colonne1 (H3). C'est ce que je faisais auparavant avec votre précédente macro. Par contre un double click sur colonne1 ou sur n'importe quelle autre plage ne fait pas évoluer la colonne2 comme vous y parvenez dans votre fichier de ce matin.
Où est mon erreur?

Merci

 

[job75]

Il n'y a plus de macro pour le double-clic : modifiez ou validez une cellule (par exemple touche <suppr> sur une cellule vide).

Le 1er tableau est copié jusqu'en D10 puisque qu'il n'y a pas de valeurs en dessous.

 

[MATLEA]

Super, c'est exactement ce que je recherche.

Sur un nouveau fichier j'ai simplement du mal à générer une colonne 2 fonctionnelle (voir fichier joint, essai lundi).
Après avoir créé le tableau dynamique qui génère une colonne1 (débutant en H3) qui contient les cellules avec les données attendues , je génère une colonne2 (débutant en I3) en propageant à partir du point en bas à droite de la cellule colonne1 (H3). C'est ce que je faisais auparavant avec votre précédente macro. Par contre un double click sur colonne1 ou sur n'importe quelle autre plage ne fait pas évoluer la colonne2 comme vous y parvenez dans votre fichier de ce matin.
Où est mon erreur?

Merci

Un mail à 11h40 m'a signalé un message de votre part mais il n'y a rien sur le site excel-downloads
a+

 

[MATLEA]

Un mail à 11h40 m'a signalé un message de votre part mais il n'y a rien sur le site excel-downloads
a+

Il n'y a plus de macro pour le double-clic : modifiez ou validez une cellule (par exemple touche <suppr> sur une cellule vide).

Le 1er tableau est copié jusqu'en D10 puisque qu'il n'y a pas de valeurs en dessous.

Merci mais comment je fais pour avoir une colonne2 qui fonctionne (voir fichier essai lundi)?
Bonne journée