XL 2021 Bugs dans la propagation de cellules

[MATLEA]

Bonjour,

Dans le fichier joint je suis bloqué suite à l'apparition de bugs dans la propagation de la formule suivante qui est de type
=SIERREUR(RECHERCHEV(PETITE.VALEUR(SI(ESTTEXTE(CC$25:CC$56);CB$25:CB$56);LIGNE(CD1));CB:CC;2;FAUX);"") visant à randomiser la liste des valeurs.
Je recherche quelqu'un qui pourrait me dire comment régler ce problème soit par correction des bugs soit par utilisation d'une formule alternative.

Merci d'avance et bonne journée.

 

[riton00]

Bonjour,

Aucune erreur de propagation chez moi 🤔

Slts

 

[Dranreb]

Bonjour.
Vous avez dans le classeur joint une fonction perso Hasard qui marche très bien sans poser de problème de propagation, et sans avoir besoin d'autre info numérique. Par exemple en '2 contre 2'!AN4, à propager sur 64 lignes et 2 colonnes :

=Hasard(LIGNE()-3;$AM$4:$AM$67;1,234565789)

 

[MATLEA]

Bonjour.
Vous avez dans le classeur joint une fonction perso Hasard qui marche très bien sans poser de problème de propagation, et sans avoir besoin d'autre info numérique. Par exemple en '2 contre 2'!AN4, à propager sur 64 lignes et 2 colonnes :

=Hasard(LIGNE()-3;$AM$4:$AM$67;1,234565789)

Parfait merci

 

[MATLEA]

Parfait merci

Bonsoir,

Je ne sais pas ce qui se passe. Quand je vous ai répondu tout marchait parfaitement quand soudain le système a buggé.
Voici le fichier avec ces bugs.
Cordialement.

 

[Dranreb]

Je ne sais pas ce qui s'est passé. En revalidant les formules ça marche.
De toute façon c'était un exemple. Vous auriez intérêt à spécifier une graine différente pour les deux colonnes pour que leur ordre ne soit pas le même …
Peut être auriez vous aussi intérêt à utiliser une macro qui produit des noms figés.
Ceci fonctionne, après test :

Sub Mélanger(ByVal RngRésu As Range, ByVal RngDon As Range)
   Dim TNomsDon(), LMax As Long, TN°() As Long, TNomsRésu(), L As Long, P As Long
   TNomsDon = RngDon.Value
   LMax = UBound(TNomsDon, 1)
   ReDim TN°(1 To LMax), TNomsRésu(1 To LMax, 1 To 1)
   TN°(1) = 1: Randomize
   For L = 2 To LMax: P = Int(Rnd * L) + 1: If P < L Then TN°(L) = TN°(P)
      TN°(P) = L: Next L
   For L = 1 To LMax: TNomsRésu(TN°(L), 1) = TNomsDon(L, 1): Next L
   RngRésu.Resize(LMax, 1).Value = TNomsRésu
   End Sub

Vous pouvez l'appeler partout où vous voulez mettre des noms mélangés, par exemple :

Mélanger Range("AI4"), Range("AH4:AH67")

 

[MATLEA]

Bonsoir,

Je ne sais pas ce qui se passe. Quand je vous ai répondu tout marchait parfaitement quand soudain le système a buggé.
Voici le fichier avec ces bugs.
Cordialement.

Re-bonsoir,

Je viens de trouver la solution, les macros s'étaient désactivées. J'ai résolu le problème en les réactivant.
Désolé pour le dérangement.
Encore merci.

Je ne sais pas ce qui s'est passé. En revalidant les formules ça marche.
De toute façon c'était un exemple. Vous auriez intérêt à spécifier une graine différente pour les deux colonnes pour que leur ordre ne soit pas le même …
Peut être auriez vous aussi intérêt à utiliser une macro qui produit des noms figés.
Ceci fonctionne, après test :

Sub Mélanger(ByVal RngRésu As Range, ByVal RngDon As Range)
   Dim TNomsDon(), LMax As Long, TN°() As Long, TNomsRésu(), L As Long, P As Long
   TNomsDon = RngDon.Value
   LMax = UBound(TNomsDon, 1)
   ReDim TN°(1 To LMax), TNomsRésu(1 To LMax, 1 To 1)
   TN°(1) = 1: Randomize
   For L = 2 To LMax: P = Int(Rnd * L) + 1: If P < L Then TN°(L) = TN°(P)
      TN°(P) = L: Next L
   For L = 1 To LMax: TNomsRésu(TN°(L), 1) = TNomsDon(L, 1): Next L
   RngRésu.Resize(LMax, 1).Value = TNomsRésu
   End Sub

Vous pouvez l'appeler partout où vous voulez mettre des noms mélangés, par exemple :

Mélanger Range("AI4"), Range("AH4:AH67")

Merci beaucoup je vais essayer mais votre dernière formule marche bien.

 

[MATLEA]

Par hasard vous n auriez pas une solution à mon problème envoyé dimanche , sélection, migration et réarrangement de cellules.
Cordialement

 

[Dranreb]

Non, je n'ai pas d'idée. Trop complexe, trop d'interventions manuelles. Vous devriez vous intéresser d'avantage aux systèmes connus faciles à mettre en œuvre et que la plupart des pétanqueurs admettent. Jamais vu, par exemple, des participants spécialisés tout au long d'un tournoi au rôle soit de pointeur soit de tireur 🙄 …

 

[MATLEA]

Non, je n'ai pas d'idée. Trop complexe, trop d'interventions manuelles. Vous devriez vous intéresser d'avantage aux systèmes connus faciles à mettre en œuvre et que la plupart des pétanqueurs admettent. Jamais vu, par exemple, des participants spécialisés tout au long d'un tournoi au rôle soit de pointeur soit de tireur 🙄 …

Bonsoir
Naturellement dans les concours officiels ce type de spécialisation n'existe pas. C'est par contre un bon moyen en interne pour équilibrer les équipes et faire jouer ensemble des gens de niveau différent;

 

[MATLEA]

Non, je n'ai pas d'idée. Trop complexe, trop d'interventions manuelles. Vous devriez vous intéresser d'avantage aux systèmes connus faciles à mettre en œuvre et que la plupart des pétanqueurs admettent. Jamais vu, par exemple, des participants spécialisés tout au long d'un tournoi au rôle soit de pointeur soit de tireur 🙄 …

Je ne sais pas ce qui s'est passé. En revalidant les formules ça marche.
De toute façon c'était un exemple. Vous auriez intérêt à spécifier une graine différente pour les deux colonnes pour que leur ordre ne soit pas le même …
Peut être auriez vous aussi intérêt à utiliser une macro qui produit des noms figés.
Ceci fonctionne, après test :

Sub Mélanger(ByVal RngRésu As Range, ByVal RngDon As Range)
   Dim TNomsDon(), LMax As Long, TN°() As Long, TNomsRésu(), L As Long, P As Long
   TNomsDon = RngDon.Value
   LMax = UBound(TNomsDon, 1)
   ReDim TN°(1 To LMax), TNomsRésu(1 To LMax, 1 To 1)
   TN°(1) = 1: Randomize
   For L = 2 To LMax: P = Int(Rnd * L) + 1: If P < L Then TN°(L) = TN°(P)
      TN°(P) = L: Next L
   For L = 1 To LMax: TNomsRésu(TN°(L), 1) = TNomsDon(L, 1): Next L
   RngRésu.Resize(LMax, 1).Value = TNomsRésu
   End Sub

Vous pouvez l'appeler partout où vous voulez mettre des noms mélangés, par exemple :

Mélanger Range("AI4"), Range("AH4:AH67")

 

[MATLEA]

Bonjour Dranreb,

j'aurais quelques petites questions complémentaires par rapport à vos 2 propositions.
1- Comment spécifier une graine spécifique à chaque colonne. D'abord qu'est-ce qu'une graine?
2 - En ce qui concerne la macro. Je suppose que je dois d'abord créer la macro en cliquant sur enregistrer une macro, lui donner un nom puis coller votre texte allant de sub à end sub dans l'espace description puis arrêtez l'enregistrement.
Ensuite pour l'appeler vous me dites d'utiliser Mélanger Range("AI4"), Range("AH4:AH67"). Je me suis positionné en AI4 et j'ai essayé différentes combinaisons de ce texte (Mélanger Range("AI4"), Range("AH4:AH67") ou seulement Range("AH4:AH67") etc) mais sans succès. Comment faut il procéder?
Merci d'avance;

 

[Dranreb]

Bonjour.
La graine c'est une base de départ d'une série de nombre aléatoires engendrés par la fonction VBA.Rnd.
Non, insérez un nouveau module et collez ça dans la fenêtre de code. Cette macro elle est faite pour être appelée ensuite selon ce modèle depuis d'autres macros.

 

[MATLEA]

Bonjour.
La graine c'est une base de départ d'une série de nombre aléatoires engendrés par la fonction VBA.Rnd.
Non, insérez un nouveau module et collez ça dans la fenêtre de code. Cette macro elle est faite pour être appelée ensuite selon ce modèle depuis d'autres macros.

Merci mais je découvre au fur et à mesure et n'ai pas les codes ni tout le vocabulaire.
Pour la graine que faut-il que je fasse? Coller VBA.Rnd ou = VBA.Rnd dans la 1ère cellule de la colonne de mes valeurs, ailleurs? Je suis un peu perdu. Dites moi.
pour la macro, je suis allé dans virtual basic, onglet insertion, insérer un nouveau module et j'ai collé le texte depuis Sub jusqu'à endsub. Mais que faire ensuite, pas par pas?
Excusez mon ignorance

Merci

 

[Dranreb]

Il n'est pas obligatoire qu'une graine soit portée dans une cellule. Mais si elle ne l'est pas elle doit avoir obligatoirement avoir un nom dans la feuille ou dans le classeur. J'ai vu que vous aviez déjà la macro ChangerGraine dans le module MChgGraine capable de créer si nécessaire ce nom ou changer la valeur de la graine. Mais je m'aperçois qu'elle possède un défaut de taille: si plusieurs graines sont générées dans la même seconde, elles risquent d'avoir la même valeur.
Pour remédier à cela, je propose de la modifier comme ceci :

Sub ChangerGraine(Optional ByVal Cible = "!Graine")
   Static DernH As Double: Dim H As Double
Rem. Change la graine utilisée pour les tirages aléatoire.
'  Argument Cible facultatif. Si omis, "!Graine" est assumé. 2 cas :
'     1) — Un String indiquant le nom de la graine. S'il commence par un point d'exclamation,
'          la suite indique un nom dans la feuille active, sinon le tout est un nom dans le classeur
'          Ce nom peut avoir comme référence une cellule contenant la graine ou directement sa valeur.
'          Si le nom n'existe pas, il est créé comme ayant pour référence la valeur.
'     2) — Un Range représentant la cellule devant contenir la graine
'  Remarque: Pour afficher cette graine, le format de cellule "jjjj hh:mm:ss" est assez pertinent,
'     parce que la graine sera l'heure courante au jour de la semaine seulement. Now aurait en effet
'     trop de bits significatifs pour pouvoir être considérée à la seconde près en le type de donnée
'     Single utilisé par la procédure Randomize et renvoyé par la fonction Rnd.
   H = Date Mod 7 + Time: If H = 0 Then H = 7
   If H = DernH Then H = H + 2 ^ -19
   DernH = H
   If TypeOf Cible Is Range Then Cible.Value = H: Exit Sub
   If VarType(Cible) <> vbString Then MsgBox "Argument de type """ & TypeName(Cible) & """ incorrect.", _
      "Spécifiez une cellule ou un nom pour la feuille ou le classeur.", _
      vbCritical, "ChangerGraine": Exit Sub
   On Error Resume Next
   If Left$(Cible, 1) = "!" Then
      If TypeOf ActiveSheet.Evaluate(Mid$(Cible, 2)) Is Range Then
         ActiveSheet.Evaluate(Mid$(Cible, 2)).Value = H
      Else
         ActiveSheet.Names.Add Mid$(Cible, 2), H
         If Err Then MsgBox "Err " & Err.Number & " lors de :" _
            & vbLf & "ActiveSheet.Names.Add """ & Mid$(Cible, 2) & """, " & H _
            & vbLf & Err.Description, vbExclamation, "ChangerGraine"
         End If
   Else
      If TypeOf Application.Evaluate(Cible) Is Range Then
         Application.Evaluate(Cible).Value = H
      Else
         ActiveWorkbook.Names.Add Cible, H
         If Err Then MsgBox "Err " & Err.Number & " lors de :" _
            & vbLf & "ActiveWorkbook.Names.Add """ & Cible & """, " & H _
            & vbLf & Err.Description, vbExclamation, "ChangerGraine"
         End If
      End If
   End Sub

Pour l'autre système, écrivez ensuite par exemple une autre macro qui utilise cette Sub Mélanger pour plusieurs plages spécifiques, et affectez la à un bouton de formulaire dessiné dans la feuille Excel.