XL 2013 Décrémentation stock en fonction de la référence et du besoin

Poulet

XLDnaute Nouveau
Bonsoir à tous,

Je me permets de vous contacter car j'aurai besoin d'aide sur mon fichier ci-joint ou j'ai tenté d'expliquer au mieux ma problématique.

Je ne suis pas sûr que mon problème peut se résoudre via des formules même imbriquées, il va probablement falloir à mon avis passé par du VBA, des boucles, définition de variables etc...
Mais mon niveau en code me rattrape rapidement donc je fais appel à vous.

Si vous pouviez jeter un œil, j'en serai très reconnaissant

Merci de votre aide.

Très cordialement
 

JHA

XLDnaute Barbatruc
Bonjour à tous,

Un début de piste par formule matricielle
VB:
=SIERREUR(SI(NB.SI($C$6:$C6;$C6)=1;RECHERCHEV($C6;BaseStock;2;FAUX)-Feuil1!$D6;INDEX(Stock;GRANDE.VALEUR(SI($C$5:$C5=$C6;LIGNE($C$5:$C5)-5);1))-$D6);-$D6)

Copier vers le bas après validation des 3 touches Ctrl+Maj+Entree

JHA
 

Pièces jointes

  • Decrementation stock en fonction de la référence et du besoin 1.xlsm
    12.6 KB · Affichages: 7

soan

XLDnaute Barbatruc
Inactif
Bonjour Poulet, le fil,

ton fichier en retour ; sur "Feuil1", fais Ctrl e ➯ travail effectué ! 😊

sur "Feuil2", regarde C4 ! 😁 eh oui, hein ? tu avais fait une erreur !

tu avais mis 45 au lieu de 935 ! 45 - 890 aurait donné : -845 ;
alors que 935 - 890 = 45 ; c'est quand même plus juste ! 😜



code VBA de Module1 :

VB:
Option Explicit

Dim T, QT%, n2&, i&

Private Sub Job()
  QT = QT - T(i, 2): T(i, 3) = QT: T(i, 4) = 1: n2 = n2 + 1
End Sub

Sub Essai()
  If ActiveSheet.Name <> "Feuil1" Then Exit Sub
  Dim n1&: n1 = Cells(Rows.Count, 3).End(3).Row: If n1 = 5 Then Exit Sub
  Dim cel As Range, ref$, j&: Application.ScreenUpdating = 0
  Columns("E:F").ClearContents: [E5] = "Stock"
  n1 = n1 - 5: T = [C6].Resize(n1, 4): n2 = 0
  Do
    'recherche d'une 1ère référence
    ref = "": i = 1
    Do
      If ref = "" And T(i, 4) = 0 Then
        ref = T(i, 1): QT = 0
        Set cel = Worksheets("Feuil2").Columns(2).Find(ref, , -4163, 1, 1)
        If Not cel Is Nothing Then QT = cel.Offset(, 1)
        Call Job: j = i + 1: Exit Do
      Else
        i = i + 1
      End If
    Loop Until i > n1
    'traitement de toutes les lignes de la 1ère référence ci-dessus
    For i = j To n1
      If ref <> "" And T(i, 1) = ref And T(i, 4) = 0 Then Job
    Next i
  Loop Until n2 = n1
  [E6].Resize(n1) = Application.Index(T, Evaluate("Row(" & "1:" & n1 & ")"), 3)
End Sub

si besoin, tu peux demander une adaptation.
à te lire pour avoir ton avis. 😉

soan
 

Pièces jointes

  • Décrémentation stock en fonction de la référence et du besoin.xlsm
    20.4 KB · Affichages: 17

Poulet

XLDnaute Nouveau
Bonjour Poulet, le fil,

ton fichier en retour ; sur "Feuil1", fais Ctrl e ➯ travail effectué ! 😊

sur "Feuil2", regarde C4 ! 😁 eh oui, hein ? tu avais fait une erreur !

tu avais mis 45 au lieu de 935 ! 45 - 890 aurait donné : -845 ;
alors que 935 - 890 = 45 ; c'est quand même plus juste ! 😜



code VBA de Module1 :

VB:
Option Explicit

Dim T, QT%, n2&, i&

Private Sub Job()
  QT = QT - T(i, 2): T(i, 3) = QT: T(i, 4) = 1: n2 = n2 + 1
End Sub

Sub Essai()
  If ActiveSheet.Name <> "Feuil1" Then Exit Sub
  Dim n1&: n1 = Cells(Rows.Count, 3).End(3).Row: If n1 = 5 Then Exit Sub
  Dim cel As Range, ref$, j&: Application.ScreenUpdating = 0
  Columns("E:F").ClearContents: [E5] = "Stock"
  n1 = n1 - 5: T = [C6].Resize(n1, 4): n2 = 0
  Do
    'recherche d'une 1ère référence
    ref = "": i = 1
    Do
      If ref = "" And T(i, 4) = 0 Then
        ref = T(i, 1): QT = 0
        Set cel = Worksheets("Feuil2").Columns(2).Find(ref, , -4163, 1, 1)
        If Not cel Is Nothing Then QT = cel.Offset(, 1)
        Call Job: j = i + 1: Exit Do
      Else
        i = i + 1
      End If
    Loop Until i > n1
    'traitement de toutes les lignes de la 1ère référence ci-dessus
    For i = j To n1
      If ref <> "" And T(i, 1) = ref And T(i, 4) = 0 Then Job
    Next i
  Loop Until n2 = n1
  [E6].Resize(n1) = Application.Index(T, Evaluate("Row(" & "1:" & n1 & ")"), 3)
End Sub

si besoin, tu peux demander une adaptation.
à te lire pour avoir ton avis. 😉

soan
 

Poulet

XLDnaute Nouveau
Bonjour Poulet, le fil,

ton fichier en retour ; sur "Feuil1", fais Ctrl e ➯ travail effectué ! 😊

sur "Feuil2", regarde C4 ! 😁 eh oui, hein ? tu avais fait une erreur !

tu avais mis 45 au lieu de 935 ! 45 - 890 aurait donné : -845 ;
alors que 935 - 890 = 45 ; c'est quand même plus juste ! 😜



code VBA de Module1 :

VB:
Option Explicit

Dim T, QT%, n2&, i&

Private Sub Job()
  QT = QT - T(i, 2): T(i, 3) = QT: T(i, 4) = 1: n2 = n2 + 1
End Sub

Sub Essai()
  If ActiveSheet.Name <> "Feuil1" Then Exit Sub
  Dim n1&: n1 = Cells(Rows.Count, 3).End(3).Row: If n1 = 5 Then Exit Sub
  Dim cel As Range, ref$, j&: Application.ScreenUpdating = 0
  Columns("E:F").ClearContents: [E5] = "Stock"
  n1 = n1 - 5: T = [C6].Resize(n1, 4): n2 = 0
  Do
    'recherche d'une 1ère référence
    ref = "": i = 1
    Do
      If ref = "" And T(i, 4) = 0 Then
        ref = T(i, 1): QT = 0
        Set cel = Worksheets("Feuil2").Columns(2).Find(ref, , -4163, 1, 1)
        If Not cel Is Nothing Then QT = cel.Offset(, 1)
        Call Job: j = i + 1: Exit Do
      Else
        i = i + 1
      End If
    Loop Until i > n1
    'traitement de toutes les lignes de la 1ère référence ci-dessus
    For i = j To n1
      If ref <> "" And T(i, 1) = ref And T(i, 4) = 0 Then Job
    Next i
  Loop Until n2 = n1
  [E6].Resize(n1) = Application.Index(T, Evaluate("Row(" & "1:" & n1 & ")"), 3)
End Sub

si besoin, tu peux demander une adaptation.
à te lire pour avoir ton avis. 😉

soan

Soan,

Il y a quelques jours maintenant, je t'avais sollicité pour ma problématique dont tu avais répondu à merveille.

Néanmoins, j'aurai besoin d'une adaptation de ton code (même fonctionnalité mais à appliquer sur un fichier différent).

Je souhaite donc que le stock total (colonne X, onglet Stock FRA0) de mon fichier se décrémente en fonction des besoins par numéro article correspondant dans l'onglet Besoins Bruts colonne Z.

==> Ci-joint mon fichier

2ème demande : Est-ce que tu peux détaillé ton code afin que je comprenne bien ce que tu fais car ce n'est pas forcément évidemment de rechercher par la suite sur internet pour décrypter les infos.

Au passage, un grand merci pour ton aide.

Cordialement.
 

Pièces jointes

  • Report.xlsx
    980.3 KB · Affichages: 7

soan

XLDnaute Barbatruc
Inactif
Bonjour Poulet,

désolé pour le très gros retard.



Option Explicit : oblige à déclarer les variables

Dim T, QT%, n2&, i& : déclaration de variables ; T est une variable sans type explicite, donc par défaut, elle est du type implicite Variant ; idem que Dim T As Variant ; Dim QT% : idem que Dim QT As Integer ; c'est pour Quantité Totale ; Dim n2&, i& : idem que Dim n2 As Long, i As Long



Private Sub Job() .. End Sub : sub privée Job() ; elle ne peut pas être appelée par l'utilisateur ; elle sera appelée uniquement par la sub Essai().

QT = QT - T(i, 2) : au départ, QT sera initialisée avec la Quantité du Stock Initial (en "Feuil2", colonne C) ; puis ici, on enlève au fur et à mesure des appels, et pour chaque REF, la quantité du Besoin (en "Feuil1", colonne D)

T(i, 3) = QT : T(i, 3) est la nouvelle Quantité, après avoir ôté la quantité du Besoin.

T(i, 4) = 1 : on met dans T(i, 4) la valeur 1 ; ça signifie qu'on s'est occupé de la ligne i ; donc on la sautera par la suite, car on s'occupera uniquement des lignes non encore traitées, celles pour lesquelles T(i, 4) est resté à 0.

n2 = n2 + 1 : n2 est augmenté de 1 ; car n2 est le nombre de lignes traitées ; or on vient tout juste de finir de traiter une ligne.​



dans mon prochain post, je détaillerai la sub principale Essai().

soan
 

soan

XLDnaute Barbatruc
Inactif
@Poulet

voici la dernière partie du commentaire de mon code VBA.​



Sub Essai() .. End Sub : c'est la sub principale Essai() ; c'est elle qui est appelée par l'utilisateur, et qui va appeler la sub Job() au moment adéquat.

If ActiveSheet.Name <> "Feuil1" Then Exit Sub : on doit lancer la sub Essai() à partir de "Feuil1", sinon on quitte la sub.

Dim n1& : idem que Dim n1 As Long

n1 = Cells(Rows.Count, 3).End(3).Row : idem que n1 = Cells(Rows.Count, "C").End(xlUp).Row ; n1 = de la dernière ligne utilisée, selon la colonne C ; ici : 14.

If n1 = 5 Then Exit Sub : si n1 = 5 (la ligne des 3 en-têtes "P/N" ; "Besoin" ; "Stock") : y'a aucune ligne à traiter ➯ on quitte la sub, sans avoir fait quoique ce soit.

Dim cel As Range, ref$, j& : idem que Dim cel As Range, ref As String, j As Long ; ref sera pour la référence d'un article.

Application.ScreenUpdating = 0 : on désactive la mise à jour de l'écran ➯ exécution plus rapide.

Columns("E:F").ClearContents : on efface les 2 colonnes E:F ; l'effacement de la colonne E permet d'éviter toute interférence entre les anciens résultats et les nouveaux résultats ; l'effacement de la colonne F permettra que tous les éléments de la dernière colonne du tableau T seront à 0 (c'est-à-dire qu'aucune ligne n'a encore été traitée).

[E5] = "Stock" : comme on a effacé toute la colonne E, du coup, on remet en E5 l'en-tête "Stock".

n1 = n1 - 5 : on enlève 5, car la 1ère ligne de données est la ligne 6 ; n1 = 14 - 5 = 9 ; ça tombe très bien, puisque des lignes 6 à 14, ça fait bien 9 lignes de données ; il faut donc traiter ces 9 lignes.

T = [C6].Resize(n1, 4) : la variable T devient ici un tableau à partir de C6, étendu à n1 lignes et 4 colonnes ; c'est donc la plage C6:F14 ; toutes les données de C6:E14 sont donc dans le tableau T ; et la dernière colonne de T est à 0 : aucune ligne n'a été traitée ➯ pour chaque ligne : 0 = non traité.

n2 = 0 : n2 est le nombre de lignes traitées ; donc au départ : n2 = 0 ; plus tard, on s'arrêtera quand n2 sera égal à n1, donc quand n2 sera égal à 9 : toutes les lignes auront été traitées.

Do .. Loop Until n2 = n1 : comme on vient de le voir, on va faire la boucle jusqu'à ce que n2 = n1, donc jusqu'à ce que n2 = 9 : on aura traité les 9 lignes de données.

'recherche d'une 1ère référence : comme l'indique ce commentaire, on va rechercher une 1ère référence (la référence d'un article, bien sûr).

ref = "": i = 1 : au départ, ref est une chaîne de caractères vide, et i = 1 (car : à partir de la 1ère ligne de T ➯ idem qu'à partir de la 1ère ligne de données).

Do .. Loop Until i > n1 : 2ème boucle Do .. Loop ; on en sortira plus haut si une référence a été trouvée ; ou ici si une référence n'a pas été trouvée et que i est supérieur à n1 : on a "balayé" toutes les lignes de données.​



VB:
If ref = "" And T(i, 4) = 0 Then
  ref = T(i, 1): QT = 0
  Set cel = Worksheets("Feuil2").Columns(2).Find(ref, , -4163, 1, 1)
  If Not cel Is Nothing Then QT = cel.Offset(, 1)
  Call Job: j = i + 1: Exit Do
Else
  i = i + 1
End If

si on n'a pas encore trouvé de référence, et que la ligne i en cours n'a pas encore été traitée, alors c'est cette ligne i qui va servir pour être la 1ère ligne d'une référence d'article à traiter ; et dans ce cas, on fait tout ce qui est entre If et Else ; sinon, cas du Else : on augmente i de 1 pour passer à la ligne suivante.

voyons maintenant tout c'qu'il y a entre If et Else :

ref = T(i, 1) : la référence d'article est T(i, 1) : élément de la ligne i et de la colonne 1 du tableau T ; c'est donc une des références de la colonne C de "Feuil1".

QT = 0 : pour la nouvelle référence d'article trouvée, QT est au départ mis à 0 ; grâce à cela, ça fait que si on ne trouvera pas la référence d'article en colonne B de "Feuil2", la quantité initiale par défaut est déjà correcte : QT = 0 ➯ y'a pas de stock initial pour la référence d'article en cours, puisque cette référence n'existe pas en "Feuil2" !​

Set cel = Worksheets("Feuil2").Columns(2).Find(ref, , -4163, 1, 1) : idem que

Set cel = Worksheets("Feuil2").Columns("B").Find(ref, , xlValues, xlWhole, xlByRows)

on cherche donc la référence d'article ref en colonne B de "Feuil2" ; si c'est non trouvé, cel contient Nothing (= rien) ; si c'est trouvé, cel contient la référence de la cellule de la référence trouvée.

If Not cel Is Nothing Then QT = cel.Offset(, 1) : si on a trouvé la référence d'article ref, alors QT = la Quantité initiale du stock, qui est notée 1 colonne à droite de la référence trouvée, donc en colonne C de "Feuil2" ; remarque : rien n'empêche que cette Quantité initiale soit 0, mais normalement, ça ne devrait pas être le cas, car sinon, c'est plus simple de ne pas mettre de ligne en "Feuil2" pour la référence d'article concernée : la référence ne sera pas trouvé, et la Quantité initiale sera à 0, du seul fait qu'on a mis QT = 0 avant même de faire la recherche de la référence. (c'est simple, hein ? 😁 😜)

Call Job : quelle que soit la valeur de QT (qui a été déterminée ci-dessus), on appelle la sub Job(), qui notera notamment que cette ligne a été traitée.

j = i + 1 : j est donc le de ligne qui suit la ligne de la 1ère référence trouvée ; c'est à partir de cette ligne j que plus bas on va lire toutes les lignes qui sont en dessous, mais on ne prendra en compte que les lignes de la référence d'article en cours, qui n'ont pas déjà été traitées.

Exit Do : dès qu'on a fait tout le travail nécessaire décrit précédemment pour une référence d'article trouvée, on sort de la boucle Do .. Loop.

'traitement de toutes les lignes de la 1ère référence ci-dessus : dixit le commentaire. 😁 😜

For i = j To n1 .. Next i : on balaye donc toutes les lignes de j à n1, via la variable i.

If ref <> "" And T(i, 1) = ref And T(i, 4) = 0 Then Job : SI on avait trouvé une 1ère référence d'article, ET que T(i, 1) est cette référence, ET que la ligne i en cours n'a pas déjà été traitée, ALORS on appelle la sub Job(), qui notera notamment que cette ligne a été traitée.

[E6].Resize(n1) = Application.Index(T, Evaluate("Row(" & "1:" & n1 & ")"), 3) : cette instruction est la dernière ligne de la sub, juste après la 1ère boucle Do .. Loop ; elle sert à écrire verticalement, à partir de E6, les valeurs de la 3ème colonne du tableau T, qui sont tout simplement les résultats attendus : la Quantité du Stock final pour chaque référence d'article.​



c'est un de mes code VBA les plus durs que j'ai eu à commenter ! alors lis bien le tout très attentivement, en prenant bien ton temps, pour ne pas risquer de manquer un détail important !

à te lire pour avoir ton avis, si toutefois tu reviens sur ce sujet ; sinon tant pis ! j'espère quand même que je n'aurais pas fait tous ces commentaires pour rien, et qu'ils pourront servir à d'autres lecteurs de cette conversation, qui ont peu ou prou le même problème que le tien. 🙂

soan
 

soan

XLDnaute Barbatruc
Inactif
Bonjour Poulet,

en retour, ton fichier "Report" ; j'ai dû le zipper pour pouvoir le joindre.

sur la feuille "Besoin Brut", fais Ctrl e ; attends un moment (durée selon la vitesse de ton PC), puis les résultats s'affichent en colonne Z "Stock décrémenté" ; à toi de vérifier tous les résultats. 😜

VB:
Option Explicit

Dim T1, T2, QT#, n2&, i&

Private Sub Job()
  QT = QT - T1(i, 3): T2(i, 1) = QT: T2(i, 2) = 1: n2 = n2 + 1
End Sub

Sub Essai()
  If ActiveSheet.Name <> "Besoin Brut" Then Exit Sub
  Dim n1&: n1 = Cells(Rows.Count, 4).End(3).Row: If n1 = 1 Then Exit Sub
  Dim cel As Range, ref$, j&: Application.ScreenUpdating = 0
  Columns("Z:AA").ClearContents: [Z1] = "Stock décrémenté"
  n1 = n1 - 1: T1 = [D2].Resize(n1, 3): T2 = [Z2].Resize(n1, 2): n2 = 0
  Do
    'recherche d'une 1ère référence
    ref = "": i = 1
    Do
      If ref = "" And T2(i, 2) = 0 Then
        ref = T1(i, 1): QT = 0
        Set cel = Worksheets("Stock FRA0").Columns(1).Find(ref, , -4163, 1, 1)
        If Not cel Is Nothing Then QT = cel.Offset(, 23)
        Call Job: j = i + 1: Exit Do
      Else
        i = i + 1
      End If
    Loop Until i > n1
    'traitement de toutes les lignes de la 1ère référence ci-dessus
    For i = j To n1
      If ref <> "" And T1(i, 1) = ref And T2(i, 2) = 0 Then Job
    Next i
  Loop Until n2 = n1
  [Z2].Resize(n1) = Application.Index(T2, Evaluate("Row(" & "1:" & n1 & ")"), 1)
End Sub

soan
 

Pièces jointes

  • Report.zip
    894.8 KB · Affichages: 3

Victor21

XLDnaute Barbatruc
Bonjour à tous.

Peut-être n'est-il pas utile de sortir la grosse artillerie ?
Code:
=SIERREUR(INDEX(Feuil2!$C$3:$C$9;EQUIV(C6;Feuil2!$B$3:$B$9;0));0)-SOMME.SI(C$6:C6;C6;D$6:D6)
 

Pièces jointes

  • Copie de Decrementation stock.xlsm
    11.8 KB · Affichages: 5

Victor21

XLDnaute Barbatruc
Bonjour, soan.
ah oui, mais moi, j'avais fait mon code VBA tout exprès pour pouvoir le commenter ensuite ! 🤪 😂 🤣

tu gâches le plaisir, si j'peux plus commenter mes codes VBA ! 😭 😭 😭 😜
C'eût été dommage, car tes explications sont un vrai plaisir à lire, à la fois claires et colorées. Tu es le synesthète du nombre et de la couleur, l'Arthur Rimbaud du VBA :
VB:
A noir, E blanc, I rouge, U vert, O bleu, voyelles,
 Je dirai quelque jour vos naissances latentes.
 A, noir corset velu des mouches éclatantes
 Qui bombillent autour des puanteurs cruelles,

Golfe d’ombre ; E, candeur des vapeurs et des tentes,
 Lance des glaciers fiers, rois blancs, frissons d’ombelles
 I, pourpres, sang craché, rire des lèvres belles
 Dans la colère ou les ivresses pénitentes ;

U, cycles, vibrements divins des mers virides,
 Paix des pâtis semés d’animaux, paix des rides
 Que l’alchimie imprime aux grands fronts studieux ;

O, suprême Clairon plein des strideurs étranges,
 Silences traversés des Mondes et des Anges :
 — O l’Oméga, rayon violet de Ses Yeux !
;)
;)
 

Discussions similaires

Statistiques des forums

Discussions
312 922
Messages
2 093 644
Membres
105 775
dernier inscrit
assen