Recherche de valeurs dans un tableau en VBA

[Raka]

Bonjour,

Je m'attaque au VBA (parce que je ne peux pas réaliser ce que je souhaite sans VBA, je pense... Si c'est le cas, merci de me le signaler, je n'ai absolument pas réussi !)

Et si je commence à comprendre et maîtriser les formules, le VBA va être une autre paire de manches.

Voici ce que j'ai :

Une plage "E2:AC354", contenant des valeurs diverses et variées, aléatoirement.
Une valeur max entrée manuellement en B2.

Ce que je souhaite faire :

Un bouton, pour commencer.

Une recherche en boucle dans la plage spécifiée, qui va chercher la valeur min (ou l'une des plus basses s'il y a des égalités), passe la case en vert (par exemple, RGB 0,255,0 ou peu importe, en couleur), ajoute cette valeur à une valeur temporaire (qui est donc comparée à celle en B2). Et on recommence en excluant les cases vertes (donc déjà utilisées) de la recherche.

La recherche s'arrête quand la valeur en B2 est atteinte (ou presque) par la valeur temporaire. Mais elle ne peut pas la dépasser ! C'est important. Lorsque plus aucune valeur du tableau E2:AC354 ne peut être ajoutée sans dépasser B2, its the end, Wend je suppose, la fin de la boucle.

J'avoue être un peu perdu par tout ça. J'ai fait quelques essais, mais rien de concluant.

Des idées d'une structure VBA pour faire ça ?

En résumé, j'ai besoin de pouvoir atteindre une valeur entrée manuellement (sans la dépasser) en me servant des plus petites valeurs du tableau.

Alors, j'avais le début !

Quelque chose comme ça.

Dim zone As Range
Dim ligne As Range
Dim cellule As Range
Set zone = Range("E2:AC354")

Dim Somme As Integer
Dim somtemp As Integer

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

Mais du coup, je comptais continuer par un While, depuis 2h je suis dessus, et rien ne fonctionne comme je veux ^^ J'ai tenté un For Each... qui se sert des lignes et des colonnes définies plus haut, mais pas moyen non plus d'avoir ce que je veux.

Alors j'en viens à demander de l'aide.

Merci pour votre compréhension de mon charabia Et pour votre aide.
Si besoin d'éclaircissements, je suis là.

 

[njhub]

Bonjour Raka

Deux solutions par formules dans le fichier joint

 

[Raka]

Merci !

Mais je ne suis pas sûr d'avoir compris (ou d'avoir été assez clair.)

Dans l'exemple proposé, la case où l'on rentre 79, je voudrais y rentrer, par exemple, 2 000 000.

Et la boucle est supposée me chercher les valeurs les plus petites, une à une, jusqu'à arriver à 2 000 000, sans le dépasser. Et colorer tout ça

Elle va par exemple commencer par 1, puis 2, puis 3, puis 3.5, puis 7, puis 14... Jusqu'à ce que la somme de tout ça atteigne 2 millions sans le dépasser.

En tout cas, le fichier a un souci, dès que je clique sur activer la modification pour l'étudier, tout passe en #NOM et en formules inconnues.

 

[Raka]

Au niveau VBA, après de maints tests, j'en suis arrivé à quelque chose comme ça :
(oui, il y a des lignes inutiles, résidus des anciens tests...)

Sub Bouton1_Cliquer()

Dim zone As Range
Dim zoneRb As Range
Dim ligne As Range
Dim cellule As Range
Dim c As String
Set zone = Range("E2:AC354")
Set zoneRb = Range("AI2:BG354")

Dim valeur As Integer
Dim Somme As Integer
Dim somtemp As Integer

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

Dim i As Integer
Dim j As Integer
Dim Max
Dim TempSomme As Integer

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

For j = 5 To 30
For i = 2 To 354
   If IsNumeric(Cells(i, j).Value) 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(0, 255, 0)
    Else
         Cells(i, j).Interior.Color = RGB(255, 0, 0)
    End If
   End If
Next i
Next j

End Sub

Mais le débogueur se met en route et me surligne Somme = Range("B2").Value.

Une raison ?

Edit : Bon, il semble que j'aie réussi à corriger ce qui était probablement un "bug" en redémarrant.

 

[Raka]

Bon.
J'y suis.
J'ai réussi tout seul, finalement... L'aide était très gentille mais ce n'était pas ce dont j'avais besoin
Merci quand même pour le geste, j'apprécie.

Voici ce que j'ai :

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
Dim NbLvl As Integer


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

For j = 5 To 30
For i = 2 To 354
   If IsNumeric(Cells(i, j).Value) 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(135, 233, 144)
           NbLvl = NbLvl + 1
        Else
             Cells(i, j).Interior.Color = RGB(231, 62, 1)
        End If
    End If
   End If
Next i
Next j

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

End Sub

Donc ici, on a une valeur source, un tableau contenant des données (défini par i et j en hauteur/largeur) et une recherche qui va prendre ces valeurs une à une pour ajouter les plus petites jusqu'à atteindre la valeur source (Somme, ici, inscrite manuellement en B2.)

Les cases utilisées seront alors colorées en vert, celles qui ne l'ont pas été en rouge.

Maintenant, deuxième étape, et je bloque à nouveau, et sévèrement ce coup-ci. Je veux bien avancer tout seul et j'aime ça, mais y a des moments, c'est juste pas possible ^^

Donc voici mon nouveau problème :

Pour chaque LIGNE de ce tableau de valeurs (dont voici un exemple)

il y a une ligne dans un deuxième tableau, à droite de celui-ci.
Dans ce deuxième tableau, certaines lignes contiennent des valeurs, d'autres sont totalement vides.

Ce que j'aimerais réaliser ici et ajouter à mon code, c'est cela :

La recherche (i,j) est autorisée à s'effectuer également sur le deuxième tableau, sous certaines conditions :
- il faut que (et c'est individuel à chaque ligne !) le 2ème tableau comprennent des valeurs IsNumeric, donc, plutôt que d'être vide "".
- il faut que dans le premier tableau, la valeur d'une cellule A soit supérieure à une autre cellule B (mettre ici ce qu'on veut comme cellules, je peux les changer à volonté), OU que la dernière valeur (de gauche à droite donc) sélectionnée en vert soit égale à la valeur de cette même cellule B précédente.

Et une fois que la recherche a commencé à colorer une case en vert dans le deuxième tableau, pour chaque ligne toujours, elle est obligée de continuer dans ce deuxième tableau...

C'est vraiment compliqué... Je ne m'en sors pas aussi bien que pour mon première travail

 

[_Thierry]

Bonsoir @Raka, le Forum

C'est très bien d'avoir pu résoudre la première partie tout seul comme un grand, c'est comme ceci qu'on apprend !

Peux-tu faire un fichier exemple bien représentatif de la structure exacte, mais sans données confidentielles.
Et avec un exemple de ce que tu veux obtenir exactement aussi que tu mettras en fond jaune par exemple...

Car là avec un screenshot je ne pense pas qu'on puisse t'aider...

Bonne soirée
@+Thierry

 

[Raka]

Ah oui oui je ne demande qu'à apprendre tout seul ! Mais parfois, je vois trop gros '^^

Je fais ça dans les 20 min ! Il n'y a rien de confidentiel, je fais ça essentiellement, justement, pour apprendre, et c'est purement ludique, je me sers de données d'un jeu vidéo, ni plus ni moins

 

[Raka]

Here is my example with explications about what I'm trying to achieve.
If something's not clear, just ask

EDIT : MAIS MERDE je sors d'un discord anglais, désolé... Reflexe.

Donc, voici l'exemple de ce que je veux atteindre. Si quelque chose n'est pas clair, je suis là pour expliquer.

 

[_Thierry]

Re Raka

Merci pour le fichier....
Mais Arf fin de journée et de semaine, je ne comprends rien mais rien du tout même avec le tableau explicatif LoL

Non désolé, je laisse ceci à des esprits plus vifs !
Bon Courage
@+Thierry

 

[Raka]

Merci quand même !
Je m'en doutais

Quand on ne connait pas, c'est pas forcément simple à appréhender, et puis j'explique pas forcément bien...

Pour faire simple, pour chaque ligne individuellement, la recherche peut se faire sur le deuxième tableau sous deux conditions, et une fois le deuxième tableau utilisé (s'il l'est et toujours individuellement, par ligne) la recherche ne peut pas retourner dans le premier.

C'est le résumé le plus court possible

 

[_Thierry]

Toujours pas compris..; LoL

Pour Archer par exemple en linéaire il n'y a rien pour lui dans le second tableau...

@+Thierry

 

[Raka]

Bon.

J'ai résolu le souci autrement.
Seulement, je me heurte maintenant à un autre problème de codage, que je n'arrive pas à tourner en ma faveur.

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

Pour info : 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 (qui se trouve directement à droite d'une cellule contenant un texte) puisse l'être.

Mais du coup, je crois que ma méthode 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 ?