Discussion :

[pitchito]

Bonjour à toutes et à tous,

Je me permet aujourd'hui de vous demander de l'aide car je suis bloqué dans la mise au point d'un tableur excel...
La question est probablement basique mais mon niveau d'excel est surement médiocre par rapport au votre...

Mon soucis: Je voudrais qu'une formule apparaisse dans une cellule si la valeur d'une cellule voisine est "oui".
Ce sera plus compréhensible avec mon cas concret:

De B4 à B49, je peux choisir deux valeurs dans une liste: "oui " et "non" correspondant à l’existence ou non d'un numéro d'article dans une base de données.
De C4 à C49, les numéros d'articles en question à rentrer manuellement.
De D4 à D49, j'aimerai que la formule "=RECHERCHEV(C4;Données!$A$330:$C$20244;2)" soit appliquée uniquement si la valeur "oui " est en B4 (pour le cas de D4). Si "non" est en B4, j'aimerai que la cellule reste vierge et puisse être saisie manuellement.

Je ne sais pas si il est possible que le code s'active automatiquement à chaque fois que la valeur de la colonne B est modifiée, mais ce serait vraiment l’idéal! Histoire que si un malin supprime la formule quand "oui " est actif, elle se réécrive automatiquement lors d'un double switch oui/non.

J'espère que vous avez compris mon problème et que vous m'accorderez quelques minutes pour m'aider! J'ai cherché pendant une bonne heure et je n'ai trouvé aucun cas semblable au miens...

[Philippe Tulliez]

Bonjour,

De D4 à D49, j'aimerai que la formule "=RECHERCHEV(C4;Données!$A$330:$C$20244;2)" soit appliquée uniquement si la valeur "oui " est en C4 (pour le cas de D4). Si "non" est en C4, j'aimerai que la cellule reste vierge et puisse être saisie manuellement.

Il suffit de faire précéder la fonction RECHERCHEV par la fonction SI

Cependant je ne comprends pas ce que tu cherches car si je lis ta demande, tu voudrais que si C4 contient la valeur "oui", il recherche cette même valeur ("oui") dans la plage $A$330:$C$20244 de la feuille nommée Données ?
Cette demande me semble étrange

De plus, il est impossible de combiner dans une cellule, la possibilité de renvoyer une valeur par formule et faire de l'encodage manuel.

[pitchito]

Pardon, la valeur "oui " ou "non" est en B4 et non en C4.


Sauf erreur de ma part, la précéder d'une fonction si oblige à supprimer la fonction si jamais je veux une entrée manuelle (donc si le numéro d'article n'existe pas: "non" en B4).
Ici, je souhaiterais que la cellule D4 switch entre cellule vide si jamais B4 est "non", mais comporte la formule "=RECHERCHEV(C4;Données!A323:C20237;2)" si la cellule B4 est "oui "

[Philippe Tulliez]

Bonjour,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(B4="Oui";RECHERCHEV(C4;Données!$A$330:$C$20244;2);"")

Es-tu certain que tu recherches une valeur proche ?

Sauf erreur de ma part, la précéder d'une fonction si oblige à supprimer la fonction si jamais je veux une entrée manuelle (donc si le numéro d'article n'existe pas: "non" en B4).

Non, si tu encodes dans la cellule où se trouve cette formule, celle-ci sera tout simplement écrasée

[Menhir]

Faire une macro évènementielle Worksheet_Change qui commence par vérifier que la cellule modifiée est bien en colonne B.
https://docs.microsoft.com/fr-fr/off...else-statement
https://msdn.microsoft.com/fr-fr/lib...0(v=office.15)
Si ce n'est pas le cas, elle sort avec un Exit Sub.

Ensuite, elle vérifie si le contenu de la cellule est "oui".
https://docs.microsoft.com/fr-fr/off...else-statement
https://msdn.microsoft.com/fr-fr/lib...3(v=office.15)

Si c'est le cas, elle implante la formule dans la cellule deux cases à droite (Offset(0, 2)) avec une propriété FormulaLocal.
https://msdn.microsoft.com/fr-fr/lib...1(v=office.15)

[pitchito]

L’inconvénient de cette formule, c'est que si en B4, il y a "non", et que la désignation est entrée manuellement en D4, il n'y a plus jamais moyen de revenir en arrière et d'entrer plus tard une valeur déjà enregistrée et susceptible d'être récupérée par une recherchev sur la même cellule. Lorsque je parlais de cellule vide, je voulais dire complétement vide, même dans la barre de formule.

J'ai l'impression qu'on ne se comprend pas. Je me permets de reprendre l'architecture de mon tableau:

Colonne B: "pièce existante dans la base de données" donne deux choix: "oui " ou "non"
Colonne C: "code article" exige une valeur entrée manuellement
Colonne D: "désignation": Je voudrais que la désignation correspondant au code article soit entrée automatiquement si elle existe dans la base de données (A323 à C20237 de l'onglet données) et que la cellule reste vierge (même de formule qui force à afficher " " ) si il s'agit d'un produit qui n'est pas encore entrée.

Je ne sais même pas si c'est possible en fait mais j'ai lu sur ce forum quelqu'un qui disait qu'excel ne présentait que des solutions alors...

[Menhir]

L’inconvénient de cette formule, c'est que si en B4, il y a "non", et que la désignation est entrée manuellement en D4, il n'y a plus jamais moyen de revenir en arrière

Avec ma méthode, tu peux revenir en arrière en mettant un "oui".

en fait mais j'ai lu sur ce forum quelqu'un qui disait qu'excel ne présentait que des solutions alors...

Excel, c'est comme le ciel de l'adage : Aide-toi et il t'aidera.
Il sait faire beaucoup de choses mais il ne fait rien tout seul.

[pitchito]

Faire une macro évènementielle Worksheet_Change qui commence par vérifier que la cellule modifiée est bien en colonne B.
https://docs.microsoft.com/fr-fr/off...else-statement
https://msdn.microsoft.com/fr-fr/lib...0(v=office.15)
Si ce n'est pas le cas, elle sort avec un Exit Sub.

Ensuite, elle vérifie si le contenu de la cellule est "oui".
https://docs.microsoft.com/fr-fr/off...else-statement
https://msdn.microsoft.com/fr-fr/lib...3(v=office.15)

Si c'est le cas, elle implante la formule dans la cellule deux cases à droite (Offset(0, 2)) avec une propriété FormulaLocal.
https://msdn.microsoft.com/fr-fr/lib...1(v=office.15)

Ta solution à l'air parfaite, mais je suis bien incapable de créer un code de A à Z. Jusqu'ici, je me suis toujours contenté de taper mon besoin sur google, quelqu'un avait quelque chose de semblable que j'adaptais plus ou moins à mon cas....

Je viens d'y passer une heure sans résultat probant. Je devrais rester dans ma zone de confort qu'est la physique, pourquoi j'essaye de m'aventurer dans l'informatique?

Je vais m'acharner dessus parce que ça me ferait gagner un temps précieux à l'avenir si ça marchait mais si tu as un exemple de code qui s'en approche... ou même plusieurs que je pourrais mixer, tu deviendrais mon héro de la journée!

Je vois très bien (enfin je crois) l'architecture que dois avoir la macro mais impossible de l'écrire.


Sauf erreur de ma part ça ressemblerait à:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Private Sub Worksheet_Change(ByVal Target As Range)
 
Si la colonne modifié est en B
         alors lire le contenu
               si le contenu est oui
               alors implanter la formule recherche V deux cases à droite de la sélection
               sinon, supprimer le contenue deux cases a droite de la sélection      
         Then exit sub
 
End Sub

J'ai absolument rien compris?

[Philippe Tulliez]

Bonjour,
Personnellement, je placerais la formule RECHERCHEV le temps d'obtenir le résultat et ne conserver que celui-ci ou mieux encore d'écrire le résultat calculé par la fonction Evaluate car une bonne pratique de l'organisation des listes de données (idéalement choisir les tableaux structurés) implique d'avoir dans une colonne soit une formule soit une constante.
Il y a lieu également de rendre inopérant l'enclenchement des procédures événementielles le temps de l'écriture

Pour en savoir plus sur ce qui est décrit ci-dessus

• Les évènements dans la feuille de calcul Excel et tout particulièrement le chapitre II-E. Worksheet_Change
• Apprendre à utiliser les tableaux structurés Excel : création, manipulations et avantages(1)
• La fonction Evaluate

[Menhir]

Je viens d'y passer une heure sans résultat probant.

Dans ce cas, peut-être pourrais-tu présenter le code que tu as passé "une heure" à produire et expliquer ce qui pose problème pour qu'on puisse te dire ce qui ne convient pas.

Je devrais rester dans ma zone de confort qu'est la physique, pourquoi j'essaye de m'aventurer dans l'informatique?

Moi aussi je vais rester dans ma zone de confort qu'est mon travail, pourquoi essayer de m'aventurer dans ton travail ?

Je ne voudrais surtout pas te "sortir de ta zone de confort" mais si tu décides de faire l'énorme effort d'être actif sur la résolution de ton propre problème, voici qui peut t'aider.
http://bidou.developpez.com/article/VBA/

si tu as un exemple de code qui s'en approche... ou même plusieurs que je pourrais mixer, tu deviendrais mon héro de la journée!

Il y a plusieurs exemples dans les lien que je t'ai proposé.
Il ne te reste plus qu'à les "mixer".

Bien sûr, il faudra un peu réfléchir à ce qu'il signifient. Des copier/coller ne suffiront pas.

[Pierre Fauconnier]

Salut.

Dans un premier temps, je reviens sur ce que Philippe a dit: il n'est pas possible de faire coexister une formule est une saisie manuelle dans une cellule. C'est l'une ou l'autre.

Avant de t'embarquer dans du code évènementiel, il serait peut-être judicieux de tenter une approche Excel:
Dans une cellule X, tu permets la saisie manuelle. Dans une cellule Y, tu utilises soit la saisie en x si elle existe, soit le résultat de RechercheV: si(et(b4 = "oui";estvide(x)); recherchev...();x)

Au passage: RechercheV, c'est bof bof...

[pitchito]

Moi aussi je vais rester dans ma zone de confort qu'est mon travail, pourquoi essayer de m'aventurer dans ton travail ?

Je ne voudrais surtout pas te "sortir de ta zone de confort" mais si tu décides de faire l'énorme effort d'être actif sur la résolution de ton propre problème

Bien sûr, il faudra un peu réfléchir à ce qu'il signifient. Des copier/coller ne suffiront pas.

Je suis désolé si tu as mal pris la remarque, elle était uniquement destiné à essayer d'installer un climat plus "personnel" et me montrer en temps que personne derrière un pseudo en dévoilant mes faiblesses. En aucun cas, je ne te demandais de résoudre mon problème directement ; j'aimerai avoir la satisfaction de faire moi même quelque chose qui sort de mes habitudes. Si je ne voulais vraiment pas sortir de ma zone de confort, je ne serai pas ici à essayer d'obtenir de l'aide pour quelque chose que je ne sais pas faire.

J'ai lu avec attention l'ensemble tes liens et je pense avoir compris la structure à donner au code (cf message du dessus) mais je suis incapable de l’écrire sous forme VBA. Par exemple, quelle variable déclarer? comment? (je ne suis même pas sûr qu'il y en est)...

Salut.

Dans un premier temps, je reviens sur ce que Philippe a dit: il n'est pas possible de faire coexister une formule est une saisie manuelle dans une cellule. C'est l'une ou l'autre.

C'est pour cette raison que je pensais que le VBA me serait utile et pourrait résoudre mon problème

Salut.

Dans un premier temps, je reviens sur ce que Philippe a dit: il n'est pas possible de faire coexister une formule est une saisie manuelle dans une cellule. C'est l'une ou l'autre.

Avant de t'embarquer dans du code évènementiel, il serait peut-être judicieux de tenter une approche Excel:
Dans une cellule X, tu permets la saisie manuelle. Dans une cellule Y, tu utilises soit la saisie en x si elle existe, soit le résultat de RechercheV: si(et(b4 = "oui";estvide(x)); recherchev...();x)

Au passage: RechercheV, c'est bof bof...

C'est comme ça que j'avais fais la première version de mon fichier mais je trouve ça très "bricole".



Je vous remercie tous d'avoir pris le temps de me répondre en tous cas =)

[Pierre Fauconnier]

Je ne sais pas si c'est de la bricole ou pas, dans l'absolu. Cette notion est subjective, mais je procède ainsi dans beaucoup de classeurs pour mes clients, à leur grande satisfaction.

Mettre du code "juste pour cela", c'est monter en complexité en sautant quelques paliers:

• Déjà, s'il n'y a du code que pour ça, ça transforme ton xlsx en xlsm;
• Dans la solution proposée par Menhir, la formule à implanter est reprise en dur dans le code. Ca veut dire que si tu dois la modifier, tu devras modifier ton code;
• Dans cette solution, la formule est proposée en FormulaLocal (A NE JAMAIS FAIRE: casse-gueule assuré si tu utilises le classeur sur une version linguistique différente. Il faut TOUJOURS utiliser la propriété Formula et écrire la formule en anglais international);
• La complexité est également amenée par le fait qu'il faut réécrire correctement la formule avec les variables éventuelles, en doublant les quotes, etc, etc;
• L'évènement est déclenché à chaque modification d'une cellule de la feuille, ce qui pourrait poser problème, et appelle de toute façon au mieux une fois la macro pour rien (lors de l'injection de la formule). Il faudrait normalement désactiver la gestion des évènements durant le traitement, mais le remettre après. Dès lors, il faut une gestion d'erreurs pour être certain de le remettre sortie de procédure;
• ...

Le jeu en vaut-il la chandelle? A toi de voir. Pour moi, la réponse est non

[Menhir]

Par exemple, quelle variable déclarer? comment? (je ne suis même pas sûr qu'il y en est)...

Bonne nouvelle : dans la procédure que j'ai décrite, il n'y a besoin d'aucune variable.
Mais si tu devais en déclarer, l'explication est à la page 13 du cours en lien dans ma réponse précédente.

Pour en savoir plus sur le sujet :
http://silkyroad.developpez.com/VBA/LesVariables/
http://mhubiche.developpez.com/Access/variables/

[pitchito]

Pour ce qui est de la transformation xls en xlsm, j'ai déjà d'autres macro donc ça ne me dérange pas.

En revanche, quel problème est lié au fait que la macro s'active à chaque fois? Problème de bug éventuel? Si c'est le cas, ce n'est pas un gros problème. Le classeur est destiné à mon équipe et moi même et nous sommes tous équipés de machine capable de faire tourner des logiciels très gourmands (logiciels de CAO, de rendu photo / vidéos...).


Je crois que je suis tombé sur un site qui m'a fait faire un bond en avant et je pense ne plus être loin de ce qu'il me faut:
https://fr.wikiversity.org/wiki/Macr...C3%A9venements

En me basant sur ça:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Private Sub Worksheet_Change (ByVal Target As Range)
    If Target.Column = 1 Then
      Target.Offset(0, 1) = Now
    End if
End Sub

J'en suis arrivé à écrire ça par mon cas:

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
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Column = 2 Then
If Target = "oui " Then
Target.Offset(0, 2) = "=(RECHERCHEVC4;Données!$A$330:$C$20244;2))"
 
Else: Target.Offset(0, 2) = ""
 
End If
 
Else: Exit Sub
 
End If
 
End Sub

Ça marche très bien si on oubli le fait que ça note la formule de recherche et pas le résultat.
Je sais que c'est justement le "formulalocal" que je dois utiliser mais je ne trouve pas où le mettre....
Et comment faire en sorte que ça ne foire pas avec une version linguistique différente?

[Pierre Fauconnier]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Target.Offset(0, 2).Formula = "=Vlookup(C4,Données!$A$330:$C$20244,2)"

En tenant compte de la remarque de Philippe sur le RechercheV... Es-tu sûr de chercher le plancher de la plage dans laquelle ta valeur se trouve?

Si oui, ok. Si non (=> tu cherches la valeur EXACTE), tu dois utiliser "=Vlookup(C4,Données!$A$330:$C$20244,2,False)"

Dans la suite de ton parcours, es-tu certain que ce sera toujours la même plage de données (lignes 330 à 20244). J'ai de gros doutes là-dessus. L'utilisation de tables de données t'aiderait à écrire ta formule de façon à ce qu'elle tienne compte des bonnes lignes. Sinon, tu vas devoir t'amuser à recréer l'adresse de la plage A330:C20244, et donc à complexifier un peu plus ton code.

Pour ce qui est du problème de l'appel récursif à cause du Change, tout dépend de ce que tu souhaites. Attention aux appels récursifs sans billet de sortie... Dans la mesure où le Change ne concerne que la cellule mentionnée et qu'il n'est actif que sur action de l'utilisateur (pas sur la modification d'une valeur par formule), il n'y a pas de risques dans ton cas...

Je dirai pour conclure que coder le process dans la procédure évènementielle ne relève pas de la meilleure architecture qui soit. Il serait utile de déléguer le traitement à une procédure qui est appelée par la procédure évènementielle en fonction du target choisi. Car si tu as plusieurs traitements à faire sur ta feuille en fonction de cellules différentes modifiées, ta proc évènementielle (unique pour ta feuille) va vite devenir un souk ingérable.

[Menhir]

Ça marche très bien si on oubli le fait que ça note la formule de recherche et pas le résultat.
Je sais que c'est justement le "formulalocal" que je dois utiliser mais je ne trouve pas où le mettre....

C'est une propriété de Range.
Il faut donc le mettre comme membre de ton Offset tout comme Offset est mis comme membre de Target.

Et comment faire en sorte que ça ne foire pas avec une version linguistique différente?

Utiliser Formula au lieu de FormulaLocal et mettre la formule avec les fonctions Excel anglophones.

[pitchito]

Dans la suite de ton parcours, es-tu certain que ce sera toujours la même plage de données (lignes 330 à 20244). J'ai de gros doutes là-dessus. L'utilisation de tables de données t'aiderait à écrire ta formule de façon à ce qu'elle tienne compte des bonnes lignes. Sinon, tu vas devoir t'amuser à recréer l'adresse de la plage A330:C20244, et donc à complexifier un peu plus ton code.

La base de données est une extraction qui est faites tout les 6 mois, si j'ai 3 minutes de travail tout les 6 mois pour modifier ça, ça ne me pose pas de gros problème.




J'ai presque atteint ce que j'imaginais dans l'utilisation (merci Menhir de m'avoir fourni l'architecture, je n'y aurai jamais pensé). Il me reste un micro problème.
La formule utilise C4 comme base de recherche pour toutes les lignes. Donc ça marche très bien pour la première, mais les 49 suivantes utilise également C4.

Savez vous comment remplacer C4 dans la formule par C "ligne en cours" ou même par "cellule de droite de la cellule modifiée"?

[pitchito]

C'est bon!

Je viens de réussir à trouver une solution. Alors c'est peut être pas du tout comme ça que l'on fait en toute rigueur mais ça fonctionne nickel pour mon équipe et moi même et c'est tout ce que je demandais!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Position = ActiveCell.Row
If Target = "oui " Then
Target.Offset(0, 2).FormulaLocal = "=RECHERCHEV(C" & Position & ";Données!$A$330:$C$20244;2)"
Target.Offset(0, 5).FormulaLocal = "=RECHERCHEV(C" & Position & ";Données!$A$330:$C$20244;3)"
Else: Target.Offset(0, 2) = ""
Target.Offset(0, 5) = ""
End If
Else: Exit Sub
End If
End Sub

Merci à tous d'avoir pris le temps de m'aider. Et encore une fois désolé si j'ai pu donner l'impression d'en demander trop, ce n'étais pas du tout voulu.
Bonne continuation à tous

[Pierre Fauconnier]

C'est bon!

Je viens de réussir à trouver une solution. Alors c'est peut être pas du tout comme ça que l'on fait en toute rigueur mais ça fonctionne nickel pour mon équipe et moi même et c'est tout ce que je demandais![...]

La première obligation d'un code est une obligation de résultat. Après, et au fur et à mesure de ton travail avec Excel et VBA, il sera intéressant de "faire dans la dentelle" mais tant que cela fonctionne, c'est le principal...