Optimisation d'une macro Excel constituée de boucles

Le 22/09/2011 à 14:19

Bonjour à tous,

Afin d'effectuer un traitement, j'ai crée une macro excel parcourant 2 colonnes à l'aide de 2 boucles for each.

A des fins de tests j'ai effectué ce traitement juste sur les 10 premières lignes des colonnes, et là tout fonctionne de manière correcte.

Le souci est que quand je fais le test sur la colonne entière (qui à plusieurs milliers de données), ben là c'est lent, bien trop lent, bref, pas exploitable.

Voici exactement ce que je cherche à faire:

J'ai une colonne A avec des références à partir de A2, certaines références peuvent être identiques.

La colonne B comporte des dates.

J'extrais à l'aide du filtre excel dans une colonne H les références uniques, et dans une colonne C la partie de la date qui m'intéresse, le mois en l'occurence.

Dans la colonne I se trouve le nombre d'occurence trouvée pour la référence dans la colonne H.

(En principe je dois utiliser ce nombre comme condition, mais pour me faciliter le traitement, je l'ai mis de coté pour le moment...)

Maintenant, je veux savoir pour chaque référence unique de la colonne H, si sa date correpond à un mois que j'aurais défini.

Je fais donc une boucle sur la liste des références uniques, puis une autre sur celle des références (colonne A).

Voici mon code:

Sub BouclePlagesCellules()

    Dim CellRefUni As Range
    Dim CellRef As Range
    i = 2
    j = 2

    'Réduction de la plage des références uniques pour les tests
    For Each CellRefUni In Sheets("Export Worksheet").Range("H2:H14")
    ' Plage totale des références uniques qui prend un temps trop proche de l'infini
    'For Each CellRefUni In Sheets("Export Worksheet").Range("H2", Range("H2").End(xlDown))
        j = 2
        'Réduction de la plage des références pour les tests
        For Each CellRef In Sheets("Export Worksheet").Range("A2:A14")
       'Plage totale des références qui prend un temps trop proche de l'infini
       'For Each CellRefUni In Sheets("Export Worksheet").Range("A2", Range("A2").End(xlDown))
            'Comparaison si les références sont identiques
            If CellRefUni.Value = CellRef.Value Then
                'Comparaison si le mois correspond
                if Range("C" & j).Value = Range("J1").Value Then Range("J" & i).Value = "Receive"
                ElseIf Range("J" & i).Value <> "Receive" Then Range("J" & i).Value = "NA"
            End If
            j = j + 1
        Next CellRef
       'i = i + 1
    Next CellRefUni
End Sub

J'ai fais quelques recherches:

For Each CellRefUni In Sheets("Export Worksheet").Range("H2:H14")
        Set p = Range("A2", Range("A2").End(xlDown)).SpecialCells(xlCellTypeConstants, xlTextValues)
        For Each c In p
             If Range("C" & ActiveCell.Row).Value = Range("J1").Value Then Range("J" & i).Value = "Receive"
             j = j + 1
        Next c
        i = i + 1
    Next CellRefUni

En gros utiliser les SpecialCells en remplaçant le xlTextValues pour les valeurs que je veux rechercher dans la colonne A, vu qu'il me semble que la fonction .find() ne ramène que la première occurence qu'il trouve...

Le truc c'est que je ne sais pas comment affecter le xlTextValues ni même si c'est possible...

Je ne connais pas bien les fonctions VBA et je sais pas s'il n'y a pas d'autres fonctions plus aptes à effectuer ce traitement ni si mon raisonnement algorithmique est correct...(Au départ je pensais pouvoir le faire juste à l'aide de simples fonctions directement dans excel, mais pas réussi)

Voila, toutes aides est la bienvenue, merci!

Le 22/09/2011 à 14:42

Bonjour stathalkha,

Peux-tu joindre ton fichier en ne gardant que quelques mais avec la structure des données ? On comprendra mieux le problème.

Le 22/09/2011 à 16:51

Bonjour,

Voici le fichier en question:

Une liste de réference dans la colonne A, les dates correspondantes colonne B

Je fais un filtre sur la colonne A pour supprimer les doublons et une formule excel qui écrit dans la colonne I m'indique combien de fois elles se trouvent dans la colonne A.

J'extrais le mois et l'année de la colonne B pour le placer dans la colonne C.

Je veux pouvoir savoir pour chaque référence dans la colonne H qui se trouve plusieurs fois dans la colonne A (Donc via le count dans la colonne I) si sa date correspond au mois + année qui se trouve dans la cellule J1, et si c'est le cas, l'indiquer dans la colonne J en face de la bonne référence...

Vous pouvez tester en lançant la macro "BouclePlagesCellules" qui fonctionne, mais qui est bien trop longue quand il y a plusieurs milliers de références...

Si vous avez une idée...

Merci!

Le 22/09/2011 à 18:02

Re,

Si j'ai bien compris le problème, plutôt qu'une macro, je te propose une formule à mettre en J2 et à tirer vers le bas (la colonne C devient alors obsolète) :

=SI(SOMMEPROD(($A$2:$A$14=$H2)*(STXT($B$2:$B$14;4;6)=J$1))>0;"Receive";"NA")

A tester avec toutes les références si ça ralentit beaucoup.

Par contre, il semblerait que la colonne I n'intervienne pas trop je me trompe ?

Le 23/09/2011 à 09:20

Bonjour,

Merci pour la formule, elle fonctionne très bien et rapidement!

Cependant, la colonne I doit intervenir quand même...

On doit pouvoir effectuer la vérification de la date seulement si la référence est présente plus d'une fois, du coup ça rajoute une condition et je vois pas trop comment l'implémenter dans ta formule...

Le 23/09/2011 à 09:36

Bonjour stathalkha,

Et si la référence n'apparait qu'une seule fois, que doit-on afficher ? NA ?

Si oui :

=SI(ET(SOMMEPROD(($A$2:$A$14=$H2)*(STXT($B$2:$B$14;4;6)=J$1))>0;NB.SI(A$2:A$14;$H2)>1);"Receive";"NA")

Plus besoin de la colonne I

Le 23/09/2011 à 09:41

Oui on affiche NA.

Cependant si on devait afficher un 3ème terme différent, de grosses modifications devraient intervenir?

Merci encore de ton aide!

Le 23/09/2011 à 09:48

Pas de grand changement.

Un exemple :

=SI(NB.SI(A$2:A$14;$H2)=1;"afficher_ce_quil_faut";SI(SOMMEPROD(($A$2:$A$14=$H2)*(STXT($B$2:$B$14;4;6)=J$1))>0;"Receive";"NA"))
Le 23/09/2011 à 11:20

Pour être sûr d'avoir tout compris:

=SI(
     ET(
           SOMMEPROD(
                      ($A$2:$A$14=$H2) 'On balaye la plage, si on trouve la même réference: 1, sinon: 0
                      *
                      (STXT($B$2:$B$14;4;6)=J$1) 'Extraction de la date et comparaison, si identique 1 sinon 0
                     )>0; 'Si le produit de la multplication est 0 (2 sont faux ou si l'un des deux est faux) => 0, si les deux sont ok => 1 
            NB.SI(A$2:A$14;$H2)>1 'Si la référence apparait plusieurs fois
        ); 'Le "ET" vérifie les deux conditions SOMMEPROD et NB.SI, si elles sont différentes de 0, premier cas, sinon second...
    "Receive";"NA"
    )

C'est bien ça?

Si c'est le cas merci beaucoup pour ton aide, ça m'a fait découvrir pas mal de choses!

Le 23/09/2011 à 12:14

Ça doit être ça si j'ai bien suivi ton raisonnement.

Rechercher des sujets similaires à "optimisation macro constituee boucles"