Bonjour à tous.

Voilà une application de DEVIS.

J’ai 3 feuilles dans mon classeur pour cette situation
‘’BD ‘’
‘’SAISIE’’
‘’DEVIS’’
Dans ma feuille de saisie je vais chercher les articles par une menu déroulant (validation de données liste) => Menu Formule/Définir un nom => fonction DECLALER dans la gestion des noms.
C’a pour la désignation des produits. Pour mon exemple il s’agit d’une poutre. Par la fonction RECHERCHEV j’affiche dans les colonnes suivantes la largeur / la hauteur / le poids etc…
Bon voilà pour le principe.


Ma feuille DEVIS contient par liaison relative une copie conforme de ma feuille de SAISIE. Pourquoi parce que dans la feuille DEVIS il s’y trouve des calculs complexes et dans la feuille DEVIS les derniers calculs commerciaux. Bon c’est comme cela que bosse la boîte dans laquelle je viens d’arriver.

Mon but je prends un exemple
Col A Col B Col C Col D Col E
Désignation   Type    Largeur   x    Hauteur
  Poutre IC      40x75     IC 40    x     75

Bon et bien dans ma feuille SAISIE la hauteur qui sert à calculer le volume du produit est une valeur du fait de sa géométrie.
Poutre IC 40x75 IC 40 x 45.4 (ce sera la valeur à changer dans la feuille DEVIS)

Et c’est dans ma feuille de DEVIS que je dois faire la conversion

Poutre IC 40x75 IC 40 x 75

Mais sans casser le lien relatif pour des questions de manipulations des opérateurs car sans cela il faudrait refaire toute la structure de ce soft et ce n’est pas au programme, donc je souhaite l’améliorer et le rendre plus souple.

Voici un code que j'ai adapté:

 
Private Sub Worksheet_Change(ByVal Target As Range)
'Dim Note As Integer 'on va s'en passer, on fera directement appelle a Target.value il est la pour ça
Dim Mention As String
 
'On vérifie que target correspond bien a une des cellules que l'on souhaite contrôler
'Je suppose que tout les nom des élèves sont en colonne A et les note en B, ca donne donc
If Not Intersect(Target, Range("A:A")) Is Nothing Then    'rempalce le B par la colonne qui contient les notes
    'B:B represente toute la colonne
    'Note = Target
 
    Select Case Target.Value

        Case "Poutre IC 40x75": Mention = "75"
        Case "Poutre IC 40x80": Mention = "80"
        Case "Poutre IC 40x95": Mention = "95"
        Case "Poutre IC 40x100": Mention = "100"
        Case "Poutre IC 40x105": Mention = "105"
        Case "Poutre IC 40x110": Mention = "110"
        Case "Poutre IC 40x115": Mention = "115"
        Case "Poutre IC 40x120": Mention = "120"
        Case "Poutre IC 40x125": Mention = "125"
        Case "Poutre IC 40x130": Mention = "130"
        Case "Poutre IC 40x145": Mention = "145"
        Case "Poutre IC 40x150": Mention = "150"
 
        
    End Select
 
    'Cells(Target.Row, 3) = Mention  'remplace le 3 par le numéro de colonne que tu veux
    'ici on va utiliser la notion d'offset, qui permet de décaler la celui "pointé" de y lignes et de x colonne,
    'y positif on descend, négatif... on remonte dans le tableau
    'x >0 on va vers la droite, x<0 on va vers la gauche
    'dans les 2 cas attention de ne pas sortir du tableau excel ;)
    
    'On va également éviter de re-déclencher Change (vu que l'on modifie une cellule OnChange se déclenche
    'Il ne faudra pas le faire si la modif de la cellule en colonne B doit exécuter du code dans l'événement Change
    
    On Error Resume Next 'si une erreur survient on passe qd même a la ligne suivante, jefais ca pour eviter de rester avec EnableEvent a false
    Application.EnableEvents = False 'Excel ne déclenchera plus de code événementiel
    Target.Offset(0, 5).Value = Mention 'la cellule immédiatement a coté de la notre prend la valeur contenu dans Mention
    Application.EnableEvents = True 'Excel recommence a gerer l'événementiel
    On Error GoTo 0 ' si des erreur surviennent a partir de maintenant excel nous avertira (comme avant le resume next - c'est par défaut)
    
End If
End Sub
 

En d'autres termes....

Ce que je veux après qu'il y ait eu les saisies dans la feuille SAISIE, c'est que dans la feuille DEVIS (qui est en quelque sorte une feuille de mise en forme devant servir à être imprimée et envoyée au client) si la colonne E trouve en colonne A la désignation Poutre IC 40x75 donc que sur la même ligne en colonne E (malgré qu'il y ait une liaison relative à la feuille de SAISIE) qu'elle prenne la main et indique la valeur 75 et non 45.4 résultant de la liaison donc la véritable hauteur de la poutre. Et cela bien sûr sans intervention manuelle.

En quelque sorte c'est une condition que je veux écrire derrière la liaison et la recopie vers le bas des liaisons.

Ouf, pas facile à expliquer comme cela... je vous joins un fichier exemple.

Est-ce la bonne méthode ?? Merci vous serez super....

Lagratte

Bonjour Lagratte.

Si tu remplaces la fonction de la colonne E par celle-ci, colle en E2, et fait un glisser vers le bas
=SI(STXT(A2;1;9)="Poutre IC";STXT(A2;TROUVE("x";A2;1)+1;10);SAISIE!E2) 

Cela résoudrait-il ton problème ?

Pour masquer les messages d'erreur #N/A, je te propose une Mise en Forme condtionnelle.

1) Sélectionne la colonne C:C
2) Clique sur le menu Format/Mise en forme condiditionnelle...
3) Dans la boîte de dialogue, champ le terme "la valeur de la cellule est" par "la formule est"
4) dans le champ juste à droite, tape la formule =ESTNA(C1)
5) Ensuite clique sur le bouton "Format", adapte la couleur du texte, à la couleur du fond de tes cellules
6) Valide deux fois

Répète l'opération pour la colonne E:E, en indiquant la formule =ESTNA(E1)

@+Jean-Marie

Merci pour ta réactivité.

Mais je ne vois pas bien comment utiliser dans mon code ta solution.

Parce que la liste de correspondance et d'articles est très longue et je n'ai mis dans mon exemple que peu de lignes.

Si tu m'expliques mieux ta solution, la fonction SI est-elle limité par un certain nombre de cas ?

Re...

Cette modification n'est pas à mettre dans ton code.

C'est au niveau de la feuille Devis, il faut remplacer la formule de la colonne E.

Si tu désires le faire absolument en macro. Il nous faut toutes les données que tu souhaites soustraire à ta formule de la feuille Devis.

@+Jean-Marie 

OK Jean-Marie.

La complexité de la tâche j'en conviens, c'est que dans la feuille DEVIS chaque cellule est liée à sa semblable dans SAISIE.

Mon but est comme dans l'exemple du fichier joint, de pouvoir malgré ce lien écrire la condition en macro VBA (ce que j'ai commencé à faire en adaptant un bout de code), un peu comme une forme conditionnelle, mais sans casser la liaison. Alors est-ce que vraiment c'est possible ou pas ?

Est-ce que que je peux faire un worksheet_change sur la feuille saisie qui mettra la valeur dans la feuille devis, et créer une séquence dans worksheet_change de la feuille devis qui remet la valeur de la feuille saisie dans la colonne E, si celle-ci devient vide ?

Et sinon comment faire ?

Il est vrai que c'est un vrai défi et je patine...

Je veux bien te passer plus d"éléments, mais de quoi aurais-tu besoin ?

Re...

Hormis la macro. Dis-moi si ma proposition te résoud ton problème, ce qui voudrait dire que j'ai compris ce que tu veux obtenir.

@+Jean-Marie 

Merci Jean-Marie.

Ouais merci pour ta réactivité, je viens juste de tomber sur ce site aujourd'hui et j'avance bien merci encore.

Donc effectivement la fonction SI marche et fait exactement ce que je souhaite.

Ce qui m'ennuie dans cette fonction, c'est qu'apparemment il faut lui indiquer la plage de départ (A2;1;9).

ligne 1 à ligne 9, mais cela peut être très variable.

=SI(STXT(A2;1;9)="Poutre IC";STXT(A2;TROUVE("x";A2;1)+1;10);SAISIE!E2)

je ne sais pas ce qu'est le STX et où ce code va lire ? mais sans cela c'est ça effectivement.

Re...

OK, pour la formule, je vais te l'expliquer

STXT(A2;1;9) : 

• 1;9 n'est donc pas une plage de cellule, se sont des paramètres passés à la fonction STXT.

• La fonction STXT est une fonction de la catégorie "Texte", elle renvoie une chaîne de caractères extraits de la chaîne de texte A2 à partir de la position que vous avez spécifiée (ici 1), en fonction du nombre de caractères spécifiés.(ici 9)

• STXT est une fonction native dans Excel, tu n'as donc pas besoin de code VBA.

Un autre point important, Le choix entre forçé la valeur et utilisé la liaison, est donné quand l'on compare l'extration du texte de A2 par rapport au terme "Poutre IC". Dans la formule "Poutre IC" est figé, il n'est pas variable et est unique.
As-tu d'autres termes à tester ?

@+Jean-Marie

Très bien.

Alors en fin de compte ce que je pensais être très compliqué à écrire en code est beaucoup plus simple.

Ta solution me convient bien et m'évite d'aller écrire de VBA.

Mais je souhaite bien comprendre STXT(A2;1;9)

- Poutre IC 40x75 est en STXT(A2;1;9)=> OK
- Mais la position (A2;1;9) c'est où ?
- Et pourquoi 9 caractères alors qu'il y en a plus (A2;1;9)  ?

Là compréhension de cette super fonction m'échappe. D'autres part comment fait-elle pour trouver sa correspondance dans la feuille BD ?

Je suis donc intrigué !!!

Bonjour Lagratte,
Hello Jean Marie

Voici un extrait de l'aide excel au sujet des fonctions texte:

Aide Excel sur la fonction TROUVE() :

TROUVE(texte_cherché;texte;no_départ)

texte_cherché   est le texte que vous voulez trouver.

texte   est le texte qui contient celui que vous recherchez.

no_départ   indique le caractère à partir duquel commencer la recherche. Le premier caractère de l'argument texte est le caractère numéro 1. Si l'argument no_départ est omis, la valeur par défaut est 1.

 

Adapté à ton fichier :

En cellule A5 : Poutre IC 40x75

Nombre da caractères de ton libellé :

Caractères:	P	o	u	t	r	e		I	C		4	0	x	7	5
Position:	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15

TROUVE(texte_cherché;texte;no_départ) devient TROUVE("x";A5;1) et cette fonction renvoie 13

(le x est en 13^ème position)

TROUVE("x";A5;1)+1 renvoie 14
Remarque: les caractères ou trouver, comme le X dans ton exemple, sont à placer entre guillements.

 

Aide Excel sur la fonction STXT():

STXT renvoie un nombre donné de caractères extraits d'une chaîne de texte à partir de la position que vous avez spécifiée, en fonction du nombre de caractères spécifiés.

Syntaxe

STXT(texte;no_départ;no_car)

texte   représente la chaîne de texte contenant les caractères à extraire.

no_départ   représente la position dans le texte du premier caractère à extraire. Le premier caractère de texte a un no_départ égal à 1, et ainsi de suite.

no_car   indique le nombre de caractères à extraire du texte à l'aide de STXT.

Adapté à ton fichier: 

STXT(texte;no_départ;no_car) devient

STXT(A5;TROUVE("x";A5;1)+1;10)

Ou encore (en appliquant le résulat de la focntion trouve()) : STXT(A5;14;10)

Concernant ta cellule A5 :

Caractères:	P	o	u	t	r	e		I	C		4	0	x	7	5
Position:	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15

La fonction renvoie 75, soit tous les 10 caractères à partir du 14^ème (10 caractères au lieu de 3 ou 5 histoire de te laisser une marge)

STXT(A5;1;9) renvoie Poutre IC

Pourquoi 9, c’est parce que les 6 lettres de Poutre + les 2 lettres de IC + l’espace entre les deux font en tout 9 caractères.

D’où la condition de la fonction SI : SI(STXT(A5;1;9)="Poutre IC" ; résultat si oui ; résultat si non)

                                                                                  STXT(A5;TROUVE("x";A5;1)+1;10)           SAISIE!E5

En espérant que cela pourra t'aider, et surtout n'hésite pas à user et abuser de l'aide excel, tu y accèdes par touche F1, et tu y trouveras une mine d'explications et d'exemples.

Bonne journée à tous,

mth