XL 2010 [VBA] Recherche, comparaison et sélection de valeurs

demonsares · 29 Septembre 2016

Bonjour à toutes, à tous et le forum !

Je travaille actuellement sur un projet d'automatisation des fichiers Excel de vérifications d'instrument de mesure. Etant le plus jeune et le plus "dégourdit" en Excel / Informatique j'ai reçu la charge de se projet.
Je veux réaliser ceci :
L'utilisateur rentre le numéro d'identification de la balance.
La macro réalise ceci :
- Rentrer la plage maximale de la balance à l'endroit prévue à cette effet. (Etat : Validé),
- Rentrer les valeurs nominales des mesures à réaliser pour la vérification (Etat : Validé),
- Récupérer les valeurs réelles (des étalons de poids) pour la vérification (Etat : Galère total !)

1 - A l'heure actuel je suis bloqué sur cette partie. Pour expliquer plus simplement, prenons pour exemple une valeur nominale de vérification de 5g.

Ce que la macro doit faire :

La macro doit venir chercher la valeur réelle de l'étalon de vérification. Pour notre cas, la macro ouvre l'onglet "Masse étalon", elle doit pouvoir venir chercher tous les étalons ayant une masse nominale de 5g. Puis réaliser une comparaison tel que :

- Poids étalon 1 : 5.006 g
- Poids étalon 2 : 5.001g.

Le poids étalon 2 est donc meilleure au niveau de sa valeur réelle.
La macro doit donc prendre cet étalon, avec cette valeur réelle. C'est cette valeur réelle qui doit être placé dans la cellule D47 de l'onglet "Balance".

2 - La difficulté au dessus. Je dois pouvoir prévoir des modèles. C'est à dire, si la Balance nommé XYZ à une valeur de vérification à 700g. Alors la macro doit pouvoir chercher les 2 meilleurs poids étalons de 500g et de 200g, en faire la somme et rentrer cela dans la bonne cellule.

Je vous met en pièce jointe mon fichier Excel, qui j'espère vous aidera a comprendre ce que je souhaite.

Voici ce que j'ai fait à l'heure actuelle niveau Macro :

Sub Mesure_Verification_Balance()

a = InputBox("Rentrer le N° d'identification de la balance à vérifier : ")

Sheets("Référencement balance").Activate
Dim b As Range
Set b = Range("A1:A56660").Find(a, , xlValues, xlWhole, , , False)
If b Is Nothing Then MsgBox "Ce numéro d'identification n'existe pas"

C = Columns(1).Find(a, LookIn:=xlValues, lookat:=xlPart).Row

d = Cells(C, 4).Value
e = Cells(C, 5).Value
f = Cells(C, 6).Value
g = Cells(C, 7).Value
h = Cells(C, 8).Value
i = Cells(C, 9).Value

Sheets("Balance").Activate
Range("M24") = d
Range("C47") = e
Range("C48") = f
Range("C49") = g
Range("C50") = h
Range("C51") = i

###########A partir d'ici je suis bloqué#########

Sheets("Masse étalon").Activate
For k = 1 To 50
j = ActiveSheet.Columns(2).Find(e, LookIn:=xlValues, lookat:=xlWhole).Row
MsgBox j
Range(Cells(k, 6)) = j

Next
End Sub

Modeste · 29 Septembre 2016

Bonjour demonsares,

J'ai lu ton message. N'y comprenant rien, je me suis contenté d'attendre ce que d'autres en diraient.
... Manifestement, ils n'en disent pas plus que moi ... La première partie, j'ai fini par comprendre, en décortiquant ton code. Là où tu dis être bloqué, même ton exemple destiné à clarifier reste obscur. Tu dis: "
La macro doit venir chercher la valeur réelle de l'étalon de vérification. Pour notre cas, la macro ouvre l'onglet "Masse étalon", elle doit pouvoir venir chercher tous les étalons ayant une masse nominale de 5g. Puis réaliser une comparaison tel que :

- Poids étalon 1 : 5.006 g
- Poids étalon 2 : 5.001g."
dans la feuille "Masse étalon", je trouve une colonne "Valeur nominale"; je suppute qu'on parle de la même chose?
Je ne trouve qu'une seule fois la valeur 5g!? ... D'où sortent tes deux poids étalon?

Je ne connais strictement rien à ton travail (d'autres non plus, j'en ai peur). Donne des indications compréhensibles par des profanes, si tu veux qu'on arrive à traduire ta demande en proposition.

demonsares · 29 Septembre 2016

Bonjour Modeste, tout d'abord merci d'avoir répondu et d'avoir pris le temps de regarder mon problème même si tu n'as rien compris.
Je vais tenter de mieux m'expliquer, il faut dire que je suis dans mon truc et je m'exprimes mal. Toutes mes excuses.

Dans le fichier Excel en pièce jointe, j'ai simplifié le modèle. Ce sera déjà beaucoup plus clair je pense.

1 - On lance la macro via le bouton : BALANCE
2 - On rentre le numéro d'identification de la balance. Ici c'est M6316
3 - La macro ouvre l'onglet "Référence balance". Elle vient prendre les valeurs des 5 mesures que devra réaliser l'opérateur.
4 - La macro affecte les valeurs des 5 Mesures dans le tableau présent dans l'onglet "Balance" en colonne C.
############## Jusque là c'est bon normalement #############

5 - La macro ouvre l'onglet "Masse étalon". Elle doit chercher les lignes pour lesquelles en colonne 2 on trouve la valeur exacte de 5.
Là j'y arrive, mais si tu lances la macro tu vas voir quelle affiche 16 ! Ce qui est parfait car c'est la ligne du premier "5" qu'elle a trouvé. Mais le soucis c'est qu'il y a un "5" juste après ! La macro devrait sortir aussi le "5" suivant mais non elle s'arrête.

6 - En fait la macro doit prendre la valeur nominale de la première mesure du tableau de l'onglet "Balance".
Elle doit chercher dans l'onglet "Masse étalon" toutes les lignes qui ont cette valeur nominale.

7 - Dès lors la macro doit pouvoir comparer les valeurs réelles avec leurs valeurs nominales. Dans notre cas comparer la valeur 5.000009 avec 5 et la valeur 5.0027 avec 5 et prendre la valeur la plus proche !

8 - Dès que la valeur la plus proche (soit ici 5.000009) est prise elle doit être placée dans le tableau (onglet "Balance" en position D5.

9 - La macro doit ensuite réaliser cette étape pour chaque mesure ! Soit 5 puis 2000 puis 3000 puis 5000 puis 5500. Là où ça se complique c'est que par exemple 3000 n'existe pas dans le tableau onglet "Masse étalon". Donc je dois ordonner a la macro de réaliser la somme de la meilleure masse étalon à 1000 et de la meilleure masse étalon à 2000. Prendre leurs valeurs réelles et les additionner. Ce qui ferait :
1000,0015 + 2000.0054 = 3000.0069 et doit se ranger cellule D7 du tableau onglet "Balance"

Modeste · 29 Septembre 2016

Bonsoir,

M'étonne pas que tes collègues t'ont refilé la patate chaude

Blague à part, simplifier le document était une bonne idée: même moi j'ai à peu près compris!

Par contre, autant je pourrais encore faire une proposition pour la première partie de la demande, autant je me déclare totalement hors jeu pour la fin! Ça revient à déterminer les combinaisons de montants à additionner ... et le pire c'est qu'il peut y en avoir plusieurs, parmi lesquelles il faudra ensuite déterminer celle qui fournira les valeurs réelles qui approcheront le plus du Graal! Et tu n'as pas encore expliqué comment atteindre les 5.500

J'y jetterai un œil, mais d'emblée je dirais que ce n'est absolument pas pour moi ce genre de "défi intellectuel" (pour ne pas dire "prise de tête"!!)

demonsares · 29 Septembre 2016

Bonsoir Modeste !
Exactement, en tant qu'alternant on se tape tous les trucs qui sont laissés de côté

Blague à part, si tu as compris le début tu peux peut-être m'aider !
Concernant les combinaisons, je prends toujours les mêmes.
C'est un paramètre que je vais fixer, voici divers exemples :

- 3000g = 1000g + 2000g
- 5500g = 5000g + 500g.
En gros je fais des combinaisons des poids que l'on a mais en essayant qu'il y en ai le moins.
MAIS je fois avouer ça part vite en ***** ... sucette pour certaines valeurs. ^^

Modeste · 30 Septembre 2016

Bonjour,

Tu m'as mal compris: ce n'est pas juste que j'ai la flemme: je ne peux pas répondre à ce type de question (même si ma vie en dépendait )

Tout ce que je peux proposer est en pièce jointe: quand la valeur nominale n'est pas trouvée, j'indique juste un "-" ... et il faudrait déjà vérifier si le reste fonctionne comme tu l'imaginais!

La solution que tu proposes ne saurait fonctionner: dans l'exemple que tu as déposé, il n'y a ni 5000g, ni 500g Il faut que tu détermines les règles à suivre (ou alors, au départ de mon fichier, tu complètes le reste "à la main")

... Mais encore une fois, la réponse ne pourra venir que de quelqu'un d'autre que moi! ... Avis aux amateurs

demonsares · 30 Septembre 2016

Bonjour Modeste !

C'est tout à fait normal que tu n'es pas 500g ou 5000g dans le fichier car je ne les ai pas mit. J'ai voulu réduire et prendre l'exemple de 5g. Sinon avec tous les tableaux cela devient dur à comprendre.

En pièce jointe il y a le fichier. Avec l'intégralité des Masses étalons !!!
Si tu cliques sur le bouton "BALANACE", la macro demande l'identification de la balance. Tu met : M6316.
Là tu vas observer que la colonne valeur nominale de C5 à C9 va obtenir les valeurs de la plage E2 à I2 de l'onglet "Référencement balance" puis le nombre 23 va apparaître en MSGBOX.
Là c'est tout à fait "normal". Le nombre 23 correspond à la 23ième ligne de la colonne B de l'onglet "Masse étalon". Ce qui correspond à la valeur 5g donc la première valeur nominale du tableau dans l'onglet "Balance".

Mon soucis est le suivant :
Après cela la macro s'arrête, alors que moi j'aimerai qu'elle continue et qu'elle me sorte toutes les lignes de la colonne B de l'onglet "Masse étalon" ayant comme valeur 5.
C'est à dire que la macro sort les valeurs 23 et 24 !
En fait ca va me permettre d'obtenir la ligne ! Donc après en utilisant Cells (Ligne, colonne) je vais pouvoir mettre dans une variable les valeurs réelles. Ensuite je veux comparer les valeurs réelles au valeurs nominales et prendre la meilleure.

Ce qui donnerait :
Macro => 23 et 24
j = 23 et k = 24

(Là il faudrait quelque chose qui soit une boucle ! Ca ne doit pas être fixe car on peut avoir 1 comme 2 comme 3 valeur nominale identique)
l= Cells(j,3).Value (On affecte la valeur réelle à une variable)
m = Cells(k,3).Value (On affecte la valeur réelle à une variable)

(Pareil ici il faudrait que ça s'adapte en fonction du nombre de valeurs nominales trouvées)
n = abs(l - e)
o = abs(m-e)

(Ceci permet de prendre la valeur réelle la plus proche de la valeur nominale. Pareil ici il faut comparer les écarts, c'est à dire les variables "n" et "o" entre eux et prendre le plus petit ! Donc le plus proche de la valeur nominale)
If n > o Then
Sheets("Balance").Activate
Range("D5") = o

If o > n Then
Sheets("Balance").Activate
Range("D5") = n

DU COUP... Avec cela je vais avoir la meilleure valeur réelle, c'est à dire la plus proche de la valeur nominale.
Ensuite je vais me débrouiller pour qu'à la fin de la macro, celle ci indique à l'utilisateur toutes les références des masses étalons à utiliser !

Modeste · 30 Septembre 2016

Re,

Ôte-moi d'un doute: tu as remarqué qu'en exécutant ta macro Balance, puis la mienne, la seconde venait compléter la première?
En clair, clique sur ton bouton "Balance" et ensuite sur le bouton "macro Modeste"

Si tu veux réunir les deux en une seule (avec simplification de la tienne au passage), ça donnerait:

VB:

Sub Balance()

a = InputBox("Rentrer le N° d'identification de la balance à vérifier : ")

Dim b As Range
With Sheets("Référencement balance")
Set b = .Range("A1:A56660").Find(a, , xlValues, xlWhole, , , False)
If b Is Nothing Then MsgBox "Ce numéro d'identification n'existe pas": Exit Sub
.Cells(b.Row, 5).Resize(1, 5).Copy
Sheets("Balance").Range("C5:C9").PasteSpecial Paste:=xlPasteValues, Transpose:=True
End With

Set src = Sheets("Masse étalon")
With Sheets("Balance")
For lig = 5 To .Cells(Rows.Count, 2).End(xlUp).Row
  Set trouve = src.[B2:B500].Find(what:=.Cells(lig, 3), LookIn:=xlValues, lookat:=xlWhole)
  If Not trouve Is Nothing Then
  tmp = trouve.Offset(0, 1)
  ligne = trouve.Row
  premAdresse = trouve.Address
  Do
  Set trouve = src.[B2:B500].FindNext(trouve)
  titi = Abs(trouve.Offset(0, 1) - trouve)
  toto = Abs(tmp - trouve)
  If Abs(trouve.Offset(0, 1) - trouve) < Abs(tmp - trouve) Then ligne = trouve.Row
  Loop While Not trouve Is Nothing And trouve.Address <> premAdresse
  .Cells(lig, 4) = src.Cells(ligne, 3)
  Else
  .Cells(lig, 4) = "-"
  End If
Next lig
End With
End Sub

demonsares · 30 Septembre 2016

Bonjour MODESTE !

En effet j'ai fait le test en séparé mais je n'avais aucun résultat !
Donc j'ai pris la dernière MACRO que tu as mis sur ce sujet, je l'ai testé... Ca fonctionne et réalise parfaitement ce que je souhaite.
Par contre pour les empilements ce n'est pas encore réalisé et c'est normal c'est de ma faute.
En pièce jointe, tu vas pouvoir retrouver le fichier sur lequel nous travaillons.
Je t'ai ajouté l'onglet "Les empilements", tu as dedans toutes les masses étalons avec leurs numéro d'identification, leurs valeurs nominales et réelles. Juste à la droite il y a les empilements pour chacune mesure !

Sinon, ta macro est merveilleuse pour le moment, mais je dois avouer que certaines lignes je ne comprends pas. Sachant que je vais devoir l'expliquer (très probablement), j'aimerai des explications quand à ces lignes :

1 / Ici je ne comprends pas le resize ? A quoi sert-il ? Je pense c'est pour intégrer les valeurs des mesures dans le tableau mais je ne suis pas du tout sur.

[code\]
If b Is Nothing Then MsgBox "Ce numéro d'identification n'existe pas": Exit Sub
.Cells(b.Row, 5).Resize(1, 5).Copy
[code\]

2 / A quoi sert le "Set src" ??
[code\]
Set src = Sheets("Masse étalon")
[code\]

3 / Bon en fait toute ta partie que tu as faite... Je ne comprends pas les passages...

Ce que j'ai compris :
Tu as une boucle FOR allant de 5 jusqu'à la dernière ligne écrite.
Tu cherches les cellules ayant la valeur "5" de la plage B2 a B500
Si tu trouves pas ça ne fait rien
Et après là je suis perdu avec l'offset et l'adresse de la cellule, qui je pense, est entrée dans une variable lorsque tu trouves la valeur "5"
et après le néant...

Merci encore pour ton investissement !!!

Modeste · 30 Septembre 2016

Bonsoir,

Comme annoncé, je ne suis pas compétent pour la suite. Ton tableau des "empilements" ne m'aide en rien. Je ne sais même pas comment le lire, ni comment l'utiliser! Confusément, je sens que la solution n'est pas là, mais suis incapable d'expliquer pourquoi.
Bref, je commenterai le code fourni, si ça peut aider, mais je n'irai pas plus loin!

Attends de voir si d'autres prennent le relais. Si pas, ouvre un nouveau fil en "épurant ta demande". Concentre-toi sur l'essentiel: la recherche d'une valeur dans un tableau et, si elle n'existe pas, des combinaisons (ou arrangements ou permutations, est-ce que je sais, moi ) sans oublier ton histoire d'approximation de la valeur nominale par la valeur réelle.
Moi je ne peux pas plus (au risque de me répéter!)

XL 2010 [VBA] Recherche, comparaison et sélection de valeurs

demonsares

XLDnaute Junior

Pièces jointes

Modeste

XLDnaute Barbatruc

demonsares

XLDnaute Junior

Pièces jointes

Modeste

XLDnaute Barbatruc

demonsares

XLDnaute Junior

Modeste

XLDnaute Barbatruc

Pièces jointes

demonsares

XLDnaute Junior

Pièces jointes

Modeste

XLDnaute Barbatruc

demonsares

XLDnaute Junior

Pièces jointes

Modeste

XLDnaute Barbatruc

Discussions similaires