Décrémentation stock sans userform

Le 04/02/2021 à 10:10

Bonjour à tous,

Je souhaite décrémenter des quantités de stock via une macro,

J'ai une feuille Export et une feuille Stock

Ma macro doit vérifier si les pièces de l'export sont présentes en stock et si la quantité est disponible,

Si oui, décrémenter les quantités et incrémenter l'historique des sorties,

Mon code (effectué en fouillant les sujet "stock" du forum) ne fonctionne pas correctement, il ne trouve pas certaines lignes alors qu'elles sont présentes en stock,

Et je ne trouve pas comment incrémenter l'historique des sorties

Code pour décrémenter stock :

Sub Sortir()

With Sheets("Stock")

For Each I In Sheets("Export").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

Set Article = .Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Find(I.Value, lookat:=xlWhole)

If Not Article Is Nothing Then
Lgn = Article.Row

.Cells(Lgn, "G").Value = .Cells(Lgn, "G").Value - I.Offset(0, 4).Value

Else: MsgBox " La pièce " & I.Value & "" & I.Offset(0, 2).Value & " n'existe pas en stock", 16
End If
Next I

End With

MsgBox "Sorties enregistrées."

End sub
15bdd-stock.xlsm (44.17 Ko)
Le 05/02/2021 à 10:11

Bonjour,

Je n'ai pas regardé votre fichier mais voici un code pour l'exemple avec un tableau structuré nommé "Stock" et la colonne d'articles libellée "Articles" et la colonne du stock restant libellée "Qte".

Ici, pas de boucle sur un ensemble d'articles pour l'instant car je n'en vois pas l'intérêt. Normalement, il y a un évènement générateur de la mise à jour de l'onglet Export (il faudra également un tableau structuré nommé "Export") et de l'onglet "Stock". Les mises à jour doivent être simultanées et provenir d'un espace de saisie qui reste à déterminer (alors la boucle sur les seuls produits exportés sera envisageable).

Sub Sortir()

Dim article$

article = ???
QteSortie = ???

if application.countif(range("Stock[Articles]"), article) = 0 then
    MsgBox " La pièce " & article & " n'existe pas en stock", 16
else
    Lgn = application.match(article, range("Stock[Articles]"), 0)
    range("Stock[Qte]")(Lgn).Value = range("Stock[Qte]")(Lgn).Value - QteSortie
end if

MsgBox "Sorties enregistrées."

End sub

Cdlt,

Le 05/02/2021 à 11:30

Bonjour, et merci pour votre retour,

Je ne parvient pas a faire fonctionner votre code, j'ai bien nommé mes tableaux et colonnes comme vous me l'avez indiqué,

L'autre chose c'est que le nombre de lignes de l'export est amener a changé, d'où le comptage jusqu'à dernière ligne.

Question : Que-ce qui est attendu dans "article = ??? QteSortie = ???" une plage de donnée ? et que signifie le $ après une déclaration de variable ?

De plus votre langage est difficilement compréhensible pour un novice comme moi.. (un évènement générateur de la mise à jour de l'onglet Export?? Les mises à jour doivent être simultanées et provenir d'un espace de saisie qui reste à déterminer (alors la boucle sur les seuls produits exportés sera envisageable).??)

J'ai essayé le code code comme ca :

Sub Sortirtest()Dim article, Qtesortiearticle = Sheets("Export").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)Qtesortie = Sheets("Export").Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)If Application.CountIf(Range("Stock[Articles]"), article) = 0 Then  MsgBox " La pièce " & article & " n'existe pas en stock", 16Else Lgn = Application.Match(article, Range("Stock[Articles]"), 0) Range("Stock[Qte]")(Lgn).Value = Range("Stock[Qte]")(Lgn).Value - QtesortieEnd IfMsgBox "Sorties enregistrées."End Sub

Le 05/02/2021 à 11:34

Désolé, impossible d'insérer un code inline mis en forme correctement ...

Le 05/02/2021 à 11:51

Dans article, il est attendu le nom de l'article à tester (string). Dans QteSorti, la quantité sortie (integer). Or, je ne sais pas d'où viennent ces infos.

Mais comme j'ai essayé d'expliquer, j'espère qu'elles proviennent d'un formulaire (feuille ou userform) de saisie et non d'une colonne d'une autre base. L'idée, c'est qu'on saisit une sortie sur, par exemple, une feuille servant de formulaire de saisie des sorties.

Une fois que nos saisies (qui contiennent l'article et la quantité en question) sont terminées, en général, on appuie sur un bouton. Cela va mettre à jour nos 2 bases export et stock.

Car d'après votre code, j'avais l'impression que vous parcouriez votre base "Export" pour faire une modif sur chaque article...

Mais je vais regarder votre fichier pour m'assurer que je ne suis pas à côté de la plaque...

le "$" siginifie "As String" (et % signifie as integer).

Pour le code, il faut utiliser l'icone avec les balises </>

Le 05/02/2021 à 12:32

Merci encore pour votre aide,

Pour être plus précis dans le process recherché :

Mon but final est d'avoir deux fichier séparer, mais j'essaye pour commencer, de coder dans un même fichier, d'une feuille à une autre.

A la fin j'aimerais avoir 1 fichier Bdd Stock qui sera enregistré dans un emplacement fixe et 1 fichier Export (qui est en fait un extraction Excel de l'ERP SAP correspondant aux pièces à sortir du stock) et qui sera différent à chaque fois (en nombre de ligne)

L'idée première est de décrémenter, via une macro, les quantités de la Bdd en fonction des quantités présentes dans l'export généré via SAP (directement sans aucune saisie de formulaire ou autre à effectuer).

Dans la même macro j'aimerais vérifier si la pièces a sortir est bien présente dans la bdd stock et si quantité est suffisante en stock

J'ai "bricoler" un code que j'ai trouver sur le forum, mais il vrai que je doute que ce soit la bonne direction a prendre :

Sub Sortir()

    With Sheets("Stock")

        For Each I In Sheets("Export").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        ' I = Valeur colonnes A feuille Export
                Set article = .Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Find(I.Value, lookat:=xlWhole)
                ' Article = valeur égale a I dans feuille Stock

                'If I = "" Then
                'MsgBox "sortie enregistré"
                'Exit Sub
                'End If

                If Not article Is Nothing Then
                Lgn = article.Row

                .Cells(Lgn, "G").Value = .Cells(Lgn, "G").Value - I.Offset(0, 4).Value
                '.Cells(Lgn, "B").Value = quantité en stock
                'I.Offset(0, 1).Value = quantité commandé

                Else: MsgBox "     La pièce " & I.Value & "" & I.Offset(0, 2).Value & " n'existe pas en stock", 16

                End If
        Next I

    End With

    MsgBox "Sorties enregistrées."

End Sub
Le 05/02/2021 à 12:46

Ahhh d'accord, il n'y a pas de saisie.

Oui, la macro est bien (mais à adapter forcément). J'essaie d'éviter d'utiliser la méthode find pour ma part mais le résultat est normalement le même.

Si le format d'export de SAP est toujours le même, il n'y a pas de raison de créer 2 fichiers. 2 onglets c'est très bien.

Voici un essai alors, avec un hypothétique tableau structuré "Export" et 2 colonnes "Articles" et "Qte" :

Sub Sortir()

Dim article$

for i = 1 to range("Export").rows.count
    article = range("Export[Articles]")(i)
    QteSortie = range("Export[Qte]")(i)
    if application.countif(range("Stock[Articles]"), article) > 0 then
        Lgn = application.match(article, range("Stock[Articles]"), 0)
        if range("Stock[Qte]")(Lgn) - QteSortie >= 0 then
            range("Stock[Qte]")(Lgn).Value = range("Stock[Qte]")(Lgn).Value - QteSortie
        else
            range("Export").rows(i).interior.color = 255
            range("Export")(i, range("Export").columns.count).offset(0, 2) = QteSortie & " non soustraite car supérieure au stock !"
        end if
    else
        range("Export").rows(i).interior.color = 255
        range("Export")(i, range("Export").columns.count).offset(0, 2) = article & " n'existe pas en stock !"
    end if
next i

MsgBox "Sorties enregistrées."

End sub

Cdlt,

Le 05/02/2021 à 14:26

Je ne sais pas vraiment ce que vous entendez par tableau "structuré" mais le but du process est de ne faire aucune saisie et donc de ne pas nommer les colonnes du tableau export à chaque fois.

Le format du fichier export sera toujours le même en terme de colonnes mais il sera bien un nouveau fichier a chaque nouvel export, l'intérêt d'avoir 2 fichier est d'éviter la manipulation de copié/collé la feuille export dans le fichier bdd stock pour chaque export.

Néanmoins, j'ai essayé votre code, je n'arrive pas a le faire fonctionner, j'ai fait l'essaie sur un fichier simplifier deux feuilles stock et export avec deux colonnes articles et quantité que j'ai nommé suivant vos indications mais j'ai une erreur d'exécution

13test-stock-version-1.xlsm (23.61 Ko)
Le 05/02/2021 à 17:05

D'accord, j'ai compris. C'est un export qui produit un fichier et pas des lignes exportées via le presse-papier...

Ok, bah c'est faisable mais ça implique de cherche le fichier l'ouvrir avant de faire cette opération (qu'il faudra juste adapter).

Le 05/02/2021 à 23:04

Bonsoir QGOUG,

Voici un code à essayer sur la base de ton fichier et de tes explications. Je ne l'ai pas testé car il nécessite un fichier nommé export dans le dossier téléchargements. On s'approche du but je pense. Je suis parti sur la même base (tableau structuré Stock et colonnes Articles et Qte) sur le fichier exécutant. Cependant, plus de tableau sur le fichier export. Il est fermé au début de la procédure. On teste son existence dans le dossier téléchargements. S'il existe on l'ouvre et on boucle sur chaque ligne (à partir de la ligne 2). Je considère qu'il y a les articles en colonne A et les quantités en colonne B (comme sur ton exemple).

Le principe du code reste le même...

Mais au cas où un article est manquant ou une quantité à sortir est excessive, on empêche la modification de la base et on surligne en rouge (avec un message à la fin de la procédure). Quand on ne rencontre aucun problème, le fichier est fermé et on a un message plus sympathique.

Voici le code :

Sub Sortir()

Dim alerte As Boolean

spath = Environ("USERPROFILE") & "\Downloads\" 'emplacement (tlchgts)
sfilename = Dir(spath & "*xport*") 'clé de recherche

If sfilename = "" Then 'si n'existe pas
    MsgBox "Fichier Introuvable"
    Exit Sub 'sortie
End If

Application.ScreenUpdating = True
Workbooks.Open spath & sfilename 'sinon, ouverture fichier
With ActiveWorkbook.ActiveSheet 'avec feuille active de fichier actif (ouvert à l'instant donc l'export)
    dl = .Cells(.Rows.Count, 1).End(xlUp).Row 'dern ligne
    For i = 2 To dl 'pour chaque ligne export
        article = .Cells(i, 1).Value 'article en A
        qte = .Cells(i, 2).Value 'qté en B
        If Application.CountIf(Range("Stock[Articles]"), article) > 0 Then 'si article existe dans tableau Stock (col Articles)
            Lgn = Application.Match(article, Range("Stock[Articles]"), 0) 'position ligne
            If Range("Stock[Qte]")(Lgn) - qte >= 0 Then 'si qte sortie inferieure au stock
                Range("Stock[Qte]")(Lgn).Value = Range("Stock[Qte]")(Lgn).Value - qte 'decrementation
            Else
                alerte = True 'sinon, prepare une alerte
                .Range("A:B").Rows(i).Interior.Color = 255 'colorie ligne en rouge
                .Cells(i, 3).Value = qte & " non soustraite car supérieure au stock !" 'commentaire en col C
            End If
        Else
            fermeture = False 'si existe pas, alerte
            .Range("A:B").Rows(i).Interior.Color = 255 'ligne rouge
            .Cells(i, 3).Value = article & " n'existe pas en stock !" 'comm en col C
        End If
    Next i
End With
Application.ScreenUpdating = True

If Not alerte Then 'si aucune alerte
    ActiveWorkbook.Close True 'fermeture et sauvegarde
    MsgBox "Sorties enregistrées." 'msg ok
Else 'sinon, si pb
    MsgBox "Anomalies rencontrées durant la procédure, surlignées en rouge !", vbCritical 'alerte et pas de fermeture
End If

End Sub

Je te renvoie le fichier avec le code dessus au cas où :

27test-stock-version-1.xlsm (20.16 Ko)

Rq : Sur ton fichier, j'ai vu des quantités négatives en stock. Si ce n'est pas pour l'exemple, il faudra surveiller car c'est anormal.

Cdlt,

Le 06/02/2021 à 09:43

Bonjour 3GB,

Merci pour ton aide, en effet j'ai l'impression qu'on s'approche du but

Cependant, j'ai tout essayé mais le code ne passe pas la ligne,

If Application.CountIf(Range("Stock[Articles]"), article) > 0 Then 'si article existe dans tableau Stock (col Articles)

Msg : La méthode Range de l'objet global a échoué

Je pense pourtant avoir bien structuré mon tableau Stock..

Le 06/02/2021 à 10:34

Salut QGOUG,

Oui, j'ai regardé le sujet ce matin et j'y ai pensé. En fait, comme le classeur export est le classeur actif, le tableau Stock n'est pas trouvé car il est défini sur le classeur de départ. Voici un nouvel essai :

Sub Sortir()

Dim alerte As Boolean

spath = Environ("USERPROFILE") & "\Downloads\" 'emplacement (tlchgts)
sfilename = Dir(spath & "*xport*") 'clé de recherche

If sfilename = "" Then 'si n'existe pas
    MsgBox "Fichier Introuvable"
    Exit Sub 'sortie
End If

set rART = Range("Stock[Articles]") 'si besoin, rajouter sheets("nomfeuille")
set rQTE = Range("Stock[Qte]") 'iden

Application.ScreenUpdating = True
Workbooks.Open spath & sfilename 'sinon, ouverture fichier
With ActiveWorkbook.ActiveSheet 'avec feuille active de fichier actif (ouvert à l'instant donc l'export)
    dl = .Cells(.Rows.Count, 1).End(xlUp).Row 'dern ligne
    For i = 2 To dl 'pour chaque ligne export
        article = .Cells(i, 1).Value 'article en A
        qte = .Cells(i, 2).Value 'qté en B
        If Application.CountIf(rART, article) > 0 Then 'si article existe dans tableau Stock (col Articles)
            Lgn = Application.Match(article, rART, 0) 'position ligne
            If rQTE(Lgn) - qte >= 0 Then 'si qte sortie inferieure au stock
                rQTE(Lgn).Value = rQTE(Lgn).Value - qte 'decrementation
            Else
                alerte = True 'sinon, prepare une alerte
                .Range("A:B").Rows(i).Interior.Color = 255 'colorie ligne en rouge
                .Cells(i, 3).Value = qte & " non soustraite car supérieure au stock !" 'commentaire en col C
            End If
        Else
            fermeture = False 'si existe pas, alerte
            .Range("A:B").Rows(i).Interior.Color = 255 'ligne rouge
            .Cells(i, 3).Value = article & " n'existe pas en stock !" 'comm en col C
        End If
    Next i
End With
Application.ScreenUpdating = True

If Not alerte Then 'si aucune alerte
    ActiveWorkbook.Close True 'fermeture et sauvegarde
    MsgBox "Sorties enregistrées." 'msg ok
Else 'sinon, si pb
    MsgBox "Anomalies rencontrées durant la procédure, surlignées en rouge !", vbCritical 'alerte et pas de fermeture
End If

End Sub

Il faudra peut-être préciser aussi la feuille sur laquelle se trouve le tableau Stock mais j'en doute.

Si tous les noms (tableaux, colonnes, etc...) sont bons, je pense que le code devrait passer :)

A bientôt,

Le 06/02/2021 à 11:27

3GB, merci beaucoup pour vos retours rapides !

En effet le problème était bien la, je venais de l'identifier :) et ca marche bien avec le nouveau code,

Par contre le process est un peu différent de ce que j'aimerais :

En réalité ca serait plutôt l'inverse, l'Export sera déjà ouvert et le fichier Stock fermé (enregistré dans un emplacement fixe). J'aimerais que le lancement des opérations (de la macro) se fasse directement depuis l'Export, que le message s'affiche à la fin sur l'Export et que le fichier Stock soit enregistré et fermé.

Je pense que celle-ci sera ma dernière sollicitation pour vous et je vous laisse tranquille :p

Le 06/02/2021 à 12:07

Honnêtement, je ne suis pas sûr que ce soit une solution envisageable...

Il est possible d'améliorer la présente solution mais si j'ai bien compris, tu réalises des exports périodiquement. Je vois mal comment ces fichiers éphémères pourraient contenir une macro sans perdre le bénéfice de l'automatisation...

Non, à mon avis, c'est bien le fichier qui contient le Stock qui doit contenir cette macro.

Le 06/02/2021 à 12:15

Oui ce sont bien des export periodique, je pensai qu'il était possible d'enregistré une macro dans la barre d'accès rapide qui permettrai d'exécuter les opérations dans les exports sachant qu'ils auront tous le même formats.

Mais en effet, je test depuis tout à l'heure et je perd ma macro a chaque fois ^^..

Le 06/02/2021 à 12:24

Il y a toujours moyen de trouver une solution mais je pense qu'elle pourrait être coûteuse et finalement inadaptée. Ton fichier c'est bien celui qui contient le stock. C'est ce fichier que tu mettras à jour, qui évoluera. C'est donc de ce fichier que tu dois partir.

Le 06/02/2021 à 12:28

Ok, je vais rester la dessus alors,

Merci pour le temps que tu m'as consacré !

Bon weekend !

Cordialement,

Quentin.

Le 06/02/2021 à 14:31

Merci, très bon week-end à toi aussi !

Rechercher des sujets similaires à "decrementation stock userform"