VBA ; recherche de valeurs mini sur une plage de façon dynamique

[Raka]

Bonjour.

Je recrée un sujet parce que ce que je recherche là est déjà un peu trop éloigné du précédent, qui, en soi, est résolu.

Donc voici ce que j'ai :

Un problème de codage que je n'arrive pas à faire tourner en ma faveur.

Je dispose d'un tableau aux données variables selon l'implémentation d'un json.

Mon but : trouver le nombre MAXMUM de cellules dont les valeurs additionnées permettent d'arriver à une valeur entrée manuellement. Donc, trouver les valeurs les plus faibles.

Handicap : une cellule ne peut pas être sélectionnée si celle de gauche ne l'est pas avant.

Mon idée : créer un code qui va chercher les valeurs les plus faibles et non-colorées dans le tableau, regarder si chacune d'entre elle, additionnée au total des précédentes, dépasse la valeur manuelle à ne pas dépasser. Si elle ne dépasse pas, verdir la case et recommencer, sinon la rougir et fin du truc.

J'ai ce code, qui fonctionne : on entre un nombre en B2, et il cherche la plus petite valeur, colonne par colonne, et les additionne jusqu'à atteindre la valeur B2 sans la dépasser. Les cellules non-atteignables (parce que valeur donnée en B2 serait dépassée) se colorent ensuite en rouge.

Sub Bouton1_Cliquer()

Dim valeur As Integer
Dim Somme As Long

valeur = 0
Somme = Range("B2").Value

Dim i As Integer
Dim j As Integer
Dim TempSomme As Long


Min = Cells(2, 2).Value
TempSomme = 0

For j = 7 To 55
For i = 2 To 354
   If IsNumeric(Cells(i, j).Value) Then
    If Not IsNumeric(Cells(i, j - 1).Value) Or Cells(i, j - 1).Interior.Color = RGB(35, 233, 144) Then
        If Cells(i, j).Value <> 0 Then
            If Cells(i, j).Value + TempSomme < Min Then
    '           Min = Cells(i, j).Value
            TempSomme = TempSomme + Cells(i, j).Value
            Cells(i, j).Interior.Color = RGB(35, 233, 144)
            NbLvl = NbLvl + 1
            Else
             Cells(i, j).Interior.Color = RGB(231, 62, 1)
            End If
        End If
    End If
   End If
Next i
Next j

Cells(12, 2).Value = NbLvl
Cells(24, 2).Value = TempSomme

End Sub

La ligne If Not est là pour s'assurer que les valeurs à droite des valeurs rouges ne soient pas sélectionnées tout en s'assurant que la valeur la plus à gauche du tableau puisse l'être (non visible sur ce screen mais elle se trouve collée à droite d'une colonne contenant des noms, donc non numériques).

Mais du coup, je crois que ma méthode d'approche n'est pas la bonne. Le but étant de toujours sélectionner les cases contenant la valeur la plus faible, et sachant que sur une même colonne, il peut y avoir des valeurs aléatoires, je crois que je n'ai pas pris le bon chemin... En effet :

La macro choisit la case à 4.000 avant de choisir celles à 2000, ce qui n'est pas bon du tout.

Plutôt que de passer le tableau ligne par ligne, colonne par colonne comme je l'ai fait, n'existe-t-il pas un moyen simple de faire un parcours d'arbre ? Ou si c'est trop compliqué en VBA, faire en sorte que la macro parcoure le tableau de la sorte :

Il faudrait que je trouve un moyen de remplacer "regarder colonne" par "regarder la valeur numérique non-colorée le plus à gauche de chaque ligne." Dans la plage couverte par i:j bien sûr.

Cette méthode aurait le mérite de comparer les cellules 2000 avec la cellule 4000, toutes trois "les plus à gauche", et de colorer les 2000 avant la 4000, logique que je cherche.

Pour donner un exemple concret :

Dans le tableau ci-dessus, la première occurrence de la recherche (sur tableau totalement blanc donc) devrait comparer les valeurs 3000, 80, 2000 et 4000. Elle va donc colorer 80, puis comparer les valeurs 3000, 120, 2000 et 4000. Colorer 120, etc...

Est-ce simple à faire ?

 

[Raka]

Bon, encore quelques heures de passées dessus en vain.
J'ai tenté des essais avec du ligne par ligne "For each", également...
Du IsEmpty qui me renvoie toujours des erreurs diverses et variées...
Je crois que j'arrête les frais là, si personne n'a d'idée

 

[Raka]

Je joins un exemple concret, au cas où... Dans un soupir désespéré
Peut-être que ça sera plus clair sur ce qui fonctionne mal.

A la valeur entrée, ça fonctionne (la théorie est bien là). Mais si on entre par exemple 150 000 en B2, on se rend compte qu'il choisit des valeurs de 4000 alors qu'il reste une 1500 et une 2000, qu'il ne peut plus choisir ensuite car la valeur en B2 est déj presque atteinte.

Tout ça parce que la macro travaille colonne par colonne, en cherchant le minimum de chaque colonne jusqu'à avoir choisi chaque valeur, avant de passer à la suivante, au lieu de faire le minimum de "chaque valeur la plus à gauche".

 

[eriiic]

Bonjour,

Je ne comprends pas bien...

une cellule ne peut pas être sélectionnée si celle de gauche ne l'est pas avant.

Ce qui implique de prendre obligatoirement la 1ère valeur, et qu'elles soient toutes consécutives.
A partir de là plus tellement de choix possible.
A mon avis il manque quelque chose dans l'explication.
eric

 

[Raka]

Ca signifie que les valeurs doivent être sélectionnées dans l'ordre gauche-droite, mais pour chaque ligne individuellement.

Dans l'image un peu plus haut par exemple, la ligne contenant 80-120-200-300, ces valeurs doivent être sélectionnées dans cet ordre.

Dans ma macro c'est ce qui se passe (puisque je parcours le tableau selon i et j donc forcément...
Mais c'est justement là le souci :la macro parcourt chaque colonne en entier pour la sélectionner en entier si possible avant d'attaquer la suivante.

Par exemple ici, toujours sur cette image, les valeurs 2000 en rouge auraient dû être vertes, et la 4000 rouge. Parce que les 2000 auraient du avoir priorité sur la 4000 (premières sélectionnables de leur lignes (puisque celles plus à gauche l'ont déjà été ou n'existent simplement pas) et inférieures à 4000).

 

[Raka]

En gros, ce que je cherche, c'est changer ma recherche quadrillée i j par "chercher au sein de chaque valeur la plus à gauche de sa propre ligne, dans une case non-colorée". Puis on colore cette case pour l'exclure de la recherche (sa cellule de droite devenant donc la nouvelle "plus à gauche" pour cette ligne), et on recommence jusqu'à ce que TempSomme atteingne B2.

Je pense que je ne peux pas résumer mieux que ça

Ca devrait choisir dans cet ordre-ci.

 

[eriiic]

Désolé, je suis complètement étanche là.
Un autre te comprendra sans doute mieux
eric

 

[Raka]

Pour être clair, j'ai la théorie de ce qu'il me faut comme logique.
Mais impossible, pour mes propres connaissances en VBA et mes recherches, de le coder proprement après toute une journée de recherche.

En gros, ce qu'il me faut, c'est ça :

Somme = B2
SommeTemporaire = 0

Début de boucle

- Chercher parmi la première valeur numérique dans une case non-colorée de chaque ligne (dans la plage G2:AT354) celle qui est la plus petite (en gros, si la première valeur d'une ligne est 2 et la première valeur de la ligne en-dessous est 10, ça choisira la cellule à valeur 2.)
- Ajouter cette valeur à SommeTemporaire à condition que SommeTemporaire + cette valeur < Somme.
- colorer la case en vert si elle a été ajoutée (pour l'exclure de la boucle suivante), en rouge si elle ne l'a pas été.
- quand aucune valeur de cette boucle ne peut être ajoutée à SommeTemporaire, sortir de la boucle.

Fin boucle.

Est-ce que c'est codable facilement pour vous ? Pour moi, absolument pas.

Je vais faire un walkthrough pas à pas de ma logique parce que j'ai l'impression de m'exprimer comme un pied

 

[Raka]

On a ceci :

Je rentre dans la valeur B2 = 150.000. Manuellement.

Je lance la macro. Elle doit effectuer une 1ere boucle en cherchant la plus petite valeur parmi celles-ci :

qui sont toutes la première valeur numérique au fond incolore de leur lignes respectives (je les ai colorées en jaune juste pour les montrer, elles restent incolores normalement, pour la recherche...)
La plus petite valeur est 50.
Elle colore 50 en vert, et ajoute 50 a la somme temporaire (qui ne doit pas dépasser Somme inscrite en B2)

Puis, deuxième boucle, qui recommence, parmi les premières valeurs numériques de chaque ligne, en excluant celles déjà colorées. (en vert lorsque ça a été ajouté à somme temporaire, en rouge lorsque ça n'était pas possible parce que ça aurait excédé B2)

On se rend compte ici que la case déjà choisie avant a été exclue de la recherche, donc la case numérique "la plus à gauche" de sa ligne devient ce 80.

Et la boucle continue ainsi jusqu'à ce que la somme en B2 soit atteinte, que plus aucune valeur ne puisse être ajoutée sans la dépasser.

J'espère que c'est plus clair ainsi :/

Je suis persuadé d'avoir pris la mauvais direction avec mon code en i,j.

 

[eriiic]

Ah, ça semble plus clair là.
Et si ton 80 aurait été en 1er utilisable sur une autre ligne on aurait été le prendre aussi ?
Si plusieurs possibilités on prend n'importe laquelle ?

Au départ il faut faire une raz des couleurs ? Sinon les rouges mis vont devenir bloquants

 

[Raka]

Oui. Puisque de toute façon, la boucle suivante va prendre l'autre valeur identique.
Si jamais la valeur manuelle se trouve atteinte "entre les deux", ben ça en prend un et pas l'autre, c'est pas bien grave.
Edit : ah non j'ai mal lu la question. Enfin, j'ai lu la moitié mdr

Pour le doublon, tu peux te rendre compte sur la dernière image qu'il y a un autre 50 dans la zone jaune. Donc la deuxième boucle va le choisir lui, puisqu'il est la valeur la plus basse des "valeurs les plus à gauche". Et la troisième boucle va effectuer la même recherche, mais avec les deux 50 en vert, et les deux 80 à leur droite dans la "zone de recherche", puisqu'ils seront les valeurs non-colorées les plus à gauche de leurs lignes.

En gros, si le 80 se trouve dans une des cellules "la plus à gauche mais pas colorée", alors oui, on le prend. Il n'y a absolument aucune condition de ligne, tant que c'est la valeur la plus basse qui respecte les deux conditions :

- Cellule numérique non-colorée la plus à gauche de sa ligne
- Valeur de B2 n'est pas dépassée par l'ajout de cette valeur.

Du coup, tu peux te rendre compte que mon code n'est pas bon pour ce que je veux faire. Il quadrille trop. Il prend les valeurs de chaque colonne jusqu'à ce que la colonne soit totalement colorée, avant de passer à la suivante, ce qui n'est pas bon.

Pour info, le Range de recherche est G2:AT354.

Du coup, j'ai oublié une condition. Je m'en rends compte.
Si jamais la somme entrée en B2 est si importante qu'on parvient à sélectionner l'intégralité des valeurs du tableau sans toutefois la dépasser, ben il ne se passe rien de plus; les cellules numériques deviennent toutes vertes et puis voilà.

 

[Raka]

Ah, ça semble plus clair là.
Et si ton 80 aurait été en 1er utilisable sur une autre ligne on aurait été le prendre aussi ?
Si plusieurs possibilités on prend n'importe laquelle ?

Au départ il faut faire une raz des couleurs ? Sinon les rouges mis vont devenir bloquants

La raz des couleurs se fait sur un autre bouton, DL mon exemple un peu plus haut.
Pas besoin de s'embêter avec ça dans la macro.

 

[eriiic]

"la plus à gauche mais pas colorée",

donc dans ce cas de figure ou le mini est 120, interdiction de prendre I2, il faut absolument prendre H2 ?

La raz des couleurs se fait sur un autre bouton, DL mon exemple un peu plus haut.
Pas besoin de s'embêter avec ça dans la macro.

La question n'est pas que ça m'embête ou pas.
C'est surtout pour savoir si tous les nombres sont disponibles au départ ou si il arrive de démarrer avec des nombres déjà pris par ailleurs.
Tel que je l'ai en tête les couleurs ne me servent pas, c'est juste pour toi visualiser les choix.

 

[Raka]

donc dans ce cas de figure ou le mini est 120, interdiction de prendre I2, il faut absolument prendre H2 ?

Dans ce cas de figure, les deux 120 sont tous deux "la valeur non-colorée la plus à gauche de leur ligne", donc non, l'un ou l'autre fait l'affaire (La boucle suivante va très logiquement choisir l'autre de toute façon, s'il reste encore du rab de valeur jusqu'à B2).

C'est vraiment la valeur la plus à gauche "de chaque ligne". Pas une globalité.

C'est surtout pour savoir si tous les nombres sont disponibles au départ ou si il arrive de démarrer avec des nombres déjà pris par ailleurs.

Je raz toujours avant, donc pas de souci. Si lancer la macro sans raz fait bug le truc, on s'en fout. On peut partir du principe que le tableau est blanc, quoi qu'il arrive, en lançant la macro.

Moi les couleurs vont me servir par la suite, quand j'aurai besoin de faire un décompte de certaines valeurs. Il me faut impérativement le vert et le rouge (après, les choix RGB que j'ai faits sont purement esthétiques, hein, c'est un joli vert et un joli rouge, tu peux prendre 0.255.0 et 255.0.0 si tu veux

 

[mapomme]

Bonsoir,

Un essai pour ce que j'en ai compris . C'est pas très rapide, le résultat est plus éloigné de la cible mais le nombre de cellules est toujours plus grand.