Discussion :

[Marc-L]

Daniel,

la fonction de feuille de calculs EQUIV (Match en VBA) peut renvoyer une erreur !

Donc la variable à utiliser conjointement doit être de type Variant et doit être vérifiée via la fonction IsError …

[Daniel.C]

Bonsoir Marc,

En fait il n'y a pas d'erreur sur une ligne précise ni de code ou message d'erreur, seulement "Exécution interrompue" :



Si je clique sur "Débogage", la ligne en erreur n'est pas toujours la même, souvent ces deux-ci :

Code :

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

Result(Lig, Col) = Result(Lig, Col) + Tabl(I, 11)

ou

Code :

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

Lig = Lig + 1

Si je clique sur Continuer, la macro se termine. Parfois, le bouton "Débogage" est grisé. Peut-être que l'ordi en a ras les ridelles et a besoin d'un redémarrage ?

[Marc-L]

Effectivement, ce n'est pas commun !

Je suppose tu as pensé à vérifier la valeur des variables …

[Loupion]

Bonjour Daniel,

Le code propose fonctionne et s execute plus rapidement

En revanche le resultat qui ressort du code n est pas celui attendu, je pense que toutes les variables et conditions du SUMIFS ne sont pas prises en compte dans le code

Le resultat ne correspond pas a ce que j attend car les criteres fixe du SUMIFS ne sont pas dans le code que vous proposez (Scenario, Business Unit, Category). Je ne sais pas comment les ajouter a votre code car je ne comprend pas les operations qui y sont effectuees.

[Daniel.C]

Bonjour à tous,

@Marc-L : oui. c'est bien du numérique pour les deux lignes citées. Le point commun est qu'il s'agit dans les deux cas d'additions ? Je pense que, peut-être Excel sature. J'ai mis des "DoEvents" et des "Wait" pour voir. Sans succès. Je vais re-tester ce matin (je viens de redémarrer).

@Loupion : J'ai peut-être mal interprété ce que tu voulais faire. A la limite, ça doit être facilement corrigé.

Je vous tiens au courant.

[Daniel.C]

Bonne nouvelle; ça ne plante plus. Comme quoi, un bon reboot, des fois... Reste plus qu'à obtenir un résultat correct.

[Marc-L]

Tant mieux !

Sinon la fonction Match pouvant renvoyer une erreur, il faut vraiment être sûr de son coup pour ne pas vérifier son résultat !
Voici un exemple avec une sécurité dans cette discussion …

[Daniel.C]

Oui, c'est un cas possible de plantage, mais ici les données sont correctes, sinon j'aurais mis un traitement d'erreur, bien sûr. J'ajoute, pour les générations qui nous liront que "Application.Match" peut générer une erreur, mais que celle-ci peut se traiter avec :

Code :

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

On Error Resume Next

contrairement à la syntaxe "WorkSheetFunction.Match" qu'il vaut donc mieux éviter.

[Marc-L]

Proscrire l'instruction On Error Resume Next, c'est inutile comme dans la discussion en lien dans mon précédent message !

Application.Match ne génère pas une erreur dans l'exécution du code si elle est combinée à une variable de type Variant
et alors c'est donc le contenu de cette variable se trouvant en erreur, à tester donc avec l'instruction IsError …

[Daniel.C]

Juste, pas encore tout à fait réveillé. variable de type variant + test si numérique; c'est en plus ce que je fais par routine.
PS. Je vais me faire un café. A+

Pour être complet, il y a des cas où l'on ne peut pas éviter "On Error", par exemple, la définition d'une page avec "SpecialCells".

[Marc-L]

Même avec SpecialCells j'évite parfois l'instruction On Error Resume Next !

J'utilise la fonction CountA (NB.SI) sur la plage de cellules après (ou avant) un filtre sur une valeur …

Sinon avec On Error j'externalise dans une fonction ou procédure autonome comme dans cette autre discussion …

[Loupion]

Je viens d essayer un nouveau code en passant par un concatenate mais il met quasiment autant de temps a s executer

J ai dans la feuille extraction cree une colonne supplementaire directement avec le concatenate pour eviter de le faire via un formule dans le code VBA, rien ne change....

Le code de Daniel s execute plus rapidement mais je ne suis pas capable de le modifier pour y integrer les variables manquantes, je suis un peu limite dans mes connaissances de code et le code propose par Daniel est plus complexe

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 Concatenate()
 
Dim I As Integer
Dim j As Integer
 
Dim Concatenate As Range
Dim Amount As Range
Dim Result As Double
Dim Concatenate2 As String
 
Set Concatenate = ActiveWorkbook.Sheets("Extract").Range("L:L")
Set Amount = ActiveWorkbook.Sheets("Extract").Range("K:K")
 
Application.ScreenUpdating = False
For j = 3 To 14
For I = 8 To 31
 
Set Monthdata = ActiveWorkbook.Sheets("Analysis").Cells(5, j)      'Ligne 5, colonne variable'
Set BUdata = ActiveWorkbook.Sheets("Analysis").Range("A1")          'fixe'
Set Scenariodata = ActiveWorkbook.Sheets("Analysis").Cells(6, j)  'Ligne 6, colonne variable'
Set Branddata = ActiveWorkbook.Sheets("Analysis").Cells(I, 1)      'Ligne variable, colonne A'
Set Axedata = ActiveWorkbook.Sheets("Analysis").Cells(I, 2)         'Ligne variable, colonne B'
Set Categorydata = ActiveWorkbook.Sheets("Analysis").Range("A2")    'fixe'
Set Scenario2data = ActiveWorkbook.Sheets("Analysis").Range("A4")   'fixe'
 
Concatenate2 = BUdata & Categorydata & Scenariodata & Monthdata & Branddata & Axedata
 
Result = WorksheetFunction.SumIf(Concatenate, Concatenate2, Amount)
 
Worksheets("Analysis").Cells(I, j) = Result
 
 
 
Next I
Next j
 
Application.ScreenUpdating = True
MsgBox ("Update done")
 
 
End Sub

[Daniel.C]

Même avec SpecialCells j'évite parfois l'instruction On Error Resume Next !

J'utilise la fonction CountA (NB.SI) sur la plage de cellules après (ou avant) un filtre sur une valeur …

Pas toujours selon le type de cellules recherché.

Sinon avec On Error j'externalise dans une fonction ou procédure autonome

C'est donc que tu l'utilises en dernier recours
De toutes façons, c'est très acceptable si l'on peut tester le code d'erreur.

@Loupion :
Pour accélerer le processus, peux-tu me dire ce que je dois comparer pour chaque ligne de Extract ? Je n'ai pas trop le temps en ce moment.

On se rapproche :

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
Sub TestDaniel()
     Dim Tabl As Variant, Mois As Variant, Brand As Variant, Prod As Variant
     Dim i As Long, Result As Variant, Ligne As Long
     Dim Lig As Integer, Col As Integer
     Dim Categorydata As Range, Scenario2data As Range, BUdata As String
     With Sheets("Extract")
        Ligne = .Cells(.Rows.Count, 1).End(xlUp).Row
        Tabl = .Range("A11", .Cells(Ligne, 11))
     End With
 
     With Sheets("Analysis")
        Set Categorydata = .Range("A2")    'fixe'
        Set Scenario2data = .Range("A4")   'fixe'
        BUdata = .Range("A1").Value          'fixe'
        .[C7:N21].ClearContents
        Result = .[C7:N21]
        Mois = .[C5:N5]
        Brand = Application.Transpose(.[A7:A21])
        Prod = Application.Transpose(.[B7:B21])
        For i = 1 To UBound(Tabl, 1)
            Col = Application.Match(Tabl(i, 2), Mois, 0)
            If Tabl(i, 4) = BUdata And (UCase(Tabl(i, 5)) = UCase(.Cells(6, Col + 2).Value) Or UCase(Tabl(i, 5)) = UCase(.[A4].Value)) And UCase(Tabl(i, 1)) = UCase(.[A2].Value) Then
                Lig = 1
                Do Until UCase(Tabl(i, 8)) = UCase(Brand(Lig)) And UCase(Tabl(i, 9)) = UCase(Prod(Lig))
                    Lig = Lig + 1
                Loop
                Result(Lig, Col) = Result(Lig, Col) + Tabl(i, 11)
            End If
        Next i
        .[C7:N21] = Result
     End With
     Debug.Print i
End Sub

Par contre, je ne m'explique pas les 2000 en C7. Comment les calculer ?

[Loupion]

Daniel,

Les 2000 sont un sous total, je pense que je vais les enlever du fichier

Le code fonctionne et il est le plus rapide a executer (un peu moins de 20 secondes sur le fichier final).

Je ne comprend pas toutes les etapes du code, et les operations qui y sont faites, cela restera ma petite deception, mais je ne vais pas me plaindre.

Pourriez vous eventuellement expliquer dans le code des actions qui sont effectuees?

Merci

[Daniel.C]

Bien sûr. Sinon, où serait l'intérêt du forum ? Je vais mettre des commentaires dans le code et supprimer ce qui est superflu. En gros, au lieu de travailler avec des plages de cellules, je copie en bloc ces plages (seulement les valeurs) en mémoire. C'est beaucoup plus rapide, parce que quand tu utilises l'objet "A1", tu désignes toutes les propriétés de cet objet alors qu'en mémoire, on ne manipule que la propriété "Value".

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
Sub TestDaniel()
     Dim Tabl As Variant, Mois As Variant, Brand As Variant, Prod As Variant
     Dim I As Long, Result As Variant, Ligne As Long
     Dim Lig As Integer, Col As Integer
     Dim BUdata As String
     With Sheets("Extract")
        '"Ligne" représente la dernière ligne des données de la feuille Extract
        Ligne = .Cells(.Rows.Count, 1).End(xlUp).Row
        '"Tabl" est une variable tableau (ou array) contenant la plage des données de la feuille Extract
        'Voir la figure 1 dans le message
        Tabl = .Range("A11", .Cells(Ligne, 11))
     End With
     With Sheets("Analysis")
        BUdata = .Range("A1").Value          'fixe'
        'on efface la plage des résultats
        .[C7:N21].ClearContents
        'on charge cette plage dans une variable tableau
        Result = .[C7:N21]
        'de même, on charge la plage C5:N5 de la feuille Analysis dans une variable tableau
        Mois = .[C5:N5]
        'On charge la plage A7:A21 dans la variable tableau Brand
        'On charge la plage B7:B21 dans la variable tableau Prod
        'Note. Toutes les variables tableau ont deux dimensions, 1 pour les lignes et 1 pour les colonnes.
        'Dans le cas de Brand et de Prod, on n'a pas besoin de connaître la colonne
        'puisque les plages sont mono-colonnes. On utilise "Application.Transpose"
        'pour supprimer la dimension inutile.
        'voir Figure 2 "Brand" sans utiliser "Transpose" et figure 3 "Brand créée avec "Transpose".
        Brand = Application.Transpose(.[A7:A21])
        Prod = Application.Transpose(.[B7:B21])
        '"Ubound" représente la dernière valeur de la dimension indiquée :
        'dans l'exemple, UBound(Tabl, 1) vaut 61774 (équivalent au nombre de lignes de la feuille Extract - 10)
        'UBound(Tabl, 2) vaut 11 (nombre de colonnes copiées dans "Tabl".
        'boucle dans "Tabl" (équivaut à une boucle sur la feuille - en plus rapide)
        For I = 1 To UBound(Tabl, 1)
            'i = 1 est équivalent à la ligne 11
            'Tabl(I, 2) correspond à Cells(I+10,2)
            'Match est l'équivalent de la fonction Excel EQUIV
            '"Col" représente la colonne correspondant au mois. Col=1 correspond à la colonne C
            Col = Application.Match(Tabl(I, 2), Mois, 0)
            'Ces tests correspondent à ta formule
            'Col+2 parce que, quand j'utilise les cellules la colonne C correspond à la valeur 1 de Col
            If Tabl(I, 4) = BUdata And (UCase(Tabl(I, 5)) = UCase(.Cells(6, Col + 2).Value) Or UCase(Tabl(I, 5)) = UCase(.[A4].Value)) And UCase(Tabl(I, 1)) = UCase(.[A2].Value) Then
                'si tous les tests sont OK
                'on boucle jusqu'à trouver la ligne ou la valeur de "Brand" est égale à Tabl(I, 8)
                'et la valeur de"Prod" est égale à Tabl(I, 9)
                '"Ucase" met tout en majuscules pour éviter les différences majuscules / miniscules.
                Lig = 1
                Do Until UCase(Tabl(I, 8)) = UCase(Brand(Lig)) And UCase(Tabl(I, 9)) = UCase(Prod(Lig))
                    Lig = Lig + 1
                Loop
                'on a ainsi déterminé la colonne(mois et "scénario") et la ligne("Brand" et "Prod")
                'on ajoute la valeur de la colonne k dans le tableau Result
                Result(Lig, Col) = Result(Lig, Col) + Tabl(I, 11)
            End If
        Next I
        'on recopie la variable Result sur la plage initiale.
        .[C7:N21] = Result
     End With
End Sub

Figure 1



Figure 2



Figure 3



Bien sûr, n'hésite pas à poser des questions sur ce que tu ne comprends pas.

PS. Tu as un tuto sur les variables tableau ici :

http://didier-gonard.developpez.com/...s-tableau-vba/

[Loupion]

Daniel,

Le code fonctionne tres bien.
je voudrais l utiliser sur une autre feuille sans avoir les deux criteres fixes (BU et Category), mais je ne parviens pas a adapter le code en consequence malgre les explications.
Est ce que vous pouvez me donner un petit coup de pouce encore?

Merci

[Daniel.C]

Bon je pense l'avoir fait. Dans la ligne :

Code :

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

If Tabl(i, 4) = BUdata And (UCase(Tabl(i, 5)) = UCase(.Cells(6, Col + 2).Value) Or UCase(Tabl(i, 5)) = UCase(.[A4].Value)) And UCase(Tabl(i, 1)) = UCase(.[A2].Value) Then

Code :

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

Tabl(i, 4) = BUdata

représente le test de la colonne D de la feuille Extract avec A1 (feuille Analysis). Je l'ai donc supprimé.
De même pour :

Code :

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

UCase(Tabl(i, 1)) = UCase(.[A2].Value)

qui représente le test de la colonne A de la feuille Extract avec A2 (feuille Analysis). La macro devient donc :

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
Sub TestDaniel()
     Dim Tabl As Variant, Mois As Variant, Brand As Variant, Prod As Variant
     Dim I As Long, Result As Variant, Ligne As Long
     Dim Lig As Integer, Col As Integer
     Dim BUdata As String
     With Sheets("Extract")
        '"Ligne" représente la dernière ligne des données d ela feuille Extract
        Ligne = .Cells(.Rows.Count, 1).End(xlUp).Row
        '"Tabl" est une variable tableau (ou array) contenant la plage des données de la feuille Extract
        'Voir la figure 1 dans le message
        Tabl = .Range("A11", .Cells(Ligne, 11))
     End With
     With Sheets("Analysis")
        BUdata = .Range("A1").Value          'fixe'
        'on efface la plage des résultats
        .[C7:N21].ClearContents
        'on charge cette plage dans une variable tableau
        Result = .[C7:N21]
        'de même, on charge la plage C5:N5 de la feuille Analysis dans une variable tableau
        Mois = .[C5:N5]
        'On charge la plage A7:A21 dans la variable tableau Brand
        'On charge la plage B7:B21 dans la variable tableau Prod
        'Note. Toutes les variables tableau ont deux dimensions, 1 pour les lignes et 1 pour les colonnes.
        'Dans le cas de Brand et de Prod, on n'a pas besoin de connaître la colonne
        'puisque les plages sont mono-colonnes. On utilise "Application.Transpose"
        'pour supprimer cette dimension.
        'voir Figure 2 "Brand" sans utiliser "Transpose" et figure 3 "Prod créée avec "Transpose".
        Brand = Application.Transpose(.[A7:A21])
        Prod = Application.Transpose(.[B7:B21])
        '"Ubound" représente la dernière valeur de la dimension indiquée :
        'dans l'exemple, UBound(Tabl, 1) vaut 61774 (équivalent au nombre de lignes de la feuille Extract)
        'UBound(Tabl, 2) vaut 11 (nombre de colonnes copiées dans "Tabl".
        'boucle dans "Tabl" (équivaut à une boucle sur la feuille - en plus rapide)
        For I = 1 To UBound(Tabl, 1)
            'i = 1 est équivalent à la ligne 11
            'Tabl(I, 2) correspond à Cells(I+10,2)
            'Match est l'équivalent de la fonction Excel EQUIV
            '"Col" représente la colonne correspondant au mois. Col=1 correpond à la colonne C
            Col = Application.Match(Tabl(I, 2), Mois, 0)
            'Ces tests correspondent à ta formule
            'Col+2 parce que, quand j'utilise les cellules la colonne C correspone à la valeur 1 de Col
            If (UCase(Tabl(I, 5)) = UCase(.Cells(6, Col + 2).Value) Or UCase(Tabl(I, 5)) = UCase(.[A4].Value)) Then
                'si tous les tests sont OK
                'on boucle jusqu'à trouver la ligne ou la valeur de "Brand" est égale à Tabl(I, 8)
                'et la valeur de"Prod" est égale à Tabl(I, 9)
                '"Ucase" met tout en majuscules pour éviter les différences majuscules / miniscules.
                Lig = 1
                Do Until UCase(Tabl(I, 8)) = UCase(Brand(Lig)) And UCase(Tabl(I, 9)) = UCase(Prod(Lig))
                    Lig = Lig + 1
                Loop
                'on a ainsi déterminé la colonne(mois et "scénario") et la ligne("Brand" et "Prod")
                'on ajoute la valeur de la colonne k dans le tableau Result
                Result(Lig, Col) = Result(Lig, Col) + Tabl(I, 11)
            End If
        Next I
        'on recopie la variable Result sur la plage initiale.
        .[C7:N21] = Result
     End With
End Sub

Dis-moi si c'est correct.

[Loupion]

Effectivement le code est parfait

J avais du mal a corriger et adapter a cause du

Code :

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

UCase(Tabl(i, 1)) = UCase(.[A2].Value)

cela est maintenant clair.

Merci Daniel