Vous utilisez un navigateur obsolète. Il se peut que ce site ou d'autres sites Web ne s'affichent pas correctement. Vous devez le mettre à jour ou utiliser un navigateur alternatif.
XL 2013Décrémentation stock en fonction de la référence et du besoin
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
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
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.
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.
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.
Dim T, QT%, n2&, i& : déclaration de variables ; T est une variable sanstypeexplicite, donc par défaut, elle est du typeimpliciteVariant ; 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éeJob() ; 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().
voici la dernière partie du commentaire de mon code VBA.
Sub Essai() .. End Sub : c'est la sub principaleEssai() ; 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 = n° 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éeet 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 lignei 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é initialepar 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'articleref 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érencetrouvée.
If Not cel Is Nothing Then QT = cel.Offset(, 1) : si on a trouvé la référence d'articleref, alors QT = la Quantité initiale du stock, qui est notée 1 colonne à droite de la référencetrouvé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 pastrouvé, et la Quantité initiale sera à 0, du seul fait qu'on a mis QT = 0avant 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 n° de ligne qui suit la ligne de la 1èreréférencetrouvé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 pasdé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'articletrouvé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èreréférence d'article, ET que T(i, 1)estcette référence, ET que la ligne i en cours n'a pasdé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ésultatsattendus : la Quantité du Stock final pour chaqueré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.
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
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 !
Ce site utilise des cookies pour personnaliser le contenu, adapter votre expérience et vous garder connecté si vous vous enregistrez.
En continuant à utiliser ce site, vous consentez à notre utilisation de cookies.
.
eh oui, hein ? tu avais fait une 
