VBA - Boucle 2 feuilles (15 millions de passages)

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

Bonjour

Ce serait bien si on pouvait travailler sur le fichier...

Bye !

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+

Merci Curulis,

Pas sur que je puisse faire cela avant la fin de ton café

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

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+

Rechercher des sujets similaires à "vba boucle feuilles millions passages"