VBA - Boucle 2 feuilles (15 millions de passages)

Y compris Power BI, Power Query et toute autre question en lien avec Excel
A
Alex2
Membre habitué
Membre habitué
Messages : 93
Inscrit le : 28 avril 2012
Version d'Excel : 2010

Message par Alex2 » 28 février 2016, 10:42

Bonjour,

je vous explique mon soucis, mon fichier contient deux feuilles

La première contient des references de produits (540 lignes)
La seconde contient les mouvements de stocks (26148 lignes)

Pour chaque ligne de la premiere feuille, je dois obtenir la somme des mouvements de la deuxieme feuille en tenant compte bien sur de la référence.

j'ai essayé avec des boucles For et If, cela me fait (avec un petit compteur) 14 200 000 passage sur la boucle et environ 3 minutes de traitement.

J'ai essayé avec des formules sommeprod, mais j'aimerai ne pas avoir de formules dans mes feuilles.
Auriez-vous une solution pour accélérer ce traitement

Merci de votre aide

Option Explicit

Voici la fonction
Function f_QtéMvtSem(Ws As String, P_SemCBN As Long)
Dim Ws_CBNProd As Worksheet, Ws_RqMvt As Worksheet
Set Ws_CBNProd = Sheets("CBNProd")
Set Ws_RqMvt = Sheets("RqMvt")
Dim NbLigneCBN
Dim NbLigneMvt
Dim TabloQtéMvt
Dim Temps As Date
NbLigneCBN = f_NbLigne("CBNProd", "C")
NbLigneMvt = f_NbLigne("RqMvt", "A")
Dim CompteurCBN
Dim CompteurMvt
UserForm1.Show 0
Application.StatusBar = True
ReDim TabloQtéMvt(1 To NbLigneCBN - 4, 1 To 1)
Dim Depart As Date
Depart = Time
For CompteurCBN = 5 To NbLigneCBN
Dim SemCBN
Dim RefCBN
Dim CptCalc
SemCBN = Ws_CBNProd.Cells(4, P_SemCBN).Value
RefCBN = Ws_CBNProd.Cells(CompteurCBN, 3).Value
Dim RefMvt
Dim SemMvt
Dim SommeQtéMvt
For CompteurMvt = 2 To NbLigneMvt
Temps = Time
RefMvt = Ws_RqMvt.Cells(CompteurMvt, 3).Value
SemMvt = Ws_RqMvt.Cells(CompteurMvt, 22).Value
If RefMvt = RefCBN And SemMvt = SemCBN Then
SommeQtéMvt = SommeQtéMvt + Ws_RqMvt.Cells(CompteurMvt, 7).Value
Else
End If
CptCalc = CptCalc + 1
Next
TabloQtéMvt(CompteurCBN - 4, 1) = SommeQtéMvt
'Ws_CBNProd.Cells(CompteurCBN, P_SemCBN).Value = SommeQtéMvt
Application.StatusBar = "Progression : " & Round((CptCalc / (NbLigneMvt * NbLigneCBN)) * 100, 2) & "%"
UserForm1.ProgressBar1 = (CptCalc / (NbLigneMvt * NbLigneCBN)) * 100
UserForm1.TextBox1 = Round((CptCalc / (NbLigneMvt * NbLigneCBN)) * 100, 0) & "%"
UserForm1.TextBox2 = Format(CptCalc, "### ### ###") & " Calculs"
UserForm1.TextBox3 = SemCBN
UserForm1.TextBox4 = Format(Temps - Depart, "hh:mm:ss")
DoEvents

SommeQtéMvt = 0
Next
Ws_CBNProd.Cells(5, P_SemCBN).Resize(CompteurCBN - 4, 1) = TabloQtéMvt
Application.StatusBar = False
Unload UserForm1
End Function
g
gmb
Fanatique d'Excel
Fanatique d'Excel
Messages : 12'519
Appréciations reçues : 317
Inscrit le : 4 avril 2013
Version d'Excel : 2016

Message par gmb » 28 février 2016, 10:54

Bonjour

Ce serait bien si on pouvait travailler sur le fichier...
Bye !
Avatar du membre
curulis57
Passionné d'Excel
Passionné d'Excel
Messages : 3'528
Appréciations reçues : 202
Inscrit le : 4 janvier 2016
Version d'Excel : 2016 FR / 2019 FR

Message par curulis57 » 28 février 2016, 11:21

Salut Alex,

c'est le genre de truc que j'adore mais pas vraiment le temps tout de suite!
Cependant, il me semble que je ferais ainsi...

1. Trie ta ta feuille 1 sur les références, si ce n'est déjà fait, ou copie et trie-la-la dans une autre feuille si tu ne peux pas la changer...
2. Copie ta feuille de Mvts dans la feuille 3 (ou 4) et trie-la sur les références également...

Les nouvelles Feuilles étant triées sur les références, ta boucle ne passera qu'une fois sur chaque ligne de chaque feuille.
Tu gagneras encore un peu de temps en éliminant les affichages de contrôle d'avancement du travail, inutiles pour 3 minutes!
Il te restera à effacer les Feuilles excédentaires après traitement.

Je suis sûr qu'un as t'aura fait ça avant que j'ai fini mon café!

Avec plaisir!
A+
A
Alex2
Membre habitué
Membre habitué
Messages : 93
Inscrit le : 28 avril 2012
Version d'Excel : 2010

Message par Alex2 » 28 février 2016, 11:38

Merci Curulis,

Pas sur que je puisse faire cela avant la fin de ton café :D
Avatar du membre
Banzai64
Fanatique d'Excel
Fanatique d'Excel
Messages : 16'726
Appréciations reçues : 4
Inscrit le : 21 novembre 2010
Version d'Excel : 2003 FR (learning 2010 - 2013)

Message par Banzai64 » 28 février 2016, 11:58

Bonjour
Alex2 a écrit :je dois obtenir la somme des mouvements de la deuxieme feuille en tenant compte bien sur de la référence.
Avec un SOMME.SI() ou en VBA SUMIF ?

C'est sur qu'avec un fichier cela serait mieux
Image
Avatar du membre
curulis57
Passionné d'Excel
Passionné d'Excel
Messages : 3'528
Appréciations reçues : 202
Inscrit le : 4 janvier 2016
Version d'Excel : 2016 FR / 2019 FR

Message par curulis57 » 28 février 2016, 21:04

Salut Alex,

voilà, j'avais quelques minutes...
Comme je te disais, tu devrais (tout au long de l'année) prévoir une feuille où tu tries dans deux colonnes les références de tes produits et les références issues des mouvements.
Ça ne prendrait pas longtemps et tes données seraient déjà prêtes à être traitées au moment voulu.

Ce que j'ai simulé ici dans le fichier joint.
- 1 bouton pour créer les références de base : -> 1000 ;
- 1 bouton pour créer les mouvements (RND), ce qui crée +- 500.000 mouvements ;
- 1 bouton pour le traitement qui affiche le résultat en E... en quelques secondes!

A utiliser l'un après l'autre, évidemment!

Bien à toi.
A+
MvtsRéférences.xlsm
(27.28 Kio) Téléchargé 4 fois
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message