macro pour automatiser la recherche de variables en fonction d'une valeur cible

[coquelicot]

Bonjour,

J'ai eu beau chercher sur le forum, je n'ai pas réussi à transposer ce que j'y ai trouvé à mon cas.
Donc je me permets de vous soumettre la question.
Je joins un petit fichier permettant, je l'espère, d'illustrer mon problème.
J'ai copié une macro trouvée sur ce site, sans forcément comprendre toutes les subtilités et je pense que ça doit expliquer mes erreurs.
Mon problème consiste à automatiser la recherche de variables en fonction de valeurs cibles prédéfinies:
Mon objectif est d'arriver à trouver les valeurs de hauteur (K56 à K59) qui permettent d'obtenir les valeurs de débit cible (F56 à F59).
Les boutons situés en U46 à U47 ne me permettent pas de garder les valeurs (de hauteur) apparentes, du moins, je n'ai pas trouvé comment faire.
Donc j'ai tenté de copier une macro, appelée par un bouton pour approximer la valeur de hauteur. Elle ne bugue pas, mais le résultat est erroné.

Si besoin d'explications plus claires, ne pas hésiter à me faire signe.

Bien cordialement,

 

[Dranreb]

Re : macro pour automatiser la recherche de variables en fonction d'une valeur cible

Bonsoir.

La 1ère chose que je vois: Vous demandez à ce que V41 atteigne une valeur (contenue en F51, mais peu importe) par ajustement de la cellule V35. Or celle ci n'intervient pas dans la formule de V41 qui est =V37*V40

Edit: Ah si, erreur, elle intervient via la formule de V37 qui est =(V33*V35). Pourquoi des parenthèses ?
Mais alors je ne vois pas ou est le problème: j'ai exécuté les 4 procédures, toutes ont su saire en sorte que V41 atteingne les valeurs contenues respectivement en F57, F58, F59, C43.

J'ai enfin trouvé la BtCorrig_Click dans le module de la feuille. Celle ci demande l'ajustement des cellules R56:R59 qui sont vides et donc Not .HasFormula. Ah oui, ça a l'air plus intéressant avec :

For Each RgVar In Me.Range("k56:k" & Me.[k65536].End(xlUp).Row)
   Set RgCbl = Intersect(Me.Columns("S"), RgVar.EntireRow)
   If RgCbl.HasFormula Then
    VCible = Intersect(Me.Columns("F"), RgVar.EntireRow).Value

 

[coquelicot]

Re : macro pour automatiser la recherche de variables en fonction d'une valeur cible

Bonjour,

Merci de vous être penché sur ma question.

Concernant les cases à cocher, je n'ai aucun souci de fonctionnement si ce n'est que j'aimerais pouvoir disposer de tous les résultats en même temps dans le tableau B55:S59. Or je n'ai pas réussi à développer un peu plus ce système de cases à cocher pour lui demander de renvoyer la valeur dans ce tableau (col K) et qu'elles y restent. J'avais mis une formule dans les cellules K56 à K59 pour lire les résultats en V35 quand la condition de débit annoncée en V41 correspond à celle du tableau en colonne F, mais bien sûr seule la ligne concernée s'affiche et toutes les hauteurs ne peuvent alors pas s'afficher simultanément.

[[[Pour les parenthèses, c'est parce qu'il n'y a pas de raison de faire simple si on peut faire compliqué .
D'une manière générale, ce fichier est loin d'être optimisé et épuré du point de vue des formules employées ou de la mise en forme, mais le souci c'est que je bricole comme je peux et que c'est un fichier en perpétuel agrandissement, démarré il y a plusieurs mois sans connaître tous les attendus, et je ne les connaîtrai peut-être jamais, ... donc les formules et la forme sont sans cesse remaniées tout en devant respecter la contrainte d'un document double page imprimable]]]

C'est la raison pour laquelle j'ai tenté de passer par une autre macro, celle adaptée de ce que j'ai trouvé dans ce forum, pour arriver à mes fins.
En faisant le grand ménage dans le fichier avant de le poster, j'ai effectivement décalé les données mises initialement en R vers la colonne S (S56:S59). J'ai donc fait la correction dans le code depuis pour effacer cette bévue, mais de mon côté ça ne marche pas plus qu'avant. Est-ce que le bouton de commande placé en K61 permet bien de mettre à jour les données en K56:K59 en fonction des débits prévus (f56:f59) chez vous?

Affaire à suivre donc.

Merci du temps que vous m'accordez.

EDIT: Je viens de comprendre. J'avais deux décalages de colonnes successifs de sorte que je n'ai jamais pu voir que la formule fonctionnait bien en fait et j'ai cherché trop loin avant de vérifier la base.
Suis ravie que ça fonctionne!! CA MARCHE!!! Trop bien!!!
Petite question bonus: Vous semble-t-il possible de s'exonérer dans le code de la colonne S (S56 à S59), un peu dans l'esprit de la fonction valeur cible, car je n'ai pas vraiment besoin de la voir (y compris à des fins de vérification du bon déroulement de la formule). Ceci dit, ce n'est pas compliqué de masquer ces données. Mais si on peut simplifier, pourquoi pas. Je demande cela, car je pense que dans le cas d'où j'ai tiré ces lignes de code, il y avait un niveau supplémentaire d'interférences dans les valeurs, donc dans mon cas, cette étape intermédiaire n'est peut-être pas nécessaire, mais je ne suis pas sûre...

 

[Dranreb]

Re : macro pour automatiser la recherche de variables en fonction d'une valeur cible

Bonjour.

Je pense qu'on ne peut pas se dispenser des formules en colonnes S pour le calcul.
À moins d'écrire une fonction qui renvoie le hgoulotte selon Qexus, en étudiant mieux les fonctions inverses impliquées.
Si vraiment pas possible, une version différente de la fonction ApprocherMieux, se basant sur une autre fonction plutôt que sur une formule de cellule pourrait y arriver. Telle quelle est peut parfois suffire sans même dégrossir préalablement avec GoalSeek.

Edit: Ça semble même être le cas ici: la procédure réécrite comme ça a l'air de fonctionner :

Private Sub BtCorrig_Click()
Dim Plg As Range, L As Long, VCible As Double, RgVar As Range, RgCbl As Range
Set Plg = Me.Rows(56).Resize(Me.[K65536].End(xlUp).Row - 55)
Plg.Columns("S").FormulaR1C1 = "=R32C22*((R34C22*RC11)/(R34C22+2*RC11))^(2/3)*SQRT(R36C22)*R34C22*RC11"
Plg.Columns("K").Value = 0.5
For L = 1 To Plg.Rows.Count
   Set RgVar = Plg.Cells(L, "K")
   Set RgCbl = Plg.Cells(L, "S")
   If RgCbl.HasFormula Then
      VCible = Plg.Cells(L, "F").Value
'      If RgCbl.GoalSeek(Goal:=VCible, ChangingCell:=RgVar) Then
         ApprocherMieux RgCbl, VCible, RgVar, 0.001
         ApprocherMieux RgCbl, VCible, RgVar, -0.00001
         ApprocherMieux RgCbl, VCible, RgVar, 0.0000001
         ApprocherMieux RgCbl, VCible, RgVar, -0.000000001
'      Else
'         MsgBox "Valeur non atteinte"
'         End If:
      End If: Next L
'Plg.Columns("S").ClearContents ' mis en commentaire provisoirement
End Sub

Il ne reste qu'à mettre cette formule en Function

 

[coquelicot]

Re : macro pour automatiser la recherche de variables en fonction d'une valeur cible

Bonsoir,

Je suis épatée par votre réactivité et la pertinence des propositions. Ça me donne envie de me former un peu plus en la matière...

Concernant la possibilité d'exprimer h en fonction de Qexu, c'est ce par quoi j'avais commencé, mais je n'ai pas réussi à réduire suffisamment l'expression pour y parvenir. Je ne dis pas que c'est impossible, mais je n'ai pas trouvé sur le coup.
Concernant le nouveau code, pour ce que je peux en analyser, ça m'a l'air pas mal du tout et je vois l'intégration de la formule directement dans le code, donc j'ai bon espoir. Je suis en déplacement pour une dizaine de jours, donc je ne suis pas en mesure de la tester dans mon fichier aujourd'hui mais je le ferai dès mon retour. Je vous tiendrai informé. Encore merci du temps passé.
Je ne sais pas quelles contraintes cela représente, mais je demandais même s'il y avait un obstacle à transformer cela en une boucle pour qu'elle recalcule en temps réel les hauteurs en fonction des changements opérés dans les différentes cellules des variables. Mais j'avoue que je n'ai pas trop en tête les conséquences que ça pourrait avoir, ni si c'est faisable...

Petite question et au risque de confirmer mon statut de béotienne, je n'ai pas bien compris le sens de la dernière phrase "mettre cette formule en Function".

A bientôt,
Bien cordialement,

 

[Dranreb]

Re : macro pour automatiser la recherche de variables en fonction d'une valeur cible

Ma dernière phrase voulait dire que puisqu'on n'a plus besoin du GoalSeek, on n'a plus besoin non plus d'une formule dans une cellule, on peut calculer Qexu dans VBA. Le plus simple c'est par une Function avec comme 1er argument h, et comme suivants les autres paramètres stables impliqués. Conformément à ce que je disais plus haut :

une version différente de la fonction ApprocherMieux, se basant sur une autre fonction plutôt que sur une formule de cellule pourrait y arriver.

À part qu'il y avait une imprécision: ApprocherMieux n'est pas une fonction mais une Sub. Je pense qu'on peut écrire une Function capable de calculer h en fonction de Qexu par la même méthode que ApprocherMieux. Il faut juste que l'hypothèse h de départ soit <> 0.

Le principe est assez simple: on a un h0 et on connait le Qexu0 correspondant. On détermine un h1 par ajout d'une quantité de plus en plus petite alternativement en + et en -, on calcule le Qexu1 correspondant, enfin on prend, si c'est meilleur, comme nouveau h0 l'interpolation linéaire entre h0 et h1 par rapport à Qexu0 et Qexu1 où ça vaut le Qexu cherché.

 

[coquelicot]

Re : macro pour automatiser la recherche de variables en fonction d'une valeur cible

Bonjour,

J'ai bien noté votre réponse. Je voulais essayer de formuler par moi-même cette fonction sous VBA. Par manque de temps et de maîtrise, je n'y suis pas encore parvenue, mais je ne désespère pas. Je m'étais dit que je vous répondrais après avoir testé votre proposition mais le temps passe... alors je ne voulais pas vous laisser sans réponse.

En tout cas, je tenais à vous remercier pour votre aide.

Dès que j'arrive à écrire cette fonction ou à proposer quelque chose pour relecture, je ferai signe!

Bien cordialement,