Gain de temps par gestion des tableaux

Bonjour à tous,

J'ai vu une fiche bien faite sur ce forum sur le gain de temps que permet l'utilisation d'un tableau plutôt qu'une boucle pour transférer des données d'un tableau vers un autre. J'ai bien compris qu'une macro de ce type permet de faire un comptage de données et de les renvoyer dans une matrice à 2 entrées par exemple.

La question que je me pose est de savoir si je peux utiliser cette approche pour renvoyer des données d'un tableau vers un autre au même format (sans comptage).

Pour être plus clair, j'ai un fichier dans lequel j'ai 100 000 références auxquelles sont attachées une date et un chiffre dans 2 colonnes distinctes.

Je veux les renvoyer dans un autre fichier ayant la même forme, sans faire un copier coller des colonnes car certaines données peuvent avoir changé dans un tableau et pas dans l'autre.

Actuellement, je filtre dans mon premier tableau les données que je veux exporter, puis avec une boucle je renvoie chaque référence dans une variable (et les données qui y sont attachées dans les 2 autres colonnes dans 2 autres variables), puis je cherche la référence dans l'autre fichier et colle les données en face.

Le problème est que c'est extrêmement long compte tenu de la quantité de données, mais je ne vois pas comment adapter la macro avec utilisation de tableau comme variable pour faire ça.

SI quelqu'un a une idée, elle est bienvenue !

Merci aux bonnes volontés !

Bonjour,

une question semblable a été posée ici

https://forum.excel-pratique.com/viewtopic.php?f=2&t=139800

si tu veux une solution adaptée à ton fichier, mets un fichier exemple avec quelques données tests

Bonjour h2so4,

Et merci pour ta réponse hyper rapide !

J'ai vu le sujet que tu m'as conseillé, mais j'avoue avoir un peu de mal à le comprendre. Aussi je profite de ta sympathique proposition de mettre un fichier test en PJ pour le cas où tu trouverais le temps d'adapter mon code avec le système des tableaux et bibliothèques. Car si mon code fonctionne, sur 100 000 lignes, il est désespérément lent !

Merci encore !

Mon fichier

bonjour,

une proposition

Sub aargh()
    Set ws1 = Sheets("tableau1")
    Set ws2 = Sheets("tableau2")
    dl2 = ws2.Cells(Rows.Count, 3).End(xlUp).Row
    tab2 = ws2.Cells(1, 1).Resize(dl2, 36)
    Set dict = CreateObject("scripting.dictionary")
    dl1 = ws1.Cells(Rows.Count, 3).End(xlUp).Row
    tab1 = ws1.Cells(1, 1).Resize(dl1, 36)
    For i = LBound(tab1) To UBound(tab1)
        dict.Add tab1(i, 3), i
    Next i
    col = Array(21, 30, 31, 34, 35, 36)
    For i = 3 To dl2
        If tab2(i, 36) = "B" Then
            k = dict(tab2(i, 3))
            For j = LBound(col) To UBound(col)
                tab2(i, col(j)) = tab1(k, col(j))
            Next j
        End If
    Next i
    ws2.Cells(1, 1).Resize(dl2, 36) = tab2
End Sub

Merci pour cette rapidité incroyable !

J'ai peut-être un peu trop bêtement collé ton code dans mon fichier et ça ne marche pas ...

Or je ne vois pas trop comment l'adapter ...

L'as-tu testé dans mon fichier ?

Merci

Bonjour,

j'ai adapté le code et le fichier dans mon message précédent.

Bonjour,

Un grand merci, effectivement avec ton fichier ça marche mieux ! Il se trouve que par rapport au fichier test que je t'ai envoyé, toutes les colonnes vides intermédiaires sont également remplies.

Sur la ligne de code "tab2 = ws2.Cells(1, 1).Resize(dl2, 36)" j'ai une erreur d'exécution '6' : dépassement de capacité.

J'ai allégé mon fichier en vidant les colonnes non cruciales pour l’exécution de la macro et l'erreur disparaît, ce qui prouve bien que le problème est lié à la quantité de données présentes dans mes tableaux. Aurais-tu une ruse pour contourner ce dépassement de capacité" ?

Merci

bonjour,

as-tu modifié le code pour ajouter des déclarations de variables ?

si oui voici comment il aurait fallu le faire.

Sub aargh()
    Dim ws1 As Object, ws2 As Object, dict As Object
    Dim dl2 As Long, dl1 As Long, i As Long, j As Long
    Dim tab2, tab1, col 'variant
    Dim k As String

    Set ws1 = Sheets("tableau1")
    Set ws2 = Sheets("tableau2")
    dl2 = ws2.Cells(Rows.Count, 3).End(xlUp).Row
    tab2 = ws2.Cells(1, 1).Resize(dl2, 36)
    Set dict = CreateObject("scripting.dictionary")
    dl1 = ws1.Cells(Rows.Count, 3).End(xlUp).Row
    tab1 = ws1.Cells(1, 1).Resize(dl1, 36)
    For i = LBound(tab1) To UBound(tab1)
        dict.Add tab1(i, 3), i
    Next i
    col = Array(21, 30, 31, 34, 35, 36)
    For i = 3 To dl2
        If tab2(i, 36) = "B" Then
            k = dict(tab2(i, 3))
            For j = LBound(col) To UBound(col)
                tab2(i, col(j)) = tab1(k, col(j))
            Next j
        End If
    Next i
    ws2.Cells(1, 1).Resize(dl2, 36) = tab2
End Sub

Sinon il faudra passer par une solution moins performante. Mais j'attends ta réponse avant de m'y attaquer.

Oui je me suis demandé si ce n'était pas une question de déclaration de variable, mais ce n'est pas le problème ... Dans le doute j'ai repris tes déclarations pour valider les miennes, mais le problème demeure ...

bonjour,

code adapté pour utiliser moins de mémoire.

Option Explicit

Sub aargh()
    Dim ws1 As Object, ws2 As Object, dict As Object
    Dim dl2 As Long, dl1 As Long, i As Long, j As Long
    Dim tab2, tab1, col 'variant
    Dim k As String
    Dim tablo()

    Set ws1 = Sheets("tableau1")
    Set ws2 = Sheets("tableau2")
    dl2 = ws2.Cells(Rows.Count, 3).End(xlUp).Row
    Set dict = CreateObject("scripting.dictionary")
    dl1 = ws1.Cells(Rows.Count, 3).End(xlUp).Row
    tab1 = ws1.Cells(1, 3).Resize(dl1, 1)
    For i = LBound(tab1) To UBound(tab1)
        dict.Add tab1(i, 1), i
    Next i
    col = Array(3, 21, 30, 31, 34, 35, 36)
    ReDim tablo(UBound(col))
    For i = LBound(col) To UBound(col)
        tablo(i) = ws2.Cells(1, col(i)).Resize(dl2, col(i))
    Next i
    For i = 3 To dl2
        If tablo(UBound(col))(i, 1) = "B" Then 'colonne 36
            k = dict(tablo(0)(i, 1))
            For j = LBound(col) To UBound(col)
                tablo(j)(i, 1) = ws1.Cells(k, col(j)).Value
            Next j
        End If
    Next i
    For i = LBound(col) To UBound(col)
        ws2.Cells(1, col(i)).Resize(dl2, col(i)) = tablo(i)
    Next i
End Sub

Un grand merci h2so4 pour tout le temps que tu as bien voulu passer sur mon problème, cette approche par tableau m'ouvre de nouveaux horizons !

bonjour,

merci pour ton retour.

pour mon info, quel gain de performance as-tu obtenu ?

Avec ta macro, ça met 25 secondes, avec la mienne ... 16 minutes 46 !!! Fabuleux !

bonsoir,

merci pour ton retour !

bonjour tout le monde,

quelqu'un pourrai m'aider

https://forum.excel-pratique.com/viewtopic.php?f=2&t=140263

Bonjour h2so4,

Je croyais pouvoir adapter ton code comme un grand, ... mais hélas, je tourne en rond.

Voici un nouveau challenge pour toi si je peux continuer à abuser de ton temps.

Je t'ai mis le fichier en PJ avec ce que je voudrais faire dedans.

Un grand merci d'avance !