IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Sélectionner lignes jusqu'à ce que la somme voulue soit atteinte [XL-2016]


Sujet :

Macros et VBA Excel

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre confirmé
    Femme Profil pro
    Étudiant
    Inscrit en
    Juillet 2018
    Messages
    56
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 29
    Localisation : France, Aisne (Picardie)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Transports

    Informations forums :
    Inscription : Juillet 2018
    Messages : 56
    Par défaut Sélectionner lignes jusqu'à ce que la somme voulue soit atteinte
    Bonjour à tous !

    Alors accrochez-vous ça va être un peu complexe à comprendre. Je vais essayer d'être la plus claire possible.

    J'ai donc une feuille INSEE :
    Où se trouve en première colonne le code postal, puis pw = le poids. La colonne Ville 1 correspond à la distance entre le code postal et la ville 1. La colonne Ville2 idem, c'est la distance entre le code postal et la ville2 etc...
    La colonne zone depot : c'est la ville la plus proche des 3 du code postal. (Mon nombre de villes sera variable pour d'autres fichiers)

    (Pour le côté représentation réel ceux sont 3 villes proches l'une de l'autre)

    A côté se trouve le tableau "somme pw" :
    ou se trouve la somme de chaque ville pour pw, l'objectif que je veux atteindre (c'est une répartition), et la colonne delta qui est la différence entre actuel et objectif.

    Mon objectif : je veux pouvoir changer la répartition actuelle et me rapprocher de l'objectif. Tout en prenant les codes postaux les plus proches qu'une seule fois.

    La méthode " à la main" serait donc de commencer la répartition par la ville ayant le delta minimum et négatif ici : VILLE3
    donc pour tous les codes postaux de VILLE3 on les garde et on met dans la colonne G (nv depot) : ville3
    Ensuite pour atteindre l'objectif il nous faut donc attribuer 10% (= delta*-1). Je dois donc sélectionner les codes postaux les plus proches de VILLE3
    A la main je triais la colonne VILLE3 ordre croissant, je ne prends pas en compte les lignes où il y a déjà un nv dépôt et j'ajoute au fur et à mesure ville3 ds colonne G jusqu'à atteindre un nouveau pw (colonne L) >= à l'objectif (colonne J)

    ensuite j'aimerai créer une boucle pr pouvoir passer à la prochaine ville qui a le prochain delta minimum, ici : VILLE2
    (pour l'instant j'inscris un 0 dans le delta précédent et je relance ma macro)
    donc on prend tous les codes postaux ayant nv dépot vide et zone depot=VILLE2 -> on inscrit ville2 ds nv depot
    on trie col VILLE2 ordre croissant pr avoir les codes postaux restants les plus proches
    et on ajoute ville2 ds colonne nv depot jusqu'à ce que le nv pw actuel soit >= objectif (c'est ce jusqu'à que je ne sais pas coder)

    puis prendre la prochaine ville avec delta min, ici : VILLE1
    comme c'est la dernière ville et que delta >0 je peux inscrire ds les lignes vides de colonne nv depot : ville1

    Infos supp/recap :
    On prend en premier le delta min et ainsi de suite
    Lorsque delta_villeX est <0 on doit prendre tous les codes postaux avec colonne_zone_depot = "VILLEX" et colonne_nv_depot="" -> inscrire ds col_nv_depot = "VILLEX"
    + le restant de pw donc = delta_villeX que l'on trouve en triant la colonne VILLEX, qui ont colonne_nv_depot="" et on inscrit ds col_nv_depot ="VILLEX" jusqu'à ce que nv pw actuel >= objectif

    Lorsque delta_villeX >0 on prends tous les codes postaux qui ont col_zone_depot="VILLEX" et colonne_nv_depot="" -> inscrire ds col_nv_depot="VILLEX"

    Une fois la ville faite, je modifie le delta par 0 pour trouver le prochain delta minimum à la nouvelle relance de la macro, j'aimerai bien trouver autre chose et pouvoir lancer la macro une seule fois pr avoir tout le résultat en 1 coup

    Je vous joins mon fichier excel, sachez que je devrais faire cette étape sur plus de 50 fichiers différents avec plus de 500 lignes, et le nombre de villes peut aller de 2 à une dizaine

    J'espère avoir été claire, sinon n'hésitez pas à me poser des questions.
    Je vous remercie d'avance à l'intérêt que vous porterez à ma requête !!
    Dans l'attente de vous lire bonne journée
    Fichiers attachés Fichiers attachés

  2. #2
    Membre chevronné
    Homme Profil pro
    Étudiant
    Inscrit en
    Novembre 2015
    Messages
    369
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 29
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Novembre 2015
    Messages : 369
    Par défaut
    Bonjour chloe,

    Si j'ai bien compris...

    Créer une boucle qui...

    1) Choisisse le dernier Delta (non utilisé) le plus petit
    2) Tant que l'objectif n'est pas atteint, rajouter à ta variable PW le prochain PW le plus élevé contenu dans la Ville associée au Delta.
    Si l'objectif est atteint et qu'il reste des villes dans VILLE3 (dans ton exemple), elles seront utilisées dans la prochaine boucle (VILLE2 ici)
    Si l'objectif n'est PAS atteint, ce sont les villes de VILLE2 avec le PW le plus élevé qui seront ajoutés à ta variable PW.

    Comment différencies-tu 2 PW identiques ? Je prends le cas de B2 et B3.

  3. #3
    Membre confirmé
    Femme Profil pro
    Étudiant
    Inscrit en
    Juillet 2018
    Messages
    56
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 29
    Localisation : France, Aisne (Picardie)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Transports

    Informations forums :
    Inscription : Juillet 2018
    Messages : 56
    Par défaut
    Bonjour
    J'ai répondu en dessous des idées principales

    1) Choisisse le dernier Delta (non utilisé) le plus petit
    'Pas le dernier mais juste le plus petit (non utilisé)

    2) Tant que l'objectif n'est pas atteint, rajouter à ta variable PW le prochain PW le plus élevé contenu dans la Ville associée au Delta.
    'Tant que l'objectif n'est pas atteint on rajoute des codes postaux qui ont la distance la plus proche de la ville du delta pour atteindre la nouvelle somme de pw >= a l'objectif

    Si l'objectif est atteint et qu'il reste des villes dans VILLE3 (dans ton exemple), elles seront utilisées dans la prochaine boucle (VILLE2 ici)
    'Oui si ils restent des codes postaux ils devront être pris plus tard (tous les codes postaux doivent être affectés à une VILLEX)
    Mais dans le sens que je sélectionne en partant du delta minimum négatif il ne peut pas rester de code postal de VILLE3 non sélectionnés vu qu'ils sont pris en priorité et qu'après on va chercher d'autres codes postaux les plus proches de VILLE3

    Si l'objectif n'est PAS atteint, ce sont les villes de VILLE2 avec le PW le plus élevé qui seront ajoutés à ta variable PW.
    'Non ce n'est pas rapport aux pw mais par rapport aux distances il faut sélectionner tjs les codes postaux les plus proches de la ville

    Comment différencies-tu 2 PW identiques ? Je prends le cas de B2 et B3.
    'Je n'ai pas besoin de les différencier du coup

    En fait je cherche à sectoriser un espace géographique
    J'ai 3 villes principales et je dois affecter les codes postaux au plus proche d'une ville en fonction de la répartition de leur poids (pw)

  4. #4
    Membre chevronné
    Homme Profil pro
    Étudiant
    Inscrit en
    Novembre 2015
    Messages
    369
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 29
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Novembre 2015
    Messages : 369
    Par défaut
    J'ai saisi la problématique,

    Je réfléchis à tout ça demain, tête reposée, ma journée est terminée

    Les idées seront plus claires après une bonne nuit de repos

    Bonne soirée à toi

  5. #5
    Membre confirmé
    Femme Profil pro
    Étudiant
    Inscrit en
    Juillet 2018
    Messages
    56
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 29
    Localisation : France, Aisne (Picardie)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Transports

    Informations forums :
    Inscription : Juillet 2018
    Messages : 56
    Par défaut
    D'accord merci beaucoup bonne soirée à toi !

  6. #6
    Membre chevronné
    Homme Profil pro
    Étudiant
    Inscrit en
    Novembre 2015
    Messages
    369
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 29
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Novembre 2015
    Messages : 369
    Par défaut
    Bonjour chloé,

    Alors, dans un premier temps je te conseillerai de trier ta table "Somme PW" par ordre décroissant au niveau des delta, cela facilitera la suite de la procédure selon moi.

    Une fois ta table triée, imaginons que tu l'aies renommé "t_sumpw"

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
     
    Private Sub Delta()
    Dim Cible As Range, VilleR As Range, LF As Range
    Dim Wb As ThisWorkbook
    Dim WbO As Worksheet
    Dim t1 As ListObject, t2 As ListObject
    Dim D As Long
    Dim i As Integer
     
    Set WbO = Wb.Sheets("INSEE")
    Set t1 = WbO.ListObjects("t_sumpw")
    Set t2 = WbO.ListObjects("t_db")
     
    For Each Cible in t.ListColumns(4).DataBodyRange
    'Colonne 4 = Delta triés par ordre décroissant
         i = 1
         'Initialisation de i
     
         Set LF = t2.HeaderRowRange.Find(Cible.Offset(0,-3).Value, lookin:=xlValues, lookat:=xlWhole)
         'Trouver VilleX dans les en-têtes de t_data
     
         t2.DataBodyRange.Sort Key1:=LF.Column Sort:=xlAscending
         'Effectue le tri croissant sur la colonne correspondant à la Ville saisie
     
         Do
             If t2.DataBodyRange(i,8).SpecialCells(xlCellTypeBlanks) Then
             'Si la cellule de la colonne G est vide alors...
                  D = D+t2.DataBodyRange(i,1)
                  'Delta = Delta + PW
                  t2.DataBodyRange(i,8) = Cible.Value
                  'Inscrit VilleX en Colonne G - la valeur ne sera pas vide donc logiquement plus prise à la prochaine boucle
             End If
             i = i + 1
         Loop Until D > t1.DataBodyRange(Cible.Row,3)
         'Boucle tant que le delta n'est pas dépassé
    Next Cible
    End Sub
    Je ne suis pas sûr que cela fonctionnera, alors dans le doute je te conseille de tester l'intérieur de la boucle "Do... Loop Until" sans la boucle, pour ne pas tomber dans une boucle infinie, cela serait dommage

    Si j'ai oublié un paramètre, n'hésite pas à me le faire savoir. Le code n'a pas été testé ^^'

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [XL-2016] Colorer la ligne jusqu’à la cellule sélectionnée
    Par iliesss dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 01/10/2018, 12h47
  2. [XL-2013] Somme avec decaler() "jusqu'à ce que"
    Par Steve Steve dans le forum Excel
    Réponses: 5
    Dernier message: 14/04/2017, 12h21
  3. Réponses: 5
    Dernier message: 29/08/2016, 09h05
  4. Réponses: 4
    Dernier message: 03/03/2008, 15h44
  5. Sélectionner ligne et Traiter
    Par PIEPLU dans le forum Macros et VBA Excel
    Réponses: 14
    Dernier message: 08/08/2007, 22h13

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo