VBA bloquée Formule Excel impossible

Bonjour,

Je vous remets rapidement le contexte.

Je me débrouille assez bien sur les formules excel mais là je m'arrive pas à solutionner un problème. Après différentes discussions on m'a répondu que ce que je voulais faire ne pouvais pas être fait en formule EXCEL mais que par VBA. On m'a monté une macro cependant elle ne fonctionne pas et malheureusement je ne sais pas trouver l'erreur.

Explication de ma demande initiale : Un article, plusieurs entrées avec des tarifs différents et des commandes.

Dans l'onglet Entrées + prix : Un article toutes les entrées dans l'ordre de date d'entrée en stock et le tarif par entrée. En colonne G c'est le cumul des volumes.

Dans l'onglet CDE : On retrouve l'article, en colonne B le volume de la commande et en colonne C le cumul des commandes.

Ce que j'ai besoin de faire : En face de chaque ligne commande (dans l'ordre qu'elles sont actuellement) le volume et le prix par ordre d'arrivée des entrées. Si le volume de la commande dépasse l'entrée alors il prend l'entrée d'après et donc le tarif qui va avec et cela jusqu'à épuisement de la première entrée. Dès que la première entrée est épuisée on passe à la deuxième entrée et on remet toujours le tarif qui correspond à l'entrée. Et ainsi de suite

Si je ne suis pas clair, je vous ai mis le fichier du résultat attendu.

Si quelqu'un me trouve une solution en EXCEL je suis plus que preneuse sinon peut on m'aider avec la macro?

MERCI à vous

Bonsoir,

je n'y comprends pas grand chose, si vous pouviez clarifier.

On doit prendre les données dans la feuille commande adh et créer ce qu'il y a sur l'autre ?

Que signifie "Si le volume de la commande dépasse l'entrée" ?

Et qu'est-ce que la première entrée, le n° d'article ?

Pardon pour les explications !

Ce que je cherche à remplir ce sont des colonnes "volumes affectés" et "tarif" dans l'onglet commande adh.

J'ai remplis le tableau pour que vous puissiez avoir le résultat final.

En fait : vous avez un onglet avec les entrees en stock d'un article avec la date d'entrée et le tarif de l'article.

Dans l'onglet commande adh, il y a les commandes de vente de cette article.

J'ai besoin de connaître le tarif d'achat pour chaque ligne de commande de vente et pour cela et je dois utiliser les volumes des entrées en difalquant au fur et à mesure les commandes et prendre le bon tarif qui correspond aux volumes de l'entrée.

Les commandes de ventes et les dates d'entrées sont déjà triées par ordre du plus ancien au plus récent.

Je vous mets un exemple :

Trois entrées en stock sur le même article

-12 janvier : 10 pces à 34€

- 14 janvier : 5 pces à 25€

- 16 janvier : 3 pces à 10 €

Soit un stock au 16 janvier de 18 pces.

Les commandes des ventes

1ere commande : 12 pces

2eme commande : 4 pces

3eme commande : 3 pces

* Pour la première commande de 12 pces je vais prendre :

10 pces à 34€ et 2 pces à 25 € soit 10 pces de l'entrée du 12 janvier et donc à 34€ et 2 pces de l'entrée du 14 janvier et donc 25€.

Je n'ai donc plus rien sur l'entrée du 12 janvier et il me reste 3 pces sur l'entree du 14 janvier.

* Pour la 2eme commande de 4 pces :

3 pces à 25€ et 1 pce à 10€

J'ai donc consommé l'intégralité de l'entree du 14 janvier à 25 euros et 1 pce de l'entrée du 16 janvier

Et ainsi de suite...

Si au bout de les commandes de ventes, je n'ai pas eu assez d'entrées alors rien.

Dites moi si mon exemple est plus clair.

Ok, c'est plus clair.

Mais j'ai encore quelques questions et remarques ;

-pourquoi 3 colonnes pour les volumes et tarifs, est-ce que ça peut être plus pour d'autres articles ? Il y a bien plus que 3 entrées pour l'article 130520.

-ne faudrait-il pas une colonne de total des 3 ?

-le passage d'ordre vertical à horizontal ne simplifie pas les choses.

Je pense qu'on pourrait tout mettre dans le même tableau (comme dans entrées + prix) avec les commandes qui se déduisent des entrées. Ou dans un 2ème tableau à côté avec la même disposition.

Bonjour à vous,

Pour répondre à vos questions :

-pourquoi 3 colonnes pour les volumes et tarifs, est-ce que ça peut être plus pour d'autres articles ? Il y a bien plus que 3 entrées pour l'article 130520 :

Il y a trois colonnes car le volume de commande de vente de cette ligne est de 62 et que j'ai besoin du volume de trois entrées pour combler ma commande soit 5.94 à 400€ (c'est le reliquat de la deuxième entrée) puis 29.96 à 500€ (qui correspond à l'intégralité de la troisième entrée) et 26.10 à 600€ (de la quatrième entrée).

En effet mon exemple est fait que sur un article mais le fichier initial contient une 50ème d'article et c'est pareil pour les entrées et le tarif il contient toutes les entrées et le prix d'achat qui va avec de tous les articles que l'on va retrouver en commande de vente.

-ne faudrait-il pas une colonne de total des 3 ?

Non car je multiplie ensuite le volume de chaque commande par le prix d'achat. Exemple en colonne jaune. Pour bien comprendre je calcul un prévisionnel au bout de tout entre le prix d'achat, le prix de vente et les volumes. Je veux prendre les commandes les plus anciennes et mettre en face le tarif des entrées des produits les plus anciennes pour calculer ensuite ma marge.

-le passage d'ordre vertical à horizontal ne simplifie pas les choses.

Je ne comprends pas trop

- Je pense qu'on pourrait tout mettre dans le même tableau (comme dans entrées + prix) avec les commandes qui se déduisent des entrées. Ou dans un 2ème tableau à côté avec la même disposition.

A aujourd'hui j'ai fait deux onglets d'un coté les commandes de ventes avec tous les articles à la suite et les volumes et dans un autre onglet toutes les entrées et le prix de chaque entrées sur tous les articles. J'ai fait ça pour que ce soit plus simple à travailler mais sincèrement je galère tellement à trouver les formules pour m'aider à faire ces calculs que toutes les propositions sont bonnes.

Je pense qu'il est possible de faire en EXCEL mais j'ai besoin d'aide. Je ne suis pas assez caler en VBA.

Il y a trois colonnes car le volume de commande de vente de cette ligne est de 62 et que j'ai besoin du volume de trois entrées pour combler ma commande

Ok, mais donc ça pourrait être plus dans certains cas je présume. Si oui, il faudra plus de colonnes.

mon exemple est fait que sur un article mais le fichier initial contient une 50ème d'article

Alors un exemple de fichier avec plus d'articles permettrait de bien tester des formules.

Je ne comprends pas trop

Les 2 feuilles n'ont pas la même disposition : les entrées sont les unes au-dessus des autres (même colonne) mais vous voulez faire un calcul sur 3 colonnes (même ligne).

Pour les déductions il serait plus simple d'aller vers le bas au lieu de la droite, d'où ma proposition de mettre un tableau similaire à celui des entrées à côté (ou sur une autre feuille).

Ce qu'on fait généralement pour ce genre de calcul, c'est d'avoir ;

-soit un seul tableau qui regroupe les entrées et sorties et on peut faire un récap en fonction de l'article désiré dans un autre tableau ou à côté

-soit 2 tableaux : un pour les entrées et un pour les sorties et on déduit l'un de l'autre, là aussi en fonction de l'article désiré.

Mais dans les 2 cas la disposition est similaire et simplifie la recherche de données.

bonjour Doux Revêur (salut), Hélène1009,

c'est le système FIFO (First In, First Out)

J'ai utilisé une feuille "Brouillon" pour ne pas toucher à votre feuille "Entrée + Prix"

Bonjour Bart,

C'est exactement ça c'est un système FIFO.

Le résultat est celui que je voulais MERCI. Cependant les données en jaune correspondent au résultat attendu. Normalement les cases sont vides.

- Est ce cas ?

- Si non, je présume que vous avez utilisé une macro pour faire cela. Je ne suis pas calé du tout en macro je vais avoir besoin d'explications :)

J'ai quelques questions :

- J'ai raccourci l'exemple mais normalement j'ai environ lignes de commandes donc les colonnes avec les articles différents et bien sur des entrées sur tous les articles. Est ce que la macro fonctionnera ? Avez vous moyen de faire à l'article ?

- J'utilise ce document tout à long de l'année et bien sur en début d'année j'ai plus de commandes que d'entrées est ce que la macro fonctionnera quand même ?

Dernier point :

Comment j'utilise votre macro ? Quels tableaux, onglets, colonnes vous utilisez pour que je puisse l'utiliser ?

MERCI beaucoup pour votre retour et votre travail qui m'aide énormément

bonjour Doux Revêur, Bart,

Pour vous donner une vue plus exhaustive, vous trouverez ci joint le fichier avec plus de données (Différents articles, volumes d'entrées, ..)

Pour répondre à votre question doux rêveur, vous pouvez modifier les colonnes comme vous voulez tant que nous arrivons à obtenir le résultat en jaune.

Je vous ai mis un exemple du résultat attendu en colonne jaune, bien sur normalement les données sont vides car c'est là ou est besoin de vous :)

Bonjour,

vu qu'il n'y a pas de date sur la feuille de commandes, comment savoir à quelle ligne de stock on doit se référer pour savoir quelle est la quantité en stock au moment de la commande ?

Bonjour,

Je n'ai pas mis les dates sur la feuille de commandes car elles sont déjà dans l'ordre.

Il faut prendre les commandes dans l'ordre. Après si dans vos calculs vous avez besoin de la date je peux l'ajouter

re,

j'ai ajouté la feuille "Brouillon" avec son tableau et puis le module2 (légèrement modifié pour coller les résultats dans les colonnes C:F).

Avec ce bouton "macro FIFO", vous lancez la macro quand vous le voulez (autre possibilité serait de la lancer à l'activation de la feuille "Commandes adh").

Sur la feuille "Brouillon", vous trouvez ce tableau, qui est pour le moment filtré pourqu'on ne voit que ses articles avec un stock >0 (colonne H)

Je l'ai fait comme ca, parce que par exemple la ligne 20 est composé de 4 différents entrées. Le max, je ne l'ose pas dire, Excel est limité à un hauteur de 409 points et le reste sera invisible, donc disons 40 sous-lignes. (A savoir, si vous dépassez ses limites, la calculation reste okay, seulement Excel ne sait pas tout montrer)

Je suppose que lorsque vous faites votre inventaire (annuel), que vous recommencez uniquement avec le stock corrigé de la feuille "Brouillon". (oubien, vous continuez et vous ajoutez quelque lignes de correction dans la feuille "commandes adh").

Important à savoir, vos données de "entrée + prix" sont trié sur article+date d'entrée avant le traitement (enlever le filtre de "brouillon" pour voir la séquence)