Automatisation méthode FIFO avec macro VBA

Bonjour à vous,

je suis vraiment débutante dans les macros. Pouvez- s'il vous plait m'aider? Merci d'avance.

J'aimerais s'il vous plait savoir comment créer une macro permettant d'automatiser la feuille « Base de travail » de telle sorte que j'obtienne comme résultat final la feuille « Objectif » ? Voir lien suivant :

  • Dans la feuille « Base de travail » qui correspond au fichier initial :
  • Les entrées (recettes), les sorties (dépenses ou opération de vente) et le stock initial, seront renseignés manuellement.

- Les opérations de ventes seront toujours en caractère gras et rouge (voir feuille « Base de travail ») pour que la macro une fois créée sache qu'il s'agit bien d'une opération de vente et « non d'une sortie toute simple ».

  • La macro que j'aimerais créer me permettrai de manière automatique d'appliquer sur les entrées (ou sorties renseignées manuellement les règles suivantes :
  • selon si c'est :

1. une entrée (recettes) =) application de la règle 1 : ajout de la recette avec son cours dans le stock initial.

2. une sortie (dépenses) =) application de la règle 2 : la méthode FIFO (premier entrée, premier sortie), la dépense est retirée de la première quantité se trouvant dans le stock c'est-à-dire la quantité dont la date est la plus ancienne, puis après la première quantité la plus ancienne, la deuxième quantité la plus ancienne et ainsi de suite jusqu'à épuisement de la dépense.

3. Une sortie (opération de ventes) =) application de la règle 3 : « première quantité dont le cours est le plus élevé, premier sortie ». On choisit de sortir la quantité dont le cours est le plus élevé jusqu'à épuisement de l'opération de vente (cf lien).

4. Enfin la règle 4 : si deux quantités différentes, ont le même cours, que se soit en méthode FIFO « premier entrée, premier sortie » ou par la règle créée en cas de sortie (opération de vente), « premier en cours le plus élevé, premier sortie », la macro créée doit sortir automatiquement la quantité dont la date est la plus ancienne.

Voir lien suivant :

).

N'hésitez pas à me demander pour toutes informations complémentaires

Merci pour votre réponse.

Bien cordialement

jacinthelafleur

Bonjour

J'avoue ne pas comprendre comment tu passes de la feuille Base de Travail à la feuille Objectif.

Exemple 1

Les entrées des 2 et 3 janvier de la feuille BdT sont reportées dans les colonnes Stock sur la feuille Objectif

L'entrée du 7 janvier ne l'est pas. Pourquoi ?

Exemple 2

Tu indiques que sur la BdT, les opérations de vente sont toujours en gras et rouge

Or, le 9 janvier, la sortie est bien ainsi mais elle perd sa couleur dans la feuille Objectif

Exemple 3

Inversement, sur la feuille Objectif,on trouve des couleurs rouges dans les colonnes Stock qui ne le sont pas sur la feuille BdT

Il faudrait qu'un certains nombre de jours (à indiquer), à partir du début soient remplies exactement comme tu le souhaites. Rien ne vaut un bon exemple pour bien appliquer un règle.

Mais peut-ête n'ai-je rien compris ....

Bonjour,

Merci beaucoup pour la réponse.

Oui c'est vrai que j'ai fais une erreur en ce qui concerne la date du 7 Janvier, j'ai oublié d'intégrer l'entrée du 7 Janvier.

La feuille " Objectif " est sensée être le résultat à obtenir une fois que j'aurai tout automatiser grâce à la macro!!!

Comment passer de la feuille "BDT" à "Objectif" en appliquant la méthode FIFO pour les sorties et pour les opérations de ventes la méthode " premier en cours le plus élevé, premier sortie.

Vous avez raison je vais refaire un autre fichier plus clair et je reviendrai vers vous. Pensez-vous pouvoir m'aider si je fournie des explications plus claires?

Merci d'avance

Bien cordialement.

jacinthelafleur

Bonjour

Vous avez écrit :

Pensez-vous pouvoir m'aider si je fournie des explications plus claires?

En tout cas, je veux bien essayer...

A vous lire

Bonjour,

j'ai essayé d'être plus claire dans mes explications. Voici le lien du fichier :

et le lien avec des explications complémentaires

N'hésitez pas à me demander pour toutes informations complémentaires!!

Merci pour votre aide.

Bien cordialement

jacinthelafleur

Bonjour

Pourquoi tes deux tableaux, l'un sur la feuille "Base de travail" et l'autre sur la feuille "Objectif" ne sont-ils pas commentés comme je te le damandais ?

Pour expliquer, tu donnes une suite d'exemples sans montrer comment ils se traduisent sur ces deux feuilles. N'est-ce pas pourtant ce que tu veux : qu'une macro te fasse passer d'une feuille à l'autre ?

Tu ne nous facilites pas la tâche....

Bonjour,

Merci de votre retour.

je vais faire le necessaire pour commenter la feuille " base de travail " et "objectif" comme vous me le demandiez et je reviendrai vers vous dans l'après-midi.

je pensais qu' en vous donnant exemple un tout simple, la compréhension serait plus facile et claire.

C'est bien le passage de la feuille "Base de travail" à "objectif" que j'aimerai automatiser à l'aide d'une macro.

Encore désolé si je ne vous facilite pas la tâche.

Bien cordialement.

jacinthelafleur

Bonjour,

Ci-joint le fichier modifié

Sur les feuilles " Base de travail ", et " Objectif ", j'ai inséré des commentaires afin que vous puissiez comprendre le fonctionnement de ces deux feuilles et comment je passe de la feuille "Base de travail " à "Objectif".

Les feuilles "Base de travail" et "objectifs" ne représentent que des échantillons car dans le vrai fichier il y a plusieurs entrées, dépenses et opérations de ventes.

J'ai aussi crée une macro (voir feuille "macro") avec l'enregistreur de macro pour que vous ayez une petite idée de la manipulation que j'aimerais faire.

L'objectif est qu'à chaque fois qu'il y'a une entrée , que la macro créée permette de manière automatique d'appliquer la règle 1, lorsqu'il y a une sortie (dépenses) qu'elle applique la règle 2 c'est à dire la méthode FIFO et enfin lorsqu'il y a une sortie (opération de vente), qu'elle applique la règle 3 c'est à dire "premier en cours le plus élevé, premier sorti"( voir feuille "objectif").

Pour que la macro puisse faire la différence entre une sortie ( dépenses) et une sortie (opération de vente), j'ai décidé à chaque fois qu'il y a une opération de vente, de mettre en caractère gras et rouge les quantités et le cours correspondant à cette opération de vente.

N'hésitez pas à me dire si je n'ai pas été assez claire.

Merci d'avance pour votre réponse .

Jacinthelafleur

Bonjour

Dommage que l’on n’ait pas les dépenses et les ventes dans des colonnes différentes mais on a fait avec.

Cela a comme conséquence que la macro, pour distinguer doit repérer la couleur de la vente. Cela peut être gênant car VBA a 255 rouges différents (et autant de vert et de bleu…)

Si le rouge de la « Sortie » n’a pas le même numéro que celui qu’elle a en dur dans son code, elle ne le reconnaîtra pas.

Aussi, par précaution, je t’ai mis un bouton « Rouge vente » que je te conseille vivement d’utiliser pour mettre en rouge tes sorties « Vente ». Le rouge qui coloriera la donnée sera celui reconnu par la macro « Suivi de stock »

Fonctionnement

On peut commencer à la ligne 4, en supposant que la ligne 3 soit remplie : Date et stocks initial ;

On met à jour le tableau, colonnes G et H, à partir des données existantes à la date précédente.

Pour cela :

• on sélectionne une cellule quelconque sur la ligne de la date concernée ou une ligne en dessous, mais avant la date suivante pour que les valeurs des colonnes G et H soient remplies

• on clique sur le bouton bleu « Suivi du stock »

Pour remplir le tableau de la feuille « Base de travail » il faut donc commencer par sélectionner une cellule de la ligne 4 ou de la ligne 5 puis de cliquer sur le bouton bleu autant de fois qu’il y a de dates en dessous. Bien sûr dans l’ordre…. !

Remarques

• J’ai du utiliser provisoirement des cellules de la colonne 9 (I) dans le cas des ventes, pour classer les cours dans l’ordre croissant de leur valeur, avant de rétablir l’ordre initial en fin de programme. Il faut donc éviter d’y mettre des données. La macro marcherait sans problème mais les données risqueraient d’être perdues

• Je n’ai pas touché (ou involontairement) aux couleurs des cellules : si elles obéissent à quelque règle, il faut me le dire…

On pourrait aussi imaginer que le travail soit refait en un clic pour toutes les dates, d’un coup, les données étant déjà entrées. Cela pourrait être utile en cas d’erreur de saisie (ou de couleur) à une date ancienne…

A toi de me dire si cela t’intéresse.

Voilà. Teste tout cela et dis-moi si cela fonctionne comme tu le souhaites.

Je reste à ta disposition.

A te lire

Bye

152stock-fifo-v1.xlsm (225.80 Ko)

Bonjour

Merci beaucoup pour votre aide précieuse!!

Actuellement, je suis entrain de tester la macro sur mon fichier.

Cependant dès le départ je rencontre des difficultés. En effet en appliquant la manipulation sur mon fichier, lorsque je clique sur le bouton « suivi du stock », un message : erreur de compilation, variable non définie apparait « SortOn:=xlSortOnValues » ; « xlSortOnValues » est surligné en bleu. S'il vous plaît où est ce que j’ai pu faire une erreur ?

" On pourrait aussi imaginer que le travail soit refait en un clic pour toutes les dates, d’un coup, les données étant déjà entrées. Cela pourrait être utile en cas d’erreur de saisie (ou de couleur) à une date ancienne…

A toi de me dire si cela t’intéresse ". Oui cela m'interesserai bien, si vous le voulez bien....

Merci d'avance pour votre réponse!

jacinthelafleur

Re bonjour,

je crois savoir d'où vient mon problème d'erreur de compilation, variable non definie « xlSortOnValues ». Je travaille actuellement sur une version d'excel 2003, pensez-vous que le problème est dû à cela? Si oui s'il vous plaît comment modifier mon code pour l'adapter à la version 2003?

Merci d'avance pour votre réponse.

Jacinthelafleur

Re bonjour,

juste pour vous dire que j'ai pu régler le problème qui apparaissait dès le début en ce qui concerne "xlSortValue" en modifiant mon code car je travaille actuellement sur une version excel 2003.

- je reviendrai vers vous si j'ai un autre un soucis qui apparait.

" On pourrait aussi imaginer que le travail soit refait en un clic pour toutes les dates, d’un coup, les données étant déjà entrées. Cela pourrait être utile en cas d’erreur de saisie (ou de couleur) à une date ancienne…

A toi de me dire si cela t’intéresse ". Oui cela m'interesserai bien, si vous le voulez bien....

Merci beaucoup.

Jacinthelafleur

Re bonjour!!

une fois que je tape " mon opération de vente ", et qu'ensuite je clique sur "vente rouge" puis " suivi stock", une ereur d'exécution 438 apparait c'est à dire "propriété ou méthode non géréé par cet objet", je vais ensuite sur débogage pour savoir qu'elle est mon erreur et je voisTintAndShade = 0surligné en jaune.

Sub RougeVente()

Selection.Font.Bold = True

With Selection.Font

.Color = 255

.TintAndShade = 0 End With

End Sub

Pouvez vous m'aider SVP? Pour savoir où ai-je fais l'erreur?

Merci d'avance pour votre réponse.

jacinthelafleur

Bonjour

Rassure-toi ! Tu n'y es pour rien. Il y avait un bug ...

Cela devrait mieux marcher. Teste et dis-moi.

Je m'occupe de la version "Vérification complète"

A te lire

Bye

35stock-fifo-v2.xlsm (225.78 Ko)

Bonjour

Voilà la version 3

Elle a 2 boutons :

* le bleu ne met ou remet à jour que les données de la dernière date, celle du bas du tableau.

* le mauve qui efface toutes les données des colonnes G et H, sauf celles de la ligne 3, (des stocks), efface également le quadrillage et recalcule tout depuis le début.

Il est possible que la macro bloque encore en particulier à cause du rouge.

VBA a modifié ses instructions sur les couleurs entre les versions de 2003, 2007 20010 ... alors, si c'est le cas, on modifiera encore.

Patience, mes étourderies et les facéties d'Excel, ne nous empêcheront pas d'y arriver.

Persévérons !

A te lire

30stock-fifo-v3.xlsm (177.55 Ko)

Bonjour,

Merci encore pour ton aide si précieuse!!!

j'ai testé la version 2 du doc et je rencontre toujours ce même problème T[Surligner]intAndShade = 0[/Surligner] est surligné en jaune, donc la macro bug .

Effectivement je crois que ce problème est peut être dû à la version excel que j'utilise mais aussi à la couleur "rouge". J'utilise la version 2003. Comment SVP modifier le code de macro pour que celle-ci puisse fonctionner avec une version d'excel 2003?

En ce qui concerne "les opérations de ventes", pensez-vous que nous pouvons contourner le problème de "la couleur rouge" en créant deux nouvelles colonnes dont l'une correspondrait à l'opération de vente et l'autre au cours auquel s'est fait l'opération de vente ? Mais dans ce cas cela pourrait générer des modifications très importantes au niveau de la macro. Si c'est trop compliqué et fastidieux ne vous embêtez pas à le faire. On trouvera bien une solution pour contourner ce problème et pour que la macro puisse être en mesure de reconnaître s'il s'agit d'une dépense ou opération de vente.

Je n'ai pas encore tester le fichier version 3, je reviendrai vers vous si j'ai un soucis. Mais à priori il devrait y en avoir un car j'utilise une version 2003 d'excel , ce qui peut ne pas être compatible avec le code de la macro.

Merci d'avance pour votre réponse.

Bien cordialement

jacinthelafleur

Bonjour

J'ai testé la macro avec une version 2003 d'Excel.

Et, surprise, il n'y avait pas que le rouge des ventes qui posaient problème.

Il y avait aussi les tris qui permettent d'appliquer les règles.

Maintenant, si tout va bien, tu as les deux versions.: 2003 et 2010

Si tu veux alterner les couleurs des colonnes G et H, on peut l'envisager...

Dis-moi en tout cas si ça marche enfin !

Bye

Bonjour,

ça marche!!!!! tout tourne bien!!!!

Merci beaucoup beaucoup!!!!

jacinthelafleur

Bonjour à vous gmb!

je me permets de vous écrire car j'ai un petit soucis avec le même fichier.

En effet lorsque je mets "-2500" avec pour cours 122.32 comme opération de vente, tout fonctionne bien, tandis que lorsque je mets "700000000" comme opération de vente avec pour cours 122.1213 par exemple, la macro ne fonctionne plus tout, je clique sur débogage et la ligne suivante apparait en jaune : "Cells(LnF + 1, 1).Select".

Pouvez vous SVP me dire ou se trouve mon erreur ou quelles sont les modifications à apporter sur la macro de telle sorte que le fichier puisse marcher avec des opérations de vente du type " 700000000"?

Par curiosité j'ai modifié les "700000000" d'opération de vente par "7000000" tout fonctionne bien, et lorsque je mets " 70000000" , la macro ne fonctionne plus et la ligne suivante apparait toujours en jaune "Cells(LnF + 1, 1).Select".

Merci pour votre réponse

jacinthelafleur

Bonjour jacinthe

J'ai refait l'exercice sur le dernier document que je t'ai envoyé et, en effet si j'ajoute une vente (nombre négatif) de -7 000 000 en cellule E28, la macro tourme normalement

Si je mets un zéro de plus soit - 70 000 000, la macro plante.

J'aurais dû mettre un message d'alerte.

Car à y regarder de plus près, on s'aperçoit dans les lignes au-dessus que l'on ne dispose que de :

15 456 981,81 + 361,39 + 52500 + 1 585,24

soit de 15 511 428.44 C'est beaucoup moins que 70 000 000

Je pensais que ce cas ne se produirait jamais, car il me semblait que l'on ne pouvait pas vendre plus que l'on n'avait.

Apparemment, j'avais tort. Alors, je te poses la question : Que doit-on faire dans ce cas ?

* Mettre une alarme pour dire que la vente envisagée ne peut être faite,faute de disposer de quantité suffisante ?

* Faire quand même l'opération et écrire une valeur négative dans la colonne des quantités, dans l'exemple ce serait - 54 448 571.56

* Ou autre chose ?

A te lire

Rechercher des sujets similaires à "automatisation methode fifo macro vba"