Macro - Calcul depuis intervalle de dimension

Bonjour,

Je sollicite votre aide car je suis face à un problème que je n'arrive pas à résoudre.

Je vous explique en détail ce que je désire obtenir.

J'ai quatre feuilles dans ce fichier exemple (j'en ai bien plus dans l'original) dont 2 feuilles du même type dans lesquelles les articles sont groupés par famille.

• données
• cable
• habillage
• Données pr calcul

* Les feuilles "cable" et "habillage" ont la même structure. C'est dans cette partie que se trouve mon problème.

• de U3 à U27 la plage de cellule correspondant à la dimension DIM 1 (longueur)
• de UV à V27 la plage de cellule correspondant à la dimension DIM 2 (largeur)

* Sur la feuille "Données pr calcul" apparaissent les prix en fonction de la dimension désirée en dim1 et dim2 (voir lien plus bas)

Je souhaite que lorsque sur la feuille "cable" les cellule U3 (dim1), V3 (dim2) et T3 (quantité) sont complétées, (c'est le cas dans le fichier) le prix d'achat (contenu dans la feuille "Données pr calcul" en colonne G) correspondant à la dimension demandée apparaîsse en W3.

Si dans la colonne J un prix supérieur à 0 apparait il ne faut pas aller chercher l'information dans la feuille "Données pr calcul".

Par contre, lorsqu'en J le prix est égal à 0 il faut alors aller rechercher l'information dans la feuille "Données pr calcul".

La difficulté que je rencontre est qu'en fonction de l'article le prix d'achat peut être plus difficile à recopier en automatique à cause de l'intervalle de dimensions.

Les articles peuvent être répartis en 2 groupes :

* Article simple : Prix d'achat = Dim1 * Quantité souhaitée

(cas d'un cable)

* Article complexe : Prix d'achat = Dim1 et Dim2 * Quantité souhaitée

(cas d'une porte faite sur mesure et dont le prix variera en fonction de la combinaison longueur / largeur)

Il est possible de se baser sur le "fournisseur" et sur "l'article", pour rechercher les informations d'une feuille à l'autre (les autres informations peuvent ne pas être identiques et ne seront donc pas fiables pour un contrôle efficace).

Un code est déjà présent dans mon fichier mais je n'obtiens toujours pas le bon résultat dans la colonne W (prix d'achat) des feuilles par famille.

Voici les formules que j'ai testé en W3 et qui dans chaque cas donne un message d'erreur :

=tarif(D3;E3;U3;V3;'Données pr calcul'!$B$2:$G$39)

ou

=tarif(D3;E3;U3;V3;DECALER('Données pr calcul'!A2;;;NBVAL('Données pr calcul'!$A:$A)-1;7))

Le fichier faisant 143ko je l'ai mis en ligne à cette adresse :

Pourriez vous s'il vous plaît me dire ce qui ne va pas dans mon fichier (code et/ou formule) ?

Quelle(s) modification(s) dois je apporter à cette macro pour que cela fonctionne correctement ?

Merci d'avance.

Sam357

Re,

Je viens de mettre en ligne mon fichier test dans lequel sont finalisés les calculs annexes.

La problématique de ce post reste toujours tristement d'actualité :

Comment obtenir le prix d'achat depuis la feuille "Données pr calcul" en colonne G vers les feuilles "habillage" et "cable" en colonne X ?

Complément d'informations :

Les colonnes DIM 1 & DIM2 sont destinées à la saisie des dimensions des articles complexes.

Les informations saisies doivent permettre de retrouver le prix d'achat correspondant à cet article pour ces dimensions.

La formule devra rechercher parmi les intervalles de l'article dans la feuille "Données pr calcul".

La colonne Longueur est en mètre (Article simple uniquement) elle est multipliée par le prix d'achat à l'unité (colonne x d'habillage ou cable).

J'aimerais, si cela était possible, mettre cette donnée dans la colonne DIM1 mais, ne sachant comment faire j'ai opter par dépit pour l'utilisation de la colonne longueur (W).

Les données obtenues dans la colonne Frais fixe (Y) proviennent de "données pr calcul" (colonne I) et sont multipliées par la Quantité souhaitée (colonneT).

Objectif de ma demande :

Obtenir en X le Prix d'achat à l'unité correspondant à l'article (cas d'un article simple) et aux dimensions DIM1 & DIM2 (cas d'un article complexe).

PS : La fonction que j'avais cité plus haut avait répondu exactement à ce que je cherchais. Malheureusement ne sachant comment l'adapter, je l'ai mis dans mon post en espérant vous faciliter la tache. Elle est toujours présente dans le module vb de mon fichier au cas où vous désireriez partir de ça.

Néanmoins, mon intention n'est pas de limiter votre marge de manoeuvre, toutes les suggestions seront les bienvenues, d'autant plus que je dois avancer au plus vite sur ce projet.

Merci d'avance pour votre aide.

Cordialement,

Sam357

Bonjour à tous,

Sam, à voir l'ampleur de ton message, on a pas envie de suivre !

un conseil: synthétise un max, tu aura davantage de chance d'être lu.

Amicalement

Claude.

Re - bonjour,

Justement c'est ce que j'ai cherché à faire dans mon deuxième message, tout en éludant pas les points importants.

Je pense que mon fichier (la dernière version) est maintenant assez parlant mais, il fallait bien que j'explique ce que je cherche à obtenir.

Enfin, si l'un des membres du forum voulait bien m'apporter son aide j'en serai très content car mon niveau sur Excel n'est pas phénoménal, surtout lorsqu'il s'agit de développer une macro.

Merci d'avance.

Cordialement,

Sam357

Bonsoir

Je ne saisis pas bien l'utilité des colonnes DIM 1 et DIM 2 sur la feuille cable.

Après avoir remplacé les vides de la colonne DIM 1 de la feuille "Données pr calcul" par 1, on obtiens le prix unitaire en colonne X de la feuille "Cable" avec une formule style

=SI(J3>0;J3;SOMMEPROD(('Données pr calcul'!$C$2:$C$39=E3)*('Données pr calcul'!$E$2:$E$39=Cable!U3)*('Données pr calcul'!$G$2:$G$39)))

Ceci pour démarrer le sujet.

Cordialement

Bonsoir Amadéus,

En effet, les colonnes DIM1 & DIM 2 peuvent ne pas sembler pertinentes dans la feuille cable, mais elles y sont car dans j'ai considéré que mon format de (feuille dite par famille) devait être standard (j'ai environ 7 feuilles du même type où les articles sont organisés par univers).

En appliquant ta méthode j'obtiens effectivement un résultat pour les articles simples.

Mais comment faire pour les articles avec une DIM1 et une DIM2 dont le prix est fonction d'un intervalle de grandeur ?

2 types d'articles :

* Article simple : Prix d'achat = Dim1 * Quantité souhaitée

(cas d'un cable)

* Article complexe : Prix d'achat = Dim1 et Dim2 * Quantité souhaitée

(cas d'une porte faite sur mesure et dont le prix variera en fonction de la combinaison longueur / largeur)

Merci d'avance.

Cordialement,

Sam357

Bonjour amadéus, sam, forum

Regarde dans le fichier suivant si ça te convient sam : https://www.excel-pratique.com/~files/doc2/hDpv3D8N0I_Macro_sur_Calcul_a_partir_d_intervalles_de_tailles_V1.zip

- J'ai modifié le code de la fonction. J'ai essayé de pointer les modifs que j'ai faites (voir code).

- J'ai supprimé la colonne W (longueur), tu devrais ne plus en avoir besoin

- Si c'est un article simple (qui ne comporte donc qu'une longueur si j'ai bien compris )

tu devras saisir la longueur seulement dans la colonne DIM1, comme souhaité.

- S'il n'y a que DIM2 qui est renseigné, aucun prix ne s'affichera. On pourrait faire en

sorte que si une seule valeur est saisie (DIM1 OU DIM2), la fonction détecte que

c'est une longueur. Mais ça va complexifier un peu le code

- Dans le dernier fichier que tu as posté, j'ai modifié la formule permettant de calculer le

prix de vente estimé. La formule n'est pas la même que dans le tout premier fichier que

tu as posté, c'est normal ça?

Avec ça, on devrait avoir bien progressé

A toi de me dire si j'ai oublié un truc sam!

PS :

sam357 a écrit :

Le fichier faisant 143ko je l'ai mis en ligne à cette adresse

Bonjour à tous,

- J'ai modifié le code de la fonction. J'ai essayé de pointer les modifs que j'ai faites (voir code).

• J'ai supprimé la colonne W (longueur), tu devrais ne plus en avoir besoin.
• Si c'est un article simple (qui ne comporte donc qu'une longueur si j'ai bien compris Question )

tu devras saisir la longueur seulement dans la colonne DIM1, comme souhaité.

Oui c'est bien ça.

Excellent, une colonne en moins ! C'est exactement ce que je voulais.

- S'il n'y a que DIM2 qui est renseigné, aucun prix ne s'affichera.

Cette solution me convient tout à fait comme ça.

- Dans le dernier fichier que tu as posté, j'ai modifié la formule permettant de calculer le prix de vente estimé. La formule n'est pas la même que dans le tout premier fichier que

tu as posté, c'est normal ça?

Oui, en effet je l’avais modifié sur le dernier fichier test. Mon but était qu'elle soit la moins lourde possible car je vais avoir 700 lignes par feuille (7 à 8 feuilles de familles + 2 feuilles (données + données pour calcul). J'appréhende la taille du fichier une fois finalisé.

A toi de me dire si j'ai oublié un truc sam!

Il y a un point ou sur les DIM1 et DIM2.

Je prends le cas ci-dessous :

Fournisseur Article Description

COMETE A438AN999 INOX DECOR bronze

Lorsque les DIM1 ou DIM2 mentionnées dans la feuille Données pr calcul sont des longueurs fixes et non des intervalles (voir cas ci-dessous) cela signifie que l’article existe uniquement dans la taille indiquée. Je ne dois donc pas avoir de Prix d'achat à l'unité (ML; pièces) renvoyé dans la feuille habillage si je rentre en DIM1 600 et en DIM2 591.

Fournisseur Article Description DIM1 DIM2 Prix d'achat

COMETE A438AN999 INOX DECOR bronze 700 590 52,56

De même, pour le cas d’un article simple si la longueur est fixe et qu’un câble existe uniquement en 20 mètres, est il possible de renvoyer un message d’erreur ? Si non, je peux toujours l’indiquer dans la cellule Info Complémentaire correspondante.

Je sais que cela n'était pas spécifié dans ma demande initiale, désolé.

Avec ça, on devrait avoir bien progressé.

C'est le moins qu'on puisse dire.

Tu peux le compresser si tu veux! Ça va le faire maigrir de beaucoup Rolling Eyes

Ok je n'y manquerai pas la prochaine fois.

Je te remercie de te pencher sur mon problème.

Cordialement,

Sam357

Bonjour forum, sam,

sam357 a écrit :

...De même, pour le cas d’un article simple si la longueur est fixe et qu’un câble existe uniquement en 20 mètres...

Comment on sait que tel ou tel câble n'existe qu'en longueur unique? Est-ce qu'on peut le savoir à partir de la feuille Données pr calcul?

Bonjour Forum,vba-new,

Comment on sait que tel ou tel câble n'existe qu'en longueur unique? Est-ce qu'on peut le savoir à partir de la feuille Données pr calcul?

Oui c'est dans la feuille Données pr calcul du fichier disponible ci dessous. L'article ci-dessous y sert d'exemple pour ma demande. J'ai indiqué une longeur de 70 en DIM1 afin de pouvoir tester.

Fournisseur Article Description DIM1

AUXIFER 4141101 CABLE F048 24X0,5 70

https://www.excel-pratique.com/~files/doc2/Macro_sur_Calcul_a_partir_d_intervalles_de_tailles_V1.zip

Merci.

Cordialement,

Sam357

Re,

J'ai quelques précisions à demander.

sam357 a écrit :

De même, pour le cas d’un article simple si la longueur est fixe et qu’un câble existe uniquement en 20 mètres, est il possible de renvoyer un message d’erreur ?

1. Où veux-tu mettre le message d'erreur?

2. Si tu as besoin de 32 mètres de câbles, il n'y a aucun moyen de raccorder 2 câbles de 20 mètres je suppose?

Re,

1. Où veux-tu mettre le message d'erreur?

Un message du type "tarif inconnu," renvoyé dans la cellule Prix d'achat à l'unité (ML; pièces) correspondante, conviendrait parfaitement.

2. Si tu as besoin de 32 mètres de câbles, il n'y a aucun moyen de raccorder 2 câbles de 20 mètres je suppose?

Non cela n'est pas possible. Toutefois, ce même type de cable peut tout à fait être disponible dans une dimension supérieure (resterait alors à le couper) mais avec un code article différent.

Si tu as d'autres questions, n'hésites pas.

Merci.

Sam357

Re,

sam357 a écrit :

Un message du type "tarif inconnu," renvoyé dans la cellule Prix d'achat à l'unité (ML; pièces) correspondante, conviendrait parfaitement.

Ah ..., je me suis compliqué la vie alors, j'ai modifié la fonction pour qu'elle affiche un message du genre "Longueur max du câble : ... m" si la longueur demandée dépassait la longueur normale du câble!

Je laisse ou met "Tarif inconnu"?

C'est re-moi,

Modifications effectuées pour le problèmes des dimensions uniques et la longueur de câble maximale! Voir fichier ci-après : https://www.excel-pratique.com/~files/doc2/Macro_sur_Calcul_a_partir_d_intervalles_de_tailles_V2.zip

Teste et redis-moi s'il y a des trucs qui vont pas.

Re,

Après quelques vérification, j'ai comme l'impression que tout est ok.

Je vais maintenant la tester en grandeur nature.

Je reviens pour te dire la suite...

Merci pour ton investissement.

Sam357

Re, forum, vba-new,

Suite au passage sur mon fichier final il y a quelques points qui ne fonctionnent pas correctement.

* Une même formule peut trouver le résultat pour un article mais pas pour un autre. Je retesterai ce point demain matin mais il semble que la formule ne fonctionne pas lorsque les données (ex : fa2354) de la cellule E3 proviennent d'une formule :

=SI(Données!$A2="habillage";Données!E2;"") => réponse en w3 = tarif inconnu

Par contre si en E3 je colle cette même référence copiée depuis la feuille "données pour calcul" alors là j'ai le résultat souhaité.

Mon problème et que je ne me vois pas chercher sur 700 lignes * 7 feuilles les articles. Enfin en raison des mises à jour du fichier je ne peux faire ça.

J'avais pensé à la police, à la catégorie mais les 2 sont en standard.

Que dois je faire face à ce problème car je ne comprends pas d'où il peut provenir ?

=SI(NON(ESTNUM(W98));"Prix achat Uté inconnu";SI(V98<>"";W98*T98+X98;X98+(U98*W98)*T98))

* Calcul des frais fixe :

=RECHERCHEV(E3;'Données pr calcul'!$C$4:$K$350;7;FAUX)*T98

Il faudrait que dans l'hypothèse où l'article est introuvable dans la feuille "données pr calcul" (il n'aura donc pas de frais fixe indiqué) mais qu'un prix apparait en J3 de la feuille famille.

Il faudrait que le message qui apparaitra en X3 n'empêche pas le calcul du"Prix de Vente estimé"

Si la cellule pouvait ressortir vide ou colorée serait très bien (la solution la moins compliquée sera la meilleur).

* Ajout d'une condition supplémentaire pour calcul du prix :

Formule actuelle :

=SI(NON(ESTNUM(W3));"Prix achat Uté inconnu";SI(V3<>"";W3*T3+X3;X3+(U3*W3)*T3))

J'ai cherché une formule comme ci-dessous mais un message m'indique que j'ai trop de conditions.

=SI(NON(ESTNUM(W3));"Prix achat Uté inconnu";SI(V3<>"";W3*T3+X3;X3+(U3*W3)*T3;T3*W3+X3)

Je cherche à calculer le "Prix de Vente estimé" dans le cas d'un article tel qu'une lampe torche qui n'a ni DIM1 ni DIM2, pour lequel seules les cases "Quantité souhaitée" * "Prix d'achat à l'unité (ML; pièces)" + "Frais fixe" (encore une fois il faut que le calcul ds frais fixe ne génère pas de message empechant le calcul du "prix de vente estimé")' comporteront des données.

* Plage dynamique :

J'ai voulu en colonne Z de la feuille cable faire en sorte que la zone désignée soit dynamique et s'adapte en fonction du nombre de ligne ajoutée ou supprimée. J'ai donc nommé la zone mais il semble que ça ne fonctionne pas.

Formule initiale :

=RECHERCHEV(E3;'Données pr calcul'!$C$2:$K$39;9;FAUX)

Modifiée :

en z3

=RECHERCHEV(E3;Plagebaremes;9;FAUX)

Voici ce que j'ai entré dans definir-nom - zone plagebaremes

=DECALER('Données pr calcul'!$C$2:$k$2;;;NBVAL('Données pr calcul'!$C:$k)-1;1)

Qu'est ce qui ne va pas dans ma formule ?

Il est très tard je vais me coucher mais si une de mes questions devaient ne pas être assez précises n'hésites pas à m'interroger...

Merci par avance.

Sam357

Waouh ça fait peur tout ça!

Pour l'instant, juste une petite remarque :

sam357 a écrit :

J'ai cherché une formule comme ci-dessous mais un message m'indique que j'ai trop de conditions.

=SI(NON(ESTNUM(W3));"Prix achat Uté inconnu";SI(V3<>"";W3*T3+X3;X3+(U3*W3)*T3;T3*W3+X3)

Il y a 2 fois le même calcul, c'est normal? -> =SI(NON(ESTNUM(W3));"Prix achat Uté inconnu";SI(V3<>"";W3*T3+X3;X3+(U3*W3)*T3;T3*W3+X3)

Je serai peut-être pas là demain, je regarderai tout ça quand j'aurai le temps.

Je ne sais pas si je pourrai t'aider sur certains points, mes compétences en formules

n'étant pas assez au point! Mais nul doute que tu recevras de l'aide d'autres exceliens

Re,

Il y a 2 fois le même calcul, c'est normal? -> =SI(NON(ESTNUM(W3));"Prix achat Uté inconnu";SI(V3<>"";W3*T3+X3;X3+(U3*W3)*T3;T3*W3+X3)

Quelqu'un aurait il une tu une idée sur la formule pour :

* Plage dynamique :

J'ai voulu en colonne Z de la feuille cable faire en sorte que la zone désignée soit dynamique et s'adapte en fonction du nombre de ligne ajoutée ou supprimée. J'ai donc nommé la zone mais il semble que ça ne fonctionne pas.

Formule initiale :

=RECHERCHEV(E3;'Données pr calcul'!$C$2:$K$39;9;FAUX)

Modifiée :

en z3

=RECHERCHEV(E3;Plagebaremes;9;FAUX)

Voici ce que j'ai entré dans definir-nom - zone plagebaremes

=DECALER('Données pr calcul'!$C$2:$k$2;;;NBVAL('Données pr calcul'!$C:$k)-1;1)

Qu'est ce qui ne va pas dans ma formule ?

et

* Calcul des frais fixe :

=RECHERCHEV(E3;'Données pr calcul'!$C$4:$K$350;7;FAUX)*T98

Il faudrait que dans l'hypothèse où l'article est introuvable dans la feuille "données pr calcul" (il n'aura donc pas de frais fixe indiqué) mais qu'un prix apparait en J3 de la feuille famille.

Il faudrait que le message qui apparaitra en X3 n'empêche pas le calcul du"Prix de Vente estimé"

Si la cellule pouvait ressortir vide ou colorée serait très bien (la solution la moins compliquée sera la meilleur).

Merci par avance.

Cordialement,

Sam357

Bonjour sam, forum,

Désolé pour l'attente.

Je viens de remarquer qu'il y a un gros problème qui m'empêche de t'aider correctement sam. On travaille pas sur le même fichier!

sam357 a écrit :

Suite au passage sur mon fichier final il y a quelques points qui ne fonctionnent pas correctement.

Certes

sam357 a écrit :

...il semble que la formule ne fonctionne pas lorsque les données (ex : fa2354) de la cellule E3 proviennent d'une formule :

Je n'ai pas de fa2354!

sam357 a écrit :

* Calcul des frais fixe :

=RECHERCHEV(E3;'Données pr calcul'!$C$4:$K$350;7;FAUX)*T98

Les données que j'ai ne vont pas jusqu'à la ligne 98!

sam357 a écrit :

...mais qu'un prix apparait en J3 de la feuille famille.

Je n'ai pas la feuille famille

Bref, avant de continuer à t'aider ça serait pas mal qu'on travaille sur un fichier commun tu penses pas?

A toi de régler ce problème