XL 2019 Boucle for dans boucle for

Jeremy1

XLDnaute Nouveau
Bonjour,
J'ai un soucis de RAM sur l'utilisation de ma macro.

La partie de code bloquant est une boucle for dans une boucle for avec un if.
Mon but est de savoir pour chaque valeur dans TabRef savoir si elle est présente dans une colonne du classeur ou pas.
Ma boucle for Z permet de défiler dans la colonne du classeur et la boucle for I de passer à la valeur suivante de TabRef.

La boucle finis par consommer 1.3go de RAM et plante.

TabRef est un tableau 1 dimension du même nombre d'element que la boucle for environ 3500.
La boucle For Z va de 0 à environ 3500 éléments (cela augmente rapidement au fil des mois)
La boucle For i va de 0 à environ 1000 élément maximum


Code:
For Z = 0 To (LastRw - FirstRw)
            For i = 0 To (LastRwRéf - FirstRwRéf)
                If TabRef(i) = Workbooks(tata).Sheets("toto").Cells(FirstRwRéfVar + Z, ColonneRef).Text Then 'Si valeur trouvée
                    TabDoublon(x, 0) = Workbooks(tata).Sheets("toto").Cells(FirstRwRéfVar + Z, ColonneArt)
                    TabDoublon(x, 1) = Workbooks(tata).Sheets("toto").Cells(FirstRwRéfVar + Z, ColonneRef)
                    TabDoublon(x, 2) = Workbooks(tata).Sheets("toto").Cells(FirstRwRéfVar + Z, ColonneDate)
                    TabDoublon(x, 3) = Workbooks(tata).Sheets("toto").Cells(FirstRwRéfVar + Z, ColonneQTY)
                    x = x + 1
                    Exit For
                ElseIf (i = (LastRwRéf - FirstRwRéf)) Then
                    TabImport(y, 0) = Workbooks(tata).Sheets("toto").Cells(FirstRwRéfVar + Z, ColonneArt)
                    TabImport(y, 1) = Workbooks(tata).Sheets("toto").Cells(FirstRwRéfVar + Z, ColonneRef)
                    TabImport(y, 2) = Workbooks(tata).Sheets("toto").Cells(FirstRwRéfVar + Z, ColonneDate)
                    TabImport(y, 3) = Workbooks(tata).Sheets("toto").Cells(FirstRwRéfVar + Z, ColonneQTY)
                    Workbooks(tata).Sheets("toto").Rows(Z + 2).Interior.Color = vbGreen
                    y = y + 1
                End If
            Next
        Next

Cordialement,
 
Dernière édition:

Jeremy1

XLDnaute Nouveau
J'ai fais ceci qui convertie en string les données que l'on va comparer par contre si j'ai compris le fonctionnement d'un dictionnaire sa ajoute les données les unes derrières les autres et la quelque chose cloche sur mon deuxième for...

VB:
        Dim TabRef(), TabImport(), TabDoublon(), TabPortofolio(), LD&, LI&, LP&, Dic As Dictionary
                
        Set Dic = New Dictionary
        
        TabRef = Sheets("Sheet1").Cells(FirstRwRéfVar, ColonneRef).Resize(LastRwRéf + 1 - FirstRwRéf, 1).Value
        For LD = 1 To UBound(TabRef, 1): Dic(CStr(TabRef(LD, 1))) = Empty: Next LD 'convertie en string la colonne "Col1" de "sheet1"
        
        TabPortofolio = Sheets("Sheet2").Cells(FirstRw2Var, 2).Resize(LastRw2 + 1 - FirstRw2, 1).Value 'charge les valeurs de "sheet2"
        For LD = 1 To UBound(TabPortofolio, 1): Dic(CStr(TabPortofolio(LD, 1))) = Empty: Next LD 'convertie en string la colonne "Col2" de "sheet2"
 

Jeremy1

XLDnaute Nouveau
Ça ne s'ajoute pas quand ça y figure déjà.
D'accord je comprend mieux, c'est une autre façon de voir mon problème alors.

Ce code fonctionne mais plante lorsqu'il y a un élément de la liste qui est vide ou qui n'est pas un nombre (For LD = 1 To UBound(TabRef, 1): Dic(CDbl(TabRef(LD, 1))) = Empty: Next LD). Comment faire pour supprimer ces lignes ou ne pas les integrer au dic ?

VB:
        Dim RngDonn As Range, TabDonnées() As Variant, LDonn As Long, Dic As New Dictionary, _
        TabDoublon(), LDoub As Long, TabImport(), LImpt As Long, C As Integer



'        '-----Charge tabdonnées avec le portefeuille de commande du jour---------------
        Set RngDonn = Sheets("Sheet2").Cells(FirstRw2Var, 1).Resize(LastRw2 + 1 - FirstRw2, 4)
        TabDonnées = RngDonn.Value
        ReDim TabDoublon(1 To UBound(TabDonnées, 1), 1 To 4), TabImport(1 To UBound(TabDonnées, 1), 1 To 4)
'        '---------------------------------------------------------------
       
        '-----Charge le dic avec toute les valeurs---------------
        TabRef = Sheets("Sheet1").Cells(FirstRwRéfVar, ColonneRef).Resize(LastRwRéf + 1 - FirstRwRéf, 1).Value
        For LD = 1 To UBound(TabRef, 1): Dic(CDbl(TabRef(LD, 1))) = Empty: Next LD 'convertie en string la colonne "Col1" de "sheet1". Supprime tous les doublons automatiquement
        LI = Dic.Count
        '---------------------------------------------------------------
       
       
        For LDonn = 1 To UBound(TabDonnées, 1)
            If Dic.Exists(TabDonnées(LDonn, ColonneRef + 1)) Then
               LDoub = LDoub + 1
               For C = 1 To 4: TabDoublon(LDoub, C) = TabDonnées(LDonn, Choose(C, Colonne1, Colonne2, Colonne3, Colonne4)): Next C
            Else
               Dic.Add TabDonnées(LDonn, ColonneRef + 1), Empty
               LImpt = LImpt + 1
                    For C = 1 To 4: TabImport(LImpt, C) = TabDonnées(LDonn, Choose(C, Colonne1, Colonne2, Colonne3, Colonne4)): Next C
               'Rng.Rows(LDonn + 1).Interior.Color = vbGreen
               End If
        Next LDonn

Merci pour votre aide et patience!
 

Dranreb

XLDnaute Barbatruc
Oh, je n'avais pas vu votre question plus tôt. Désolé.
Le plus simple c'est de faire en sorte d'avoir les même types de données des deux cotés et de prendre alors les valeurs telles qu'elles sont.
Donc enlevez ce format texte partout ou mettez le partout, puis ressaisissez les données où vous l'aurez changé. Si vous l'enlevez vous pouvez toujours mettre une apostrophe devant des textes qui sinon seraient interprétés comme nombres, genre "1E3" qui sans elle devient 1000.
 
Dernière édition:

Jeremy1

XLDnaute Nouveau
Bonjour,
Pour moi la methode tout convertir en texte poserait moins de probleme. Je cherche à convertir en texte les données que je place dans TabDonnées. Est ce possible de le faire en meme temps que le remplissage du tableau avec un Cstr?
VB:
'-----Charge tabdonnées avec le portefeuille de commande du jour---------------
        Set RngDonn = Sheets("Sheet2").Cells(FirstRw2Var, 1).Resize(LastRw2 + 1 - FirstRw2, 4)
        TabDonnées = CStr(RngDonn.Value)
        ReDim TabDoublon(1 To UBound(TabDonnées, 1), 1 To 4), TabImport(1 To UBound(TabDonnées, 1), 1 To 4)
'        '---------------------------------------------------------------
 

Dranreb

XLDnaute Barbatruc
Bonjour.
Pas comme ça en tout cas. Chargez d'abord votre TabDonnées = RngDonn.Value
et ensuite modifiez par des boucles.
Choisissez donc des noms beaucoup plus courts, bon sang :
VB:
TDon = RngDon.Value
For L = 1 To Ubound(TDon, 1)
    For C = 1 To Ubound(TDon, 2)
        TDon(L, C) = CStr(TDon(L, C))
        Next C, L
L'accès à un élément de tableau VBA est incommensurablement plus rapide que l'accès à une cellule.
 

Jeremy1

XLDnaute Nouveau
Merci pour votre réponse.
Je suis en train d'adapter le code pour l'integrer au reste.
Pour le code ci dessous est ce possible de choisir 4 colonnes spécifiques plutôt que tous le tableau?
Dans mon classeur en exemple il y a que 4 colonnes que je cherche à récuperer mais dans mon cas il y en a 17 et je n'en veux que 4 sans supprimer les autres.
VB:
Set RngDonn = Sheets("Sheet1").Cells(FR, 1).Resize(LR - FR + 1, 4)
Ou alors je charge tout le tableau si cela ne ralentit pas trop et je récupére que les cases voulu, mais cela me demande de modifier le code de récupération
 

Jeremy1

XLDnaute Nouveau
Voila j'ai intégré le code. Je passe de 15sec (j'avais tous mis dans des tableau dynamique mais pas utilisé dic) à 14sec d’exécution.
Mais la différence se fera peut être quand il y aura beaucoup plus de données.
En tout cas merci de m'avoir appris à utiliser la fonction dictionary qui me sera utile pour d'autre chose.

Je teste vendredi si cela fonctionne correctement sur différent poste.

Encore merci.

PS : je cloture le sujet apres verification sur les autres postes
 

Dranreb

XLDnaute Barbatruc
Non, correctement programmé ça devrait passer à 10 millisecondes !
Enfin peut être quand même un tout petit peu plus car chaque requête Excel de transfert de données est péniblement pénalisante quel que soit le nombre de données élémentaires transférées par chacune.
 

Discussions similaires

Réponses
12
Affichages
506
Réponses
2
Affichages
129

Statistiques des forums

Discussions
311 720
Messages
2 081 907
Membres
101 836
dernier inscrit
karmon