Discussion :

[sharox]

Bonjour a tous !!

Donc je vous explique mon problème,
j'ai un 1er classeur, (2200 Ko) qui ouvre un 2nd classeur en mode caché quand j'appuie sur un bouton, et fait plein de recherche dedans.(j'ai envieront 14 boutons similaires)
Le deuxième classeur (varie entre 800 Ko et 2400 Ko selon le bouton sur le quel j'appuie) s'ouvre très bien, mais après je ne sais pas ce qui se passe, ma macro met une éternité et dure pendant des heures QUE pour certain classeur (et pas les plus gros)

donc en gros pour un 2em classeur de 2000 Ko ma macro va durée 5min et pour un 2em classeur de 800Ko cela va durée plus de 30min (je coupe au bout d'un moment)

Ma macro est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Sub Macro_Recherche()
 
         Sheets("Feuill1-classeur1").Select
 
  'Ouverture du fichier de prix (en caché)
        Dim xlApp As Object
        Set xlApp = CreateObject("Excel.Application")
        Dim xlWk As Workbook
        Set xlWk = xlApp.Workbooks.Open(Chemin_dossier & Dossier_sources)
        Dim xlWs As Worksheet
        Set xlWs = xlWk.Worksheets(1)
 
    For i = 11 To 81 Step 5
            Dim rngArticle As Range
            Dim myWs As Worksheet
            Set myWs = ThisWorkbook.ActiveSheet
            DerLig = myWs.Cells(Rows.Count, i).End(xlUp).Row
            Set rngArticle = myWs.Range(myWs.Cells(4, i), myWs.Cells(DerLig, i))
 
            'Recherche de l'article dans l'autre fichier
            Dim rngArticleRecherche As Range
            Set rngArticleRecherche = xlWs.Range(xlWs.Range("A2"), xlWs.Range("A65536").End(xlUp)) 'ICI LA COLONNE RECHERCHE
 
            Dim rngRefTrouve As Range
            Dim cell As Range
 
            For Each cell In rngArticle
                    Set rngRefTrouve = rngArticleRecherche.Find(cell.Value, , xlValues, xlWhole)
 
              If rngRefTrouve Is Nothing Then
            Else
                cell.Offset(, 1).Value = rngRefTrouve.Offset(, 6).Value
 
                    End If
            Next
    Next
 
    'Libération des ressources (fermeture du fichier caché)
    Set xlWs = Nothing
    xlWk.Close (False)
    Set xlWk = Nothing
    xlApp.Quit
    Set xlApp = Nothing
 
    End Sub

je ne sais pas du tout quoi faire ^^'
Merci de m'aider

Cordialement

[mercatog]

Essaies comme ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Sub Macro_Recherche()
Dim rngArticle As Range, rngArticleRecherche As Range, rngRefTrouve As Range, Cell As Range
Dim xlWs As Worksheet, myWs As Worksheet
Dim xlWk As Workbook
Dim DerLig As Long
Dim i As Integer
 
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
 
Set xlWk = Workbooks.Open(Chemin_Dossier & Dossier_Sources)
Set xlWs = xlWk.Worksheets(1)
 
Set myWs = ThisWorkbook.Worksheets("Feuill1-classeur1")
With myWs
    For i = 11 To 81 Step 5
        DerLig = .Cells(.Rows.Count, i).End(xlUp).Row
        Set rngArticle = .Range(.Cells(4, i), .Cells(DerLig, i))
 
        'Recherche de l'article dans l'autre fichier
        Set rngArticleRecherche = xlWs.Range(xlWs.Range("A2"), xlWs.Cells(.Rows.Count, 1).End(xlUp))    'ICI LA COLONNE RECHERCHE
 
        For Each Cell In rngArticle
            Set rngRefTrouve = rngArticleRecherche.Find(Cell.Value, , xlValues, xlWhole)
            If Not rngRefTrouve Is Nothing Then Cell.Offset(, 1).Value = rngRefTrouve.Offset(, 6).Value
        Next Cell
    Next i
End With
Set myWs = Nothing
Set xlWs = Nothing
xlWk.Close False
Set xlWk = Nothing
MsgBox "Triatement terminé!"
 
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

[el_slapper]

Je pense qu'il y a un problème d'indentation dans ton code, mais ça n'est pas la racine de ton problème.

Par contre application.ScreenUpdating est une petite variable d'environnement qui fait parfois des miracles. Si tu mets application.ScreenUpdating = False au début de ta sub, et application.ScreenUpdating = False à la fin, ça ira plus vite. Ou pas.


Si ça ne suffit pas(souvent, ça suffit, mais tu n'as pas d'insertions de lignes, alors je ne suis pas sur que ça suffise), il faut regarder l'algorithme. Il me semble que la clef du truc, c'est ton find. comme il est systématique, ça finit par faire lourd. Pour aller plus vite, il est parfois nécéssaire de faire des tableaux, voire même de les indexer avec des dictionnaires. Un tutorial sur le sujet. C'est plus performant de travailler sur un tableau que sur un onglet EXCEL comme tu le fais, mais c'est aussi plus compliqué à coder.

(ou alors il y a un truc évident qui m'a échappé. Mais la programmation semble assez propre et standard, indentation mise à part. Je ne suis pas convaincu que tu t'en sortes sans aller dans le compliqué).



EDIT : grillé!!! (et par maitre Mercatog, en plus, je passe pour un rigolo, après, moi...)

[mercatog]

@el_slapper, bonjour
Loin de là

Ton intervention est pertinente.

[sharox]

Merci beaucoup d'avoir répondu,

Alors on va commencer par le premier qui m'a répondu ^^
Mercatog.

Donc j'ai copier coller ton code, et encore jamais vue au par avent, mon écran devient tout gris ^^ avec le logo Excel en haut a gauche
J'ai du stopper l'application au bout de 5-10 minutes environ, donc cela n'est pas plus rapide
Par contre mon processeur tourne a fond de balle pour ton code et le mien (90%-75%) ce qui signifie que Excel fait bien quelque chose... mais quoi ^^
Enfin bref les mystère de l'informatique

El_slapper :
La fonction application.ScreenUpdating , je l'ai déjà utilisé et absolument aucun effet... j'ai attendu au moins 5-10 minutes pareils et j'ai stopper



Pour le reste de ton commentaire avec les tableaux et dictionnaires, si je peux éviter d'aller dans le compliquer ^^ je le ferais
Le travail que je fait est un cadeau pourris enfaite et si je peux le quitter le plus vite possible sans me prendre la tête je le ferais

Ensuite je ne comprend pas, le code est le même pour tout les fichier pourquoi pour certain ca marche et pas pour d'autre ?!
Et a cette question je me suis dit ca dois être dans le fichier Excel qu'un truc cloche et je ne trouve pas ce qui cloche -_-

Cordialement

[el_slapper]

(.../...)Ensuite je ne comprend pas, le code est le même pour tout les fichier pourquoi pour certain ca marche et pas pour d'autre ?!
Et a cette question je me suis dit ca dois être dans le fichier Excel qu'un truc cloche et je ne trouve pas ce qui cloche -_-

Cordialement

Hypothèse : ce n'est pas la taille du fichier qui compte, c'est plus la colonne "A" de ton onglet xlWS. Comme tu passes ta vie à faire des Find là-dedans, si il arrive que c'est particulièrement long, et que ce que tu cherches est toujours à la fin(voir carrément absent), ça explique pourquoi c'est plus long. (mais je ne suis pas sur).

Je n'ai hélas plus avec moi le code de dictionnaire que j'avais codé chez un autre client. C'était un peu laborieux(comparé au code magnifique de Mercatog), mais j'étais passé de 2 heures d'execution à 10 secondes(dont une seule de comparaison indexée des 100 000 éléments avec les 160 000 de référence, le reste, c'était ouvrir les fichiers excel et remplir les tableaux).

Mais avant de savoir si je galèges ou si je brûle, je dois savoir quelle est la taille de cette fameuse colonne ou tu cherches en permanence des trucs. Si elle est colossalle, alors je suis dans le vrai. Sinon, je ne sais pas.

[sharox]

el-slapper,

enfaite au début j'avais que 2 fichiers
puis étant donné que mon deuxième faisait 20Go et 53 000 lignes / 15 colonnes
Donc je l'ai coupé en 8 morceaux qui font environ entre 6000 et 7000 lignes ( j'ai pris fait pour que ca soit presque la même taille)
(d'ailleurs ca m'a surpris car 20 / 8 = 2.5 et aucun de mes fichier dépasse les 2.4^^)

mais je me demande aussi si c'est pas le fait que à force de rechercher et de rien trouver il s'encrasse pas un peu et ca fini par ramer
Car dans mes deux fichier il doit y avoir entre 0 et 50 références grand max que je recherches.
donc 750 recherches x 6000 lignes = 4 500 000
donc de trouver ca dois lui débloquer le processeur ^^ et c'est pour ca qu'avec les autres ils terminent.

Enfin bref
Merci
Cordialement

[mercatog]

J'ajoute un exit for dès que l'article cherché serait trouvé

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
            If Not rngRefTrouve Is Nothing Then
                Cell.Offset(, 1).Value = rngRefTrouve.Offset(, 6).Value
                Exit For
            End If

Sinon au vu du nombre de lignes et la taille des fichiers, je me demande