Recherche trop longue pour Excel

Question

Pour faire ma recherche je fais le code ci dessous. Mais mon tableau F1 contient 65000 lignes et le tableau F2 34000, si bien que quand excel se plante très rapidement avec un message excel ne répond pas. est ce qu'il y a un moyen différent de faire ma recherche ? ou bien existe t il une fonction pour faire tourner la recherche en priorité sans que excel se plante ? ou finalement vais je être obligé de scinder mes tableaux en plusieurs parties pour lancer des bouts de recherche et concaténer le tout. Merci d'avance, Ps : je ne mets pas de pièce jointe car la macro de recherche fonctionne si je réduis le nombre de lignes de mes tableaux Sub Recherche() Dim F1, F2 As Range Dim i, j, e As Integer Dim DL2, DL1 As Long Dim T1 As Workbook Dim T1F, T2F, T3R, T3B As Worksheet Set T1 = Workbooks("Test contrats.xlsm") Set T1F = Workbooks("Test contrats.xlsm").Sheets("Report 1") Set T2F = Workbooks("Test contrats.xlsm").Sheets("Report 2") Set T3R = Workbooks("Test contrats.xlsm").Sheets("Récap") Set T3B = Workbooks("Test contrats.xlsm").Sheets("Base installée Fr") DL2 = T2F.Range("B" &amp; Rows.Count).End(xlUp).Row DL1 = T3B.Range("N" &amp; Rows.Count).End(xlUp).Row Set F2 = T2F.Range("AD2:AD" &amp; DL2) Set F1 = T3B.Range("B1:B" &amp; DL1) e = 2 Application.ScreenUpdating = False For i = 1 To F1.Rows.Count For j = 1 To F2.Rows.Count If F1(i, 1).Value = F2(j, 1).Value Then F2(j, 38).Value = F1(i, 6) ' colonne 38 car c'est le décallage par rapport à la colonne 2 (B) 'T2F.Rows(j).Copy T3R.Rows(e) e = e + 1 End If Next j Next i Application.ScreenUpdating = True End Sub...

MFerrand · Accepted Answer

Curieux pour les nombres, le petit essai que j'ai fait ne montrait pas d'anomalie, ils étaient reconnus comme nombres une fois affectés. Sinon le dico, effectivement ne gère pas de valeur numérique en tant que telle. Et les dates pourraient aussi poser quelque problème : j'ai retenu à cet égard la préconisation de Boisgontier d'utiliser la propriété Value2 (à la place de Value) pour mettre des dates en dico. La particularité de Value2 est qu'elle ignore les types de données spécifiques Date et Currency et les renvoie exclusivement sous forme de nombre. Si tu as besoin de convertir tes montants à la sortie : T1f(d1F(k), 1) = Val(Replace(F2.Cells(d2F(k), 2), ",", ".")) Val est le plus pratique, la fonction ignore les espaces, mais elle ne reconnaît que le point et bloque sur la virgule, d'où la petite gymnastique. Mais tu retrouves la virgule dans les valeurs affectées... Sinon le dico ne permet qu'une association de 2 valeurs : key-item. La clé identifie un élément unique du dico, ce qui en fait un filtre à doublons particulièrement rapide : tu boucles les éléments d'une liste en les basculant en clés de dico (valeur de l'item ="") sans te préoccuper, puis tu bascules sur un tableau (tablo = dico.keys), et tu as un tableau d'éléments sans doublon. Autre utilisation utile : la recherche multicritère, tu affectes les valeurs à renvoyer par la recherche en item de dico dont la clé est la concaténation des critères (ça a de l'intérêt avec plusieurs dizaines de milliers de lignes à cause de la rapidité...). A la fin tu n'as plus qu'à appeler l'élément dico voulu en concaténant tes critères de recherche, et tu as le résultat (il faut bien sûr que l'élément cherché soit unique, ou alors prévoir un dispositif pour ne pas éliminer les doublons. Car tu ne peux affecter qu'une valeur comme item, mais tu peux concaténer des valeurs en utilisant un séparateur : à la sortie tu splites pour obtenir un tableau de valeurs... Bon dimanche...

gmb · Answer

Essaie en travaillant avec des variables ''Tableau" Sans fichier, difficile de tester. Bye ...

MFerrand · Answer

En préalable, je te signale que tu as un certain nombre de variables non typées. Toutes les variables non typées individuellement sont de type Variant. Cela peut se rectifier facilement, mais en fait la plupart de tes variables sont inutiles, donc autant assainir. J'aimerais que tu me confirmes que tu affectes bien : une valeur de la colonne G de la feuille Base à une cellule de la colonne BO de la feuille Report 2 ? Un doute m'est venu avec tes mentions en commentaires... Sinon effectivement, il faut essayer de travailler en tableau comme l'a conseillé GMB, ou bien j'essaie de voir la possibilité de travailler en dictionnaires. Cordialement...

MFerrand · Answer

Dim F1, F2 As Range Dim i, j, e As Integer Dim DL2, DL1 As Long Dim T1 As Workbook Dim T1F, T2F, T3R, T3B As Worksheet C'est tout simple : F1, i, j, DL2, T1F, T2F, T3R ne sont pas typées, donc de type Variant (ce qui peut par ailleurs accroître le ralentissement). Une variable doit être typée individuellement ! On ne type pas un groupe. &#91;On est sans doute tous passés par là aux débuts, mais ça s'apprend...&#93; Ceci dit, tu n'utilises en fait (dans ta macro actuelle que les variables Range, Long et 2 Integer. Le reste est donc inutile, sauf si ta macro 'réelle' comprend autre chose que tu as shunté. Mais auquel cas ce ne serait pas malin de ta part car tu ne peux escompter avoir une réponse utile et utilisable si tu biaises le contexte. On va considérer que ce n'est pas le cas (c'est comme la "présomption d'innocence" ) et dans ce cadre, j'aimerais bien avoir réponse à mes questions sur les colonnes impactées. Plus une question complémentaire concernant BO (colonne réceptrice) : est-ce qu'elle est préalablement vide ? ou non ? Cordialement...

MFerrand · Answer

Alors là : Set F2 = T2F.Range("AD2:AD" &amp; DL2) Pour moi, si F2 est définie dans la colonne AD, F2(j, 38) renvoie à BO ...

MFerrand · Answer

Bon ! Si ça met 2heures pour tourner, ya vraiment matière à chercher à améliorer...

Mon propos était de te proposer rapidement une procédure utilisant deux dictionnaires à tester... Pour la rapidité, le temps du petit déjeuner est passé... et je dois passer à autre chose. Mais si tu es intéressé, il me faut les bonnes références des plages : les colonnes testées sur chacune des feuilles et les lignes de début pour chacune (lignes de fin fournies par variables), la colonne de prélèvement lorsqu'une occurrence est trouvée, la colonne d'affectation.

Cordialement.

MFerrand · Answer

Je suis intéressé aussi par le résultat...

Je note les références. Donc 2 colonnes à servir : est-ce qu'elles contiennent des valeurs antérieures à préserver (pour le cas où rien à affecter) ?

Et les lignes de début sont 2 pour les 2 feuilles (ou décalées : 1 pour l'une et 2 pour l'autre dans ta macro) ?

Je dois m'absenter. A suivre.

MFerrand · Answer

Ok ! Pas encore parti en fait : quelques erreurs à résorber sur du code que je venais de livrer... du coup j'ai déjà griffoné les grandes lignes. Mais il vaut mieux que je l'écrive après décantation et en vérifiant les points dont je peux vérifier le fonctionnement... car dans les dernières erreurs, j'avais réussi à référencer une cellule avec 2 colonnes et aucune ligne !

J'ai quelques erreurs récurrentes quand je code, elles sont généralement rectifiées avant que je livre et je les retrouve assez vite depuis le temps... mais celle-la c'est la première fois, alors : pas prudent que j'écrive tout de suite.

A+

MFerrand · Answer

J'ai oublié une question importante, celle de la possibilité de doublons !?

En effet ta recherche qui pour chaque valeur de la colonne de F1 balayait toute la colonne de F2, si elle trouvait 2 (ou plus) fois la même valeur la traitait identiquement.

Par contre si doublon dans la colonne de F1, le résultat final correspondait à la dernière valeur doublonnée dont le traitement effaçait les précédents (que le résultat soit identique aux précédents ou non).

Or l'utilisation du dictionnaire élimine naturellement les doublons au départ (si on ne prend pas de disposition pour l'éviter), une clé de dictionnaire ne pouvant être doublonnée...

Il faut donc être fixé avant.

MFerrand · Answer

C'est le point délicat...

T2f.B contient donc les num. système qu'on va chercher, ils sont sans doublon, j'aurais donc dico d'éléments uniques au départ.

T1fAD contient également les num systèmes, qui là ne sont pas uniques : j'aurai un dico d'éléments uniques à la fin du balayage ;

pour les num doublons, celui qui demeurera en dico sera le plus loin dans la liste, qui aura effacé les précédent au cours du balayage.

Pour les éléments de T1f.AD qui coincident avec un élément de T2f.B, on prélève la valeur de Tf1.AH qui ira en T2f.AM (le montant donc).

Dans l'autre sens, on prélève la valeur de T2f.C qui ira en T1f.A (ça doit être le nom machine si j'ai compris).

Dans le cas des doublons évoqués, seul le dernier conservé, verra l'affectation en A.

Je vais jeter un oeil sur ton fichier avant de m'y mettre.

Mais c'est sur la mise au point du schéma et sa confirmation qu'on aura passé le plus de temps...

Ah !

AH est devenu AI ?

MFerrand · Answer

Tu rectifieras les noms de feuille le cas échéant, j'ai mis "Cible" et "Bdd".

J'ai retenu AI pour les montants, suite à ton fichier d'essai, pour AH, remplacer 6 par 5 dans cette ligne :

T2f(d2F(k), 1) = F1.Cells(d1F(k), 6)

Sub Recherche()
    Dim d1F As Object, d2F As Object, k, i&, j&, dl1&, dl2&
    Dim T1f(), T2f(), F1 As Range, F2 As Range
    '1re phase : dico F1, clé = valeur en AD, item = num. ligne
    Set d1F = CreateObject("Scripting.Dictionary")
    With Worksheets("Cible")
        dl1 = .Range("AD" & .Rows.Count).End(xlUp).Row
        Set F1 = .Range("AD2:AD" & dl1)
    End With
    For i = 1 To F1.Rows.Count
        d1F(F1.Cells(i, 1).Value) = i
    Next i
    '2e phase : dico F2, clé = valeur en B, item = num. ligne
    Set d2F = CreateObject("Scripting.Dictionary")
    With Worksheets("Bdd")
        dl2 = .Range("B" & .Rows.Count).End(xlUp).Row
        Set F2 = .Range("B2:B" & dl2)
    End With
    For i = 1 To F2.Rows.Count
        d2F(F2.Cells(i, 1).Value) = i
    Next i
    '3e phase : tableaux de valeurs à reporter
    ' - de F1(AI) vers F2(AM), - de F2(C) vers F1(A)
    ReDim T1f(1 To F1.Rows.Count, 1 To 1)
    ReDim T2f(1 To F2.Rows.Count, 1 To 1)
    For Each k In d1F.keys
        If d2F.exists(k) Then
            T2f(d2F(k), 1) = F1.Cells(d1F(k), 6)
            T1f(d1F(k), 1) = F2.Cells(d2F(k), 2)
        End If
    Next k
    '4e phase : affectations
    F1.Offset(, -29).Value = T1f
    F2.Offset(, 37).Value = T2f
End Sub

Tiens-moi au courant de tes essais et résultats...

Cordialement.