XL 2021 finaliser un script VBA pour répartir une liste de personnes dans des villes.

Créer une discussion
Répondre

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
O

Olivier592025

XLDnaute Nouveau
Bonjour,
Comme précisé sur ma présentation je suis novice sur le VBA, je travail sur ce script VBA mais je n'arrive pas à finaliser sont bon fonctionnement. Ci dessous la présentation du script VBA

créer un script VBA qui permet de répartir une liste de noms dans 15 villes (Agen, Anglet, Annecy, Arras, Bordeaux, Grenoble, Limoges, Lyon, Marseille, Nantes, Orleans, Paris, Thouars, Toulouse, Tours), suivant 9 contraintes,
contrainte 1 placer les personnes suivant leur grades ( Compagnon, Aspirant ou Jeune),
contrainte 2 un maximum de 5 Compagnons dans chacune des villes,
contrainte 3 un maximum de 5 Aspirants dans chacune des villes,
contrainte 4 un maximum de 13 Jeunes dans chacune des villes,
contrainte 5 placer les personnes suivant l'un des trois choix de villes,
Contrainte 6 ne pas mettre la personne dans une ville déjà faite
contrainte 7 si maximum atteint placer les personnes suivant le choix de travail proposé dans les villes,
contrainte 8 Si maximum atteint placer les personnes suivant le choix de formation proposé dans les villes,
Contrainte 9 ne pas mettre la personne dans une ville déjà faite,

merci d'avance pour l'aide que vous pourrez me donner.

Bonne journée
 

Pièces jointes

Trier par date Trier par votes
Hello

j'ai regardé ton code, et il me semble qu'il y a des boucles qui ne vont pas..
j'ai repris ton fichier
1) j'ai mis les villes et les grades dans des Tables Structurées (TS) "t_Villes" et "t_Grades"
==> plus besoin de ton code avec du select case pour rentrer les villes

2) le grand tableau est aussi une TS "t_BDD"
==> j'ai repris le code général ainsi que tes fonctions qui cherchent si la ville est déjà utilisées (c'est la que tu avais un souci de boucle)

tes petits tableaux de synthèse à droite ont aussi été mis sous forme de TS

je te laisse regarder le nouveau code avec les commentaires que j'y ai mis
 

Pièces jointes

Vote positif 0 Vote négatif
Bonjour,
On ce fiche complètement de savoir si la personne habilite près d'une ville ou elle habitent toutes dans la même région et dans ce cas ça n'a aucune importance ?

Dans la police nationale c'est celui qui a obtenu la meilleure note qui choisit sa destination en aucurrence des places disponibles.
 
Vote positif 0 Vote négatif
dysorthographie à dit:
Bonjour,
On ce fiche complètement de savoir si la personne habilite près d'une ville ou elle habitent toutes dans la même région et dans ce cas ça n'a aucune importance ?

Dans la police nationale c'est celui qui a obtenu la meilleure note qui choisit sa destination en aucurrence des places disponibles.
Cliquez pour agrandir...
Bonjour dysorthographie,

On ce fiche complétement de savoir si la personne habite prés d'une ville ou dans la région d'une ville.
Merci de l'importance portée à mon problème
 
Vote positif 0 Vote négatif
vgendron à dit:
et une V2 beaucoup plus rapide, car elle travaille sur un tableau
Cliquez pour agrandir...
Merci beaucoup pour le travail réalisé.

Puis-je te solliciter sur la partie du script qui ne fera jamais rien en l'état, et une contrainte du nombre possible d'accueil dans chaque ville?

J'ai rajouté une feuille avec le travail proposé et formation dans chaque ville et sur sheet 1 qui est la contrainte 7 et 8 de mon premier post.
J'ai rajouter un tableau vert avec le nombre possible d'accueil par grade dans chaque ville pour traiter la contrainte 9, un peu modifié car chaque ville ne peut accueillir le même nombre de personnes par rapport à la possibilité de logement.

Dix mille merci si tu as la possibilité de me dépanner dans ce script
 

Pièces jointes

Vote positif 0 Vote négatif
Hello

si j'ai bien compris, ton tableau vert sert à remplacer les valeurs initiales 5-5-13 qui étaient dans le code "maxcompagnon=5, maxaspirange=5 maxjeune=13

j'ai donc modifié le code pour prendre en compte ce tableau

dans le fichier, le premier nom qui n'a pas de ville attribuée est le nom32
==> choix 1 = Paris ==> ne peut pas etre mis, car il y a déjà 5 compagnon à paris
==> Choix 2 = Bordeaux ==> idem
==>Choix 3 = Thouars ==> il y a déjà 1 compagnon


et donc.. la.; on arrive au cas non traité "formation ou travail"
==> il faudrait donc que tu selectionnes une formaton et un travail (ou un seul des deux?)
et que tu expliques comment on détermine la ville à inscrire..
 

Pièces jointes

Vote positif 0 Vote négatif
vgendron à dit:
Hello

si j'ai bien compris, ton tableau vert sert à remplacer les valeurs initiales 5-5-13 qui étaient dans le code "maxcompagnon=5, maxaspirange=5 maxjeune=13

j'ai donc modifié le code pour prendre en compte ce tableau

dans le fichier, le premier nom qui n'a pas de ville attribuée est le nom32
==> choix 1 = Paris ==> ne peut pas etre mis, car il y a déjà 5 compagnon à paris
==> Choix 2 = Bordeaux ==> idem
==>Choix 3 = Thouars ==> il y a déjà 1 compagnon


et donc.. la.; on arrive au cas non traité "formation ou travail"
==> il faudrait donc que tu selectionnes une formaton et un travail (ou un seul des deux?)
et que tu expliques comment on détermine la ville à inscrire..
Cliquez pour agrandir...
Bonjour Vgendron,

Un grand merci pour le travail exécuté, Tu es un 💪cador 💪
Concernant ta question, lors du remplissage de la fiche individuelle, les personnes font un souhait sur trois villes, et une souhait sur le travail et la formation. Dans le tableau les colonnes de répartition sont organisées dans le sens de priorités.
priorité 1 : le souhait d'une des trois villes (que tu à finalisé 👍)
Point travail ou formation:
Priorité 2 : si le souhait des trois villes n'a pas pu être rempli le placer dans une autre ville qu'il n'a pas déjà et qui correspond au travail souhaité.
Priorité 3 : si le souhait de travail n'a pas pu être rempli le placer dans une autre ville qu'il n'a pas déjà et qui correspond au souhait de formation.
la feuille "formation-travail" identifie les formations possible et le type de travail par ville. J'ai mis un tableau avec cases à cocher car les formations et type de travail peut évoluer dans le temps.
Voila les informations pour le souhait du fonctionnement de la deuxième phase de répartition.

D'avance merci si tu arrive à intégrer ces 2 autres priorités.

Bonne journée

🙏Olivier🙏
 
Vote positif 0 Vote négatif
vgendron à dit:
Hello

regarde la PJ
PS: selon l'ordre des colonnes de ton tableau, on regarde le choix de la ville de formation AVANT le choix de la ville de travail
au besoin.. il suffit d'inverser les deux boucles dans le code
Cliquez pour agrandir...
Rebonjour,

Toujours un travail au TOP!
Oui il est mieux d'avoir le choix de ville de travail avant, J'ai fait une simulation avec la colonne rempli pour la formation et travail pour chacun, mais les 9 derniers noms pas de ville attribuée même en retirant le travail et la formation et en changeant des villes de souhait avec des villes qui ne sont pas à 100%.
 

Pièces jointes

Vote positif 0 Vote négatif
et comme tu n'as pas inversé les deux boucles "Formation" et "Travail"
voici le code à remplacer

VB: 
Sub RepartirPersonnes()

    Dim WsBDD As Worksheet
    Dim i As Long, j As Long
    Dim Nom As String, Grade As String, villeAttribuee As String
    Dim choix(1 To 3) As String
    Dim Villes() As Variant 'tablo VBA
    Dim MaxCompagnon As Long, MaxAspirant As Long, MaxJeune As Long
    Dim VilleTestée As String
    Set WsBDD = ThisWorkbook.Sheets("BDD") ' Remplacez "Feuille1" par le nom de votre feuille
    Set TSBDD = WsBDD.ListObjects("t_BDD")
    With TSBDD
        .ListColumns("Ville Attribuée").DataBodyRange.ClearContents 'on efface la colonne "Ville Attribuée"
        TabBDD = .DataBodyRange.Value 'on met le contenu de la TS dans le Tablo vba
    End With
    Set DicoCompteurs = CreateObject("Scripting.dictionary") 'création du dictionnaire des compteurs
    
    Application.ScreenUpdating = False 'désactive le refresh écran pour éviter l'effet sapin de noel
    
    ' Initialiser les villes
    With Sheets("Listes")
        Villes = .ListObjects("t_Villes").DataBodyRange.Value 'on remplit le tableau Ville avec les données de la TS
    End With
    
    For i = LBound(Villes, 1) To UBound(Villes, 1) 'pour chaque ville
        With WsBDD.ListObjects("t_Capacité") 'on récupère les capacités de la ville pour chaque grade
            MaxCompagnon = .ListColumns(Villes(i, 1)).DataBodyRange(1)
            MaxAspirant = .ListColumns(Villes(i, 1)).DataBodyRange(2)
            MaxJeune = .ListColumns(Villes(i, 1)).DataBodyRange(3)
        End With
        DicoCompteurs.Add Villes(i, 1), Array(MaxCompagnon, MaxAspirant, MaxJeune) 'on ajoute la ville dans le dictionnaire en y associant un array des compteurs initiaux
    Next i
        
    ' Parcourir chaque personne dans le tableau==> on travaille directement sur le tablo VBA = plus rapide
    For i = LBound(TabBDD, 1) To UBound(TabBDD, 1) 'pour chaque ligne du tableau
        Nom = TabBDD(i, 1)
        Grade = TabBDD(i, 2)
        
        For j = 1 To 3 'on récupère les 3 choix
            choix(j) = TabBDD(i, j + 2)
        Next j

        ' Essayer d'attribuer une ville selon les choix
        villeAttribuee = ""
        For j = 1 To 3 'pour chaque choix
            If Not VilleDejaFait(choix(j), i) Then ' Vérifier si la ville a déjà été faite
                If PeutAttribuer(CStr(choix(j)), Grade) Then ' Vérifier si le grade peut être attribué
                    villeAttribuee = choix(j)
                    TabBDD(i, 16) = villeAttribuee 'on place la ville attribuée
                    Exit For 'pas besoin de continuer à parcourir les choix
                End If
            End If
        Next j
        
        If villeAttribuee = "" Then 'on regarde le choix de travail
            ChoixTravail = TabBDD(i, 7)
            If ChoixTravail <> "" Then
                'pour chaque ville offrant la formation
                With Sheets("Formation-travail").ListObjects("t_FormationTravail")
                    For j = 1 To .ListRows.Count
                        If UCase(.ListColumns(ChoixTravail).DataBodyRange(j)) = "X" Then
                            VilleTestée = .DataBodyRange(j, 1)
                            If Not VilleDejaFait(VilleTestée, i) Then
                                If PeutAttribuer(VilleTestée, Grade) Then
                                    villeAttribuee = VilleTestée
                                    TabBDD(i, 16) = villeAttribuee 'on place la ville attribuée
                                    Exit For 'pas besoin de continuer à parcourir les choix
                                End If
                            End If
                        End If
                    Next j
                End With
            End If
        End If
        
        If villeAttribuee = "" Then 'on regarde le choix de formation
            ChoixFormation = TabBDD(i, 6)
            If ChoixFormation <> "" Then
                'pour chaque ville offrant la formation
                With Sheets("Formation-travail").ListObjects("t_FormationTravail")
                    For j = 1 To .ListRows.Count
                        If UCase(.ListColumns(ChoixFormation).DataBodyRange(j)) = "X" Then
                            VilleTestée = .DataBodyRange(j, 1)
                            If Not VilleDejaFait(VilleTestée, i) Then
                                If PeutAttribuer(VilleTestée, Grade) Then
                                    villeAttribuee = VilleTestée
                                    TabBDD(i, 16) = villeAttribuee 'on place la ville attribuée
                                    Exit For 'pas besoin de continuer à parcourir les choix
                                End If
                            End If
                        End If
                    Next j
                End With
            End If
        End If
    Next i
    With TSBDD
        .DataBodyRange = TabBDD 'on colle le résultat dans la TS
    End With

    Application.ScreenUpdating = True
    MsgBox "Opération Terminée"
End Sub
 
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

L
  • Question Question
VBA, prendre des données de la feuil1! pour l'inclure dans une textbox1
Réponses
0
Affichages
652
Lou3434
L
Retour