dico

Version imprimable

21/05/2021, 09h57
Osaka2017

1 pièce(s) jointe(s)

Copie de cellules sous condition avec tableau/array/dico

Bonjour à toutes et tous,

Dans le fichier joint, j'ai réalisé une macro permettant la copie de produits (avec description, unité et prix) de la feuille MVTS qui n'existent pas dans feuille ETAT_STOCK.

Actuellement, la macro s'exécute rapidement car il y a peu de lignes dans la feuille MVTS. Mais à court terme, celle-ci contiendra plusieurs milliers de lignes.

Il peut y avoir des doublons dans la feuille MVTS (ce qui est normal) mais pas dans la feuille ETAT_STOCK (car celle-ci une synthèse de tous les mouvements effectués…un peu à l’image d’un TCD).

Pour la rapidité, je pense qu'il serait préférable d'utiliser un tableau, array et/ou dictionnaire mais j'avoue avoir un peu de mal.

C'est la raison pour laquelle je sollicite votre aide.

Merci à vous
21/05/2021, 15h59
Pierre Fauconnier

Salut.

Si l'état du stock part toujours "à vide" et est rempli par les mvts à raison d'une ligne par article, pourquoi ne gardes-tu pas l'idée du TCD? Tu pourrais aussi partir sur du Power Query pour regrouper sur les articles à une date D.

Perso, si je devais le réaliser en VBA, j'utiliserais les références structurées et les listobject plutôt que du code classique...

Si tu souhaites remplir d'autres données (stock mini, ...), tu pourrais utiliser le TCD comme tremplin avec la fonction LIREDONNEESTABCROISEDYNAMIQUE, car la fonction est instantanée. Tu pourrais aussi passer par Power Pivot. Tu pourrais digérer des centaines de milliers de lignes très rapidement.
22/05/2021, 12h07
Osaka2017

Merci pour cette suggestion Pierre.

Dans la première version du fichier, j'avais utilisé un TCD sur la feuille MVTS pour connaitre le stock actuel, ce qui est effectivement bien plus pratique et rapide.

Mais l'alimentation de la feuille ENTREE se fait via un excellerator càd, une connexion externe à une base de données ERP. C'est n'est donc pas aussi simple qu'il n'y parait.

bonjour
je sais pas si ça t'aide à avancer

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sub test()
Dim f1 As Worksheet
 Dim f2 As Worksheet
 Dim TblBD
 Dim L As Long
Set f1 = Sheets("MVTS")
Set f2 = Sheets("ETAT_STOCK")
 L = f1.Range("A" & Rows.Count).End(xlUp).Row
 f2.Range("A6:I" & L).ClearContents
  Set d = CreateObject("scripting.dictionary")
  d.CompareMode = vbTextCompare
 
  derlig = f1.Range("A" & Rows.Count).End(xlUp).Row
   TblBD = f1.Range("A2:J" & derlig)
  For i = 1 To UBound(TblBD)
    clé = TblBD(i, 3) & "|" & TblBD(i, 4) & "|" & TblBD(i, 5)
    d(clé) = d(clé) + TblBD(i, 6)
  Next i
 f2.Range("A6").Resize(d.Count) = Application.Transpose(d.keys)
 f2.Range("D6").Resize(d.Count) = Application.Transpose(d.items)
 Application.DisplayAlerts = False
 f2.Range("A6").Resize(d.Count).TextToColumns Other:=1, OtherChar:="|"
 f2.Range("D6").Resize(d.Count).TextToColumns Other:=1, OtherChar:="|"
End Sub

Citation:

Envoyé par Osaka2017

[...]
Mais l'alimentation de la feuille ENTREE se fait via un excellerator càd, une connexion externe à une base de données ERP. C'est n'est donc pas aussi simple qu'il n'y parait.

Qu'entends-tu pas un "excellerator"? D'une manière générale, une chose est d'acquérir les données, une autre est de les préparer pour l'analyse et une autre encore de les analyser. Déjà, il n'y a pas de feuille "ENTREE" mais une feuille "MVTS". Je suppose que c'est la même. De plus, les données y sont en tableau structuré, je ne vois donc pas le problème lié à un "excellerator". De plus, si c'est un tableau des entrées, où est le tableau des sorties? Que faut-il valoriser dans le stock? En regardant ton classeur et en m'en tenant à ce que réalise ton code, je penses qu'un TCD est suffisant. Si le volume est vraiment énorme, on peut passer par Power Pivot, mais il faudrait vraiment que ça en vaille la peine. Un simple TCD est déjà capable de digérer vraiment beaucoup de lignes. Ca te permet de ne pas avoir de VBA pour ce traitement.

Donc:

ton excellerator s'occupe d'acquérir les données;
Power Query les prépare pour l'analyse en les organisant en tableaux structurés et éventuellement réaliser certaines analyses (regroupements, dénombrements ...). Si vraiment Power Query ne s'en sort pas, on utilise le VBA;
TCD, Power Pivot et graphiques en permettent l'analyse.

Le bouton MAJ remplit ETAT_STOCK, mais en reprenant seulement les produits et les prix unitaires. Les autres colonnes seront calculées? Par quelles formules? ETAT_STOCK doit-il être vidé avant d'y transférer les mouvements?

Après avoir créé le tableau structuré t_Stock sur la feuille Etat_Stock, tu peux utiliser le code suivant, qui copie en fait ce que tu ferais dans Excel (Quand on programme en VBA pour Excel, on pense d'abord Excel avant de penser VBA).

Pour m'en tenir stricto sensuà ce que fait ta procédure MAJ, voici comment je la coderais:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Sub MAJ() Dim r As Long Dim Columns(1) Application.ScreenUpdating = False r = Range("Tableau_MVTS").ListObject.ListRows.Count Columns(0) = Range("t_Stock").ListObject.ListColumns("Produit").Index Columns(1) = Range("t_Stock").ListObject.ListColumns("Unité").Index If Not Range("t_Stock").ListObject.DataBodyRange Is Nothing Then Range("t_Stock").ListObject.DataBodyRange.Delete Range("t_Stock[Produit]").Resize(r, 1).Value = Range("Tableau_MVTS[Produit]").Value Range("t_Stock[Description]").Resize(r, 1).Value = Range("Tableau_MVTS[Description]").Value Range("t_Stock[Prix Unit]").Resize(r, 1).Value = Range("Tableau_MVTS[Prix Unit]").Value Range("t_Stock").RemoveDuplicates Columns, Header:=xlYes Application.ScreenUpdating = True End Sub
Ce code est plus lent que celui de BENNASR avec le dictionnaire lorsqu'on les teste sur 1 000 000 lignes, mais je doute que tu aies autant de lignes à gérer. Il se rapproche plus de ce que l'on ferait en Excel (lorsque l'on programme en VBA pour Excel, et qu'on n'est pas informaticien, on pense Excel avant de penser VBA... et d'utiliser des bibliothèques externes telles que Scripting.Dictionary ;) ).

Attention que dans le code de BENNASR, 4 variables ne sont pas définies, ce qui rend le test impossible pour qui a Option Explicit en tête de module. Pour rappel, on devrait toujours avoir Option Explicit en tête de module. (Voir mon billet à ce sujet)

Citation:

Envoyé par BENNASR

bonjour
je sais pas si ça t'aide à avancer

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sub test()
Dim f1 As Worksheet
 Dim f2 As Worksheet
 Dim TblBD
 Dim L As Long
Set f1 = Sheets("MVTS")
Set f2 = Sheets("ETAT_STOCK")
 L = f1.Range("A" & Rows.Count).End(xlUp).Row
 f2.Range("A6:I" & L).ClearContents
  Set d = CreateObject("scripting.dictionary")
  d.CompareMode = vbTextCompare
 
  derlig = f1.Range("A" & Rows.Count).End(xlUp).Row
   TblBD = f1.Range("A2:J" & derlig)
  For i = 1 To UBound(TblBD)
    clé = TblBD(i, 3) & "|" & TblBD(i, 4) & "|" & TblBD(i, 5)
    d(clé) = d(clé) + TblBD(i, 6)
  Next i
 f2.Range("A6").Resize(d.Count) = Application.Transpose(d.keys)
 f2.Range("D6").Resize(d.Count) = Application.Transpose(d.items)
 Application.DisplayAlerts = False
 f2.Range("A6").Resize(d.Count).TextToColumns Other:=1, OtherChar:="|"
 f2.Range("D6").Resize(d.Count).TextToColumns Other:=1, OtherChar:="|"
End Sub

Merci BENNASR, votre code fonctionne parfaitement.