Discussion :

[eliovan]

bonjour a tous,

voici mon problème :

- j'ai + de 100 000 lignes a traiter
- chaque ligne dispose d'un code
- au total j'ai 236 codes différents

mon but est d'additionner chaque temps de chaque ligne en fonction de chaque code

Je connais le code vba suivant qui permet de réaliser l'addition de chaque ligne comportant un code précis (dans mon cas le "1") :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Dim cellule As Range
 
Range("a1", Range("a300000").End(xlUp)).Select
 
For Each cellule In Selection.Cells
 
If cellule.Value = "1" Then
cellule.Offset(0, 2).Copy
Range("g2").Activate
ActiveCell.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
End If
 
Next cellule

comment répéter ce code autant de fois que necessaire sans avoir a me farcir le même bout de code pour les 236 codes différents?

j'ai pensé a du "NEXT ___FOR" imriqué mais la je sèche.

Au secours SVP

NB : je voulais dire du "FOR EACH -_-_-NEXT"

[Philippe Tulliez]

Bonjour,
Si j'ai bien compris ton explication, tu veux faire la somme des cellules de la colonne C si la cellule de la colonne A se trouvant sur la même ligne a comme valeur "1" (chaîne de caractères). Ce résultat devant se trouver en cellule G2.
Si c'est bien cela que tu souhaites, la formule SOMME.SI est la formule qui pourra t'apporter la solution. Même s'il faut la placer en VBA

[eliovan]

Le probleme c est que j ai 110000 lignes pour 236 codes différents

[eriiic]

Bonjour,

Pourquoi ne pas faire un tableau croisé dynamique ?
eric

[joe.levrai]

Bonjour,

solution en VBA puisqu'on est en section VBA, à tester et confirmer que ok.

Environnement prévu :

- les codes sont listés en colonne A et commencent en A1, sans aucune ligne vide sur la plage
- les quantités sont listées en colonne C et commencent en C1, sans aucune ligne vide sur la plage
- les colonnes G et H sont vides

Prérequis :

- écrire en H1 : "CODE"
- écrire en G1 : "VALEUR"


Coller la macro suivante dans un module standard du classeur qui contient les données
Changer dans la macro le nom de la feuille "Le Nom de Ta Feuille" pour mettre le nom de ta propre feuille


Résultat testé :

- en colonne H, tu as le listing de l'ensemble des codes trouvés (sans doublon)
- en colonne G, tu as les quantités additionnées qui se rapportent au code


L'avantage étant qu'on est pas sur une liste de "codes" figés (qu'il y en ai 236 ou 1 seul ... les noms peuvent également changer du jour au lendemain sans que le code soit à modifier)

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
Sub Develo()
 
Dim CellSource As Range
Dim CellDest As Range
Dim Sh As Worksheet
Dim ListeCode As String
 
Application.ScreenUpdating = False
 
' la liste qui va recenser les codes déjà trouvés
ListeCode = "|"
 
Set Sh = ThisWorkbook.Worksheets("Le Nom de Ta Feuille")
 
With Sh
 
    ' pour chaque cellule contenant un code
    For Each CellSource In .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Cells
 
        ' si on ne trouve pas le code qu'on cherche
        If Not ListeCode Like "*" & "|" & CellSource & "|" & "*" Then
 
            ' on ajoute le code dans la liste (avec un simple séparateur pour ôter les biais)
            ListeCode = ListeCode & CellSource & "|"
 
            ' on prend la première cellule non vide en G
            Set CellDest = .Range("G" & Rows.Count).End(xlUp)(2)
 
            ' on y écrit la valeur
            CellDest.Offset(0, 1) = CellSource
 
            ' on indique en colonne H le code qui s'y rapporte
            CellDest = CellSource.Offset(0, 2)
 
        ' si le code qu'on teste est déjà dans la liste
        Else
 
            ' on cherche dans le listing des codes déjà recencés en colonne H
            For Each CellDest In .Range("H2", .Range("H" & Rows.Count).End(xlUp)).Cells
 
                ' dès qu'on a trouvé le code
                If CellDest = CellSource Then
 
                    ' on ajoute la nouvelle quantitée
                    CellDest.Offset(0, -1) = CellSource.Offset(0, 2) + CellDest.Offset(0, -1)
                    Exit For
                End If
 
            Next CellDest
 
        End If
 
    Next CellSource
 
End With
 
Set Sh = Nothing
 
Application.ScreenUpdating = True
 
End Sub

[eliovan]

bonjour,

merci pour ce code,

cependant j'ai trouvé mon bonheur différrement voici mon code :

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
Dim cellule As Range
Dim unite As Range
 
Range("I1", Range("I300000").End(xlUp)).Select
 
For Each unite In Selection.Cells
 
Range("a1", Range("a300000").End(xlUp)).Select
 
For Each cellule In Selection.Cells
 
If cellule.Value = unite.Value Then
unite.Offset(0, 2).Value = unite.Offset(0, 2).Value + 1
 
cellule.Offset(0, 3).Copy
unite.Offset(0, 1).Activate
ActiveCell.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
End If
 
Next cellule
 
Next unite

le temps de traitement est long car je dois traiter 326 fois 110 000 lignes mais je penses ajouter le fait d'effacer chaque ligne a chaque fois qu'elle a été traiter.

merci à vous tous.

[Philippe Tulliez]

Bonjour,
A la lecture de ton code, je réalise que la formule que je te proposais n'était pas la bonne, c'est NB.SI qu'il fallait.
Soit par exemple

Code :

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

=NB.SI(Feuil1!$A$2:$A$10000;I2)

Je ne voudrais évidemment pas gâcher ton bonheur mais je suis convaincu que ce code est plus rapide en exécution (moins d'une seconde après test sur 10000 lignes de 326 codes).

Code :

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

1
2
3
4
5
6
7
8
 Dim myFormula As String, SearchAddress As String, rngFunction As Range
 myFormula = "=COUNTIF(<SearchAddr>,I2)"
 Set rngFunction = Range("I2", Range("I13000").End(xlUp)).Offset(columnoffset:=2)
 SearchAddress = Range("A2", Range("A13000").End(xlUp)).Address(external:=True)
 With rngFunction
 .Formula = Replace(myFormula, "<SearchAddr>", SearchAddress)
 .Value = .Value ' Remplace la formule pas sa valeur retournée
 End With

Alors j'ai laissé ta recherche depuis la ligne 13000 et n'ai pas précisé le classeur et la feuille du Range mais il est évident que le code pourrait être plus propre.
Attention le code commence le calcul à partir de la ligne 2, il faut l'adapter. De plus il affiche une valeur nulle si la valeur n'est pas trouvée. Une petite adaptation du code pourrait éviter cela.

[eriiic]

Bonjour,

Je m'excuse d'insister : pourquoi ne pas faire un tableau croisé dynamique ?
Résultat en 0.5 s...

eric

[Philippe Tulliez]

Bonjour Eric,

Je m'excuse d'insister : pourquoi ne pas faire un tableau croisé dynamique ?

Nous sommes bien d'accord mais peut-être qu'Eliovan souhaite ensuite effectuer un autre traitement

[eriiic]

Possible.
Mais comme il est nouveau sur le forum ça serait bien qu'il prenne l'habitude de regarder au-dessus de la dernière réponse voir s'il y a pas d'autres propositions, et qu'il fasse un retour qu'elles le satisfassent ou non ;-)

eric