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,
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 …
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 :
ou
Code : Sélectionner tout - Visualiser dans une fenêtre à part Result(Lig, Col) = Result(Lig, Col) + Tabl(I, 11)
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 ?
Code : Sélectionner tout - Visualiser dans une fenêtre à part Lig = Lig + 1
Effectivement, ce n'est pas commun !
Je suppose tu as pensé à vérifier la valeur des variables …
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.
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.
Bonne nouvelle; ça ne plante plus. Comme quoi, un bon reboot, des fois... Reste plus qu'à obtenir un résultat correct.
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 …
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 :
contrairement à la syntaxe "WorkSheetFunction.Match" qu'il vaut donc mieux éviter.
Code : Sélectionner tout - Visualiser dans une fenêtre à part On Error Resume Next
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 …
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".
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 …
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
Pas toujours selon le type de cellules recherché.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 …
C'est donc que tu l'utilises en dernier recoursSinon avec On Error j'externalise dans une fonction ou procédure autonome
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 :
Par contre, je ne m'explique pas les 2000 en C7. Comment les calculer ?
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
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
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".
Figure 1
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 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/
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
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) Thenreprésente le test de la colonne D de la feuille Extract avec A1 (feuille Analysis). Je l'ai donc supprimé.
Code : Sélectionner tout - Visualiser dans une fenêtre à part Tabl(i, 4) = BUdata
De même pour :
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 UCase(Tabl(i, 1)) = UCase(.[A2].Value)
Dis-moi si c'est correct.
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
Effectivement le code est parfait
J avais du mal a corriger et adapter a cause ducela est maintenant clair.
Code : Sélectionner tout - Visualiser dans une fenêtre à part UCase(Tabl(i, 1)) = UCase(.[A2].Value)
Merci Daniel
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager