Voir le flux RSS

User

Créer une gestion de stock sous Excel

Noter ce billet
par , 24/06/2016 à 22h56 (2973 Affichages)
Objectif

Mettre à jour, avec une macro, les quantités en stock des produits, en fonction des mouvements d'entrée/sortie qui ont lieu au cours du temps.

I) On dispose pour cela de 3 feuilles pour enregistrer les données dans notre classeur :

  • Produits
  • Inventaire
  • Mouvements



1) La feuille Produits

Pour enregistrer les produits et visualiser leur quantité en stock.

Nom : Produits.jpg
Affichages : 11006
Taille : 133,4 Ko


2) La feuille Inventaire

Pour enregistrer les quantités de produits à une date donnée.

Nom : Inventaire.jpg
Affichages : 1622
Taille : 131,4 Ko


3) La feuille Mouvements

Pour enregistrer les quantités de produits entrées et sorties à une date précise.

Nom : Mouvements.jpg
Affichages : 4390
Taille : 125,7 Ko


II) Le classeur prend en charge les macros et comporte en plus un module VBA pour actualiser les quantités :


Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
 
Option Explicit
 
'***************************************************************
'***********   Renvoie la qté du dernier inventaire  ***********
'***************************************************************
Public Function QteDernierInventaire(RefProduit As String, dtMouv As Date) As Long
Dim i As Long, dt As Date, qt As Long
Dim c As Range, firstAddress As Variant
 
   If RefProduit = "" Or IsNull(dtMouv) Then
      QteDernierInventaire = 0
      Exit Function
   End If
 
With Inventaire
 
Set c = .Columns(2).Find(What:=RefProduit, After:=.Columns(2).Cells(.Columns(2).Cells.Count), LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
 
    If Not c Is Nothing Then
 
       firstAddress = c.Address
 
       Do
 
        i = c.Row
 
        If (CDate(.Cells(i, 1)) <= dtMouv) And (CDate(.Cells(i, 1)) > dt) Then
           dt = CDate(.Cells(i, 1))
           qt = .Cells(i, 3)
        End If
 
        Set c = .Columns(2).FindNext(c)
 
              If c Is Nothing Then Exit Do
 
       Loop While c.Address <> firstAddress
 
    End If
 
End With
 
QteDernierInventaire = qt
 
End Function
 
'****************************************************************
'***********   Renvoie la date du dernier inventaire  ***********
'****************************************************************
Public Function DtDernierInventaire(RefProduit As String, dtMouv As Date) As Long
Dim i As Long, dt As Date, qt As Long
Dim c As Range, firstAddress As Variant
 
   If RefProduit = "" Or IsNull(dtMouv) Then
      DtDernierInventaire = 0
      Exit Function
   End If
 
With Inventaire
 
Set c = .Columns(2).Find(What:=RefProduit, After:=.Columns(2).Cells(.Columns(2).Cells.Count), LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
 
    If Not c Is Nothing Then
 
       firstAddress = c.Address
 
       Do
 
        i = c.Row
 
        If (CDate(.Cells(i, 1)) <= dtMouv) And (CDate(.Cells(i, 1)) > dt) Then
           dt = CDate(.Cells(i, 1))
           qt = .Cells(i, 3)
        End If
 
        Set c = .Columns(2).FindNext(c)
 
              If c Is Nothing Then Exit Do
 
       Loop While c.Address <> firstAddress
 
    End If
 
End With
 
DtDernierInventaire = dt
 
End Function
 
'************************************************************************************
'***********  Renvoie la qté en stock pour 1 mouvement à une date précise ***********
'************************************************************************************
Public Function QteMouv(RefProduit As String, dtMouv As Date) As Long
Dim i As Long, dt As Date, qt As Long
Dim c As Range, firstAddress As Variant
 
   If RefProduit = "" Or IsNull(dtMouv) Then
      QteMouv = 0
      Exit Function
   End If
 
dt = DtDernierInventaire(RefProduit, dtMouv)
qt = 0
 
With Mouvements
 
Set c = .Columns(2).Find(What:=RefProduit, After:=.Columns(2).Cells(.Columns(2).Cells.Count), LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
 
    If Not c Is Nothing Then
 
       firstAddress = c.Address
 
       Do
 
        i = c.Row
 
        If (CDate(.Cells(i, 1)) >= dt) And (dtMouv >= CDate(.Cells(i, 1))) Then
           If (.Cells(i, 3).Value <> "") Then
              qt = qt + CLng(.Cells(i, 3))
           ElseIf (.Cells(i, 4).Value <> "") Then
              qt = qt - CLng(.Cells(i, 4))
           End If
        End If
 
        Set c = .Columns(2).FindNext(c)
 
              If c Is Nothing Then Exit Do
 
       Loop While c.Address <> firstAddress
 
    End If
 
End With
 
QteMouv = qt
 
End Function
 
'*************************************************************************************
'***********  Actualise les quantités initiales et en cours par mouvement  ***********
'*************************************************************************************
Public Sub ActualiserQtesMouvs()
Dim i As Long, RefProduit As String, dt As Date
Dim qteInv As Long, qteStock As Long
 
i = 2
 
With Mouvements
 
Do While .Cells(i, 2).Value <> ""
 
   If .Cells(i, 1).Value <> "" Then
 
      RefProduit = .Cells(i, 2).Value
      dt = CDate(.Cells(i, 1).Value)
 
      qteInv = QteDernierInventaire(RefProduit, dt)
      qteStock = QteMouv(RefProduit, dt)
 
      .Cells(i, 5).Value = qteInv
      .Cells(i, 6).Value = qteInv + qteStock
 
   End If
 
i = i + 1
Loop
 
End With
 
End Sub
 
'**********************************************************************
'***********  Actualise les quantités en stock par produit  ***********
'**********************************************************************
Public Sub ActualiserQtesProduits()
Dim i As Long, RefProduit As String
Dim qteInv As Long, qteStock As Long
 
i = 2
 
With Produits
 
Do While .Cells(i, 1).Value <> ""
 
    RefProduit = .Cells(i, 1).Value
 
    qteInv = QteDernierInventaire(RefProduit, Date)
    qteStock = QteMouv(RefProduit, Date)
 
    .Cells(i, 3).Value = qteInv + qteStock
 
i = i + 1
Loop
 
End With
 
End Sub


III) Le fichier comprend également un module pour actualiser les données depuis le ruban :


Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Option Explicit
 
Public Const REF_WBK = "Analyse v1"
Public Const REF_WBK_EXT = "*.XLSM"
Public Const CTR_Version = "10 novembre 2014"
 
'***************************************************************************************
'***********  Actualise les quantités en stock par produit à partir du ruban ***********
'***************************************************************************************
Sub ActualiserQuantites_OnAction(Control As IRibbonControl)
 
Select Case Control.ID
 
Case "ActualiserQuantites"
ActualiserQtesProduits
ActualiserQtesMouvs
 
End Select
 
End Sub

IV) Ci-joint le fichier Excel :
Miniatures attachées Fichiers attachés

Envoyer le billet « Créer une gestion de stock sous Excel » dans le blog Viadeo Envoyer le billet « Créer une gestion de stock sous Excel » dans le blog Twitter Envoyer le billet « Créer une gestion de stock sous Excel » dans le blog Google Envoyer le billet « Créer une gestion de stock sous Excel » dans le blog Facebook Envoyer le billet « Créer une gestion de stock sous Excel » dans le blog Digg Envoyer le billet « Créer une gestion de stock sous Excel » dans le blog Delicious Envoyer le billet « Créer une gestion de stock sous Excel » dans le blog MySpace Envoyer le billet « Créer une gestion de stock sous Excel » dans le blog Yahoo

Mis à jour 30/06/2016 à 18h03 par ClaudeLELOUP (Coloration syntaxique CODE=VBA)

Catégories
Sans catégorie

Commentaires