XL 2010 Accélérer vba excel 2007

[chilo27]

Bonsoir le forum

Je souhaite accélérer le décompte, mais je bug

je vous remercie chaleureusement pour votre aide
je joins un petit fichier
Merci par avance

 

[chilo27]

Bonsoir chilo27, patricktoulon, sylvanu,

S'agissant d'une comparaison dans n'importe quel ordre, pour aller vite il faut utiliser le Dictionary :

Sub Decompte()
Dim d As Object, j%, tablo, ub&, i&, n%
Set d = CreateObject("Scripting.Dictionary")
For j = 2 To 6: d(Cells(4, j).Value) = "": Next j 'liste sans doublon
With Range("A6", Range("F" & Rows.Count).End(xlUp)).Resize(, 6)
    If .Row < 6 Then Exit Sub
    tablo = .Value 'matrice, plus rapide
    ub = UBound(tablo)
    For i = 1 To ub
        n = 0
        For j = 2 To 6
            If d.exists(tablo(i, j)) Then n = n + 1
        Next j
        tablo(i, 1) = n
    Next i
    '---restitution---
    .Columns(1) = Application.Index(tablo, 0, 1)
    .Offset(ub).Resize(Rows.Count - ub - .Row + 1).ClearContents 'RAZ en dessous
End With
End Sub

A+

Bonsoir JoB75, Sylvanu
J'ai un message Incompatibilité" sur cette ligne .Columns(1) = Application.Index(tablo, 0, 1)
quand je dépasse ligne 65000
Je peux avoir au moins 600000 lignes à traiter

Mais autrement c'est rapide même si j'ai 65000 lignes

Merci

 

[job75]

Ce qui est fait dans le post #13.

Pas du tout, le problème posé au post #1 est de compter en colonne A le nombre d'occurrences des valeurs B4:F4 de chaque ligne.

 

[job75]

J'ai un message Incompatibilité" sur cette ligne .Columns(1) = Application.Index(tablo, 0, 1)
quand je dépasse ligne 65000
Je peux avoir au moins 600000 lignes à traiter

Chez moi sur 600 000 lignes pas de bug sur cette ligne, c'est peut-être dû à la version Excel.

Par contre sur la ligne suivante il y a bug.

Je corrige donc cette ligne dans mon post #9 avec :

.Rows(1).Offset(ub).Resize(Rows.Count - ub - .Row + 1).ClearContents 'RAZ en dessous

 

[mapomme]

Bonsoir à tous,

Pour le FUN. Une autre macro (si j'ai bien compris). Pas la plus rapide, mais relativement concise :

Sub DecompteMapommeRapide()
'utilisation de FormulaR1C1 au lieu FormulaLocal
Dim deb, der&
   deb = Timer: Application.ScreenUpdating = False
   Range(Range("a6"), Cells(Rows.Count, "a")).ClearContents
   der = Cells(Rows.Count, "b").End(xlUp).Row
   Range(Range("a6"), Cells(der, "a")).FormulaR1C1 = "=SUMPRODUCT(COUNTIF(R4C2:R4C6,RC[1]:RC[5]))"
   Range(Range("a6"), Cells(der, "a")) = Range(Range("a6"), Cells(der, "a")).Value
   MsgBox Format(Timer - deb, "0.00\ sec.")
End Sub

edit : modifié le code. Cette version est un peu plus rapide

 

[job75]

Donc si votre version Excel n'aime pas Application.Index utilisez ce fichier (2) avec :

Sub Decompte()
Dim d As Object, j%, tablo, ub&, resu%(), i&, n%
Set d = CreateObject("Scripting.Dictionary")
For j = 2 To 6: d(Cells(4, j).Value) = "": Next j 'liste sans doublon
With Range("A6", Range("F" & Rows.Count).End(xlUp)).Resize(, 6)
    If .Row < 6 Then Exit Sub
    tablo = .Value 'matrice, plus rapide
    ub = UBound(tablo)
    ReDim resu(1 To ub, 1 To 1)
    For i = 1 To ub
        n = 0
        For j = 1 To 6
            If d.exists(tablo(i, j)) Then n = n + 1
        Next j
        resu(i, 1) = n
    Next i
    '---restitution---
    .Columns(1) = resu
    .Rows(1).Offset(ub).Resize(Rows.Count - ub - .Row + 1).ClearContents 'RAZ en dessous
End With
End Sub

Il faut un tableau VBA supplémentaire (resu).

Salut mapomme, je pense que SOMMEPROD avec NB.SI prendra bien plus de temps.

 

[sylvanu]

@ MaPomme,
Chapeau bas l'artiste,
A isopérimètre, ça tourne autour de 169 ms pour 65000 lignes ( vs 400ms sans FormulaLocal en formule classique sur mon PC )

 

[job75]

Ah oui, je suis très surpris.

J'ai testé en recopiant le tableau B6:G23 sur 72 000 lignes :

- ma macro du post #20 => 0,59 seconde

- la macro de mapomme du post #19 => 0,28 seconde, c'est incroyable !

 

[chilo27]

Bonjour le forum, Mapomme, Jb75

Merci Beaucoup
pour votre aide le bug n'existe plus
Merci et bonne journée

 

[chilo27]

Rebonjour le Forum, Jb75, Mapomme

J'ai essayé les deux versions
elles me conviennent parfaitement
Merci à vous pour l'aide
Bonne journée

 

[mapomme]

sans FormulaLocal en formule classique sur mon PC

Effectivement l'utilisation de FolrmulaR1C1 au lieu de FormulaLocal est plus rapide. J'ai donc modifié le code de mon précédent message.

 

[mapomme]

Re Sylvanu 🙂, job75 🙂,

Je n'avais même pas fais les comparaisons. J'étais persuadé, comme @job75, que passer par Excel serait forcément plus lent (surtout avec un SOMMEPROD). Il faut se méfier des idées préconçues. Il y a des cas où...

 

[chilo27]

Rebonjour le forum, Job75, Mapomme sans oublier les autres

Ce n'est pas de la flatterie, je ne sais pas faire mais
vos propositions sont top, là ou je mettais de longues minutes, l'opération s'effectue en quelques secondes.
Mon souci est le choix en Job75 et mapomme car seulement quelques secondes de différences entre les deux

Merci à vos deux pour l'aide

 

[job75]

Prenez la solution de mapomme car la macro est plus simple.

 

[chilo27]

Rebonjour le forum, Jb75

Merci pour la suggestion, je vais vous faire rigoler j'utilise les deux
sur les dossiers différents