Discussion :

[YanAndSeb]

Bonjour à tous

Je sollicite vos conseils car la macro ci-dessous qui s'appuie sur des fonctions excel Si + SOMMEPROD imbriquées me retourne l'erreur #VALEUR! dans ma colonne résultat lorsque j'applique la méthode Application.Evaluate.

Si je n'applique Evaluate, les résultats retournés (avec la formule!) sont bons mais, compte tenu du nombre d'imbrications SI/SOMMEPROD et du nombre de lignes (10 000) à traiter, le fichier devient presque inutilisable car trop lourd.

Ci-dessous mon code . Je vous remercie par avance de votre aide

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
 
Private Sub Boucle()
 
Dim i As Long
i = Range("B2").End(xlDown).Row
 
Sheets("CDMAJORA =1").Range("C2").Formula = Application.Evaluate("=IF(SUMPRODUCT((B$2:B$» & i & «=B2)*(L$2:L$» & i & «=""CAVAMAC"")*(O$2:O$» & i & «=""R1001  "")*(Q$2:Q$» & i & «=""N"")*1)<>0,""RAF"", IF(AND(SUMPRODUCT((B$2:B$» & i & «=B2)*(L$2:L$» & i & «=""CAVAMAC"")*(J$2:J$» & i & «=""RIV"")*(O$2:O$» & i & «=""R1001   "")*(Q$2:Q$» & i & «=""A"")*1)<>0, SUMPRODUCT((B$2:B$» & i & «=B2)*(L$2:L$» & i & «<>""CAVAMAC"")*(O$2:O$» & i & «=""R1001   "")*(U$2:U$» & i & «=2))=0),""RAF"",IF(SUMPRODUCT((B$2:B$» & i & «=B2)*(AR$2:AR$» & i & «=""Nb de CIPREG erroné                                         "")*1)<>0,""RAF"",IF(SUMPRODUCT((B$2:B$» & i & «=B2)*(L$2:L$» & i & «=""CAVAMAC"")*(O$2:O$» & i & «=""R1001   "")*((J$2:J$» & i & «=""ACC"")+(J$2:J$» & i & «=""""))*1)<>0, ""RAF"", IF(SUMPRODUCT((B$2:B$» & i & «=B2)*(L$2:L$» & i & «=""CAVAMAC"")*(M$2:M$» & i & «<>1110000000)*(M$2:M$» & i & «<>1100000000))<>0,""RAF"","""")))))")
 
Sheets("CDMAJORA =1").Range("C2").AutoFill Destination:=Sheets("CDMAJORA =1").Range("C$2:C" & i & "")
 
End sub

[Menhir]

Il n'y a pas de "=" au début d'une formule dans un Evaluate.
Lire ceci : https://docs.microsoft.com/fr-fr/off...ation.evaluate

Si ton but est de mettre dans la cellule une formule et non une valeur (sinon le AutoFill qui suit n'aurait aucun sens), alors il ne faut pas utiliser d'Evaluate mais d'assigner directement la chaine de caractère de caractère à Formula.

[YanAndSeb]

Bonjour Menhir,

Merci pour ton retour.

Mon but est de renvoyer dans Excel uniquement le résultat de la formule, et non la formule qui est trop lourde compte tenu du nombre de lignes à traiter.

Maintenant, tu a raison. Je ne peux plus utiliser Autofill avec Evaluate (c'était ma 1ere intention lorsque j'ai souhaité le formule, ce qui marchait alors).

Maintenant, je ne sais pas encore comment faire ... Soit ne pas utiliser autofill, soit Evaluate?
Une petite idée ?

[Menhir]

Classiquement, tu peux utiliser une boucle For To qui calculera (avec Evaluate) la valeur pour les paramètres de chaque ligne et mettra le résultat dans la cellule correspondante de la colonne C (en utilisant Value et non Formula).

Mais il y a plus simple et plus rapide.

Code :

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

1
2
3
4
5
6
7
Private Sub Boucle()
   Dim Fin As Long
   Fin = Cells(Rows.Count, "B").End(xlUp).Row
   Sheets("CDMAJORA =1").Range("C2:C" & Fin).Formula = "=IF(SUMPRODUCT((B$2:B$» & i & «=B2)*(L$2:L$» & i & «=""CAVAMAC"")*(O$2:O$» & i & «=""R1001  "")*(Q$2:Q$» & i & «=""N"")*1)<>0,""RAF"", IF(AND(SUMPRODUCT((B$2:B$» & i & «=B2)*(L$2:L$» & i & «=""CAVAMAC"")*(J$2:J$» & i & «=""RIV"")*(O$2:O$» & i & «=""R1001   "")*(Q$2:Q$» & i & «=""A"")*1)<>0, SUMPRODUCT((B$2:B$» & i & «=B2)*(L$2:L$» & i & «<>""CAVAMAC"")*(O$2:O$» & i & «=""R1001   "")*(U$2:U$» & i & «=2))=0),""RAF"",IF(SUMPRODUCT((B$2:B$» & i & «=B2)*(AR$2:AR$» & i & «=""Nb de CIPREG erroné                                         "")*1)<>0,""RAF"",IF(SUMPRODUCT((B$2:B$» & i & «=B2)*(L$2:L$» & i & «=""CAVAMAC"")*(O$2:O$» & i & «=""R1001   "")*((J$2:J$» & i & «=""ACC"")+(J$2:J$» & i & «=""""))*1)<>0, ""RAF"", IF(SUMPRODUCT((B$2:B$» & i & «=B2)*(L$2:L$» & i & «=""CAVAMAC"")*(M$2:M$» & i & «<>1110000000)*(M$2:M$» & i & «<>1100000000))<>0,""RAF"","""")))))"
   Sheets("CDMAJORA =1").Range("C2:C" & Fin).Copy
   Sheets("CDMAJORA =1").Range("C2:C" & Fin).PasteSpecial Paste:=xlPasteValues
End sub

[YanAndSeb]

Si je comprends bien, une 1ere commande (formula=...) va d'abord renvoyer dans la formule dans la cellule excel puis 2 autres commandes (copy + pastspecial) vont remplacer dans chaque cellule la formule par la valeur résultat.

Ca va "exploser" je pense car formule SI/SOMMPROD trop lourde et + de 10 000 lignes à traiter....

[Menhir]

Si je comprends bien, une 1ere commande (formula=...) va d'abord renvoyer dans la formule dans la cellule excel puis 2 autres commandes (copy + pastspecial) vont remplacer dans chaque cellule la formule par la valeur résultat.

C'est ça.

Je viens de m'apercevoir que ta formule contient des guillemets bizarres sans doute dus à un remplacement automatique.
A cause de ça, je ne me suis pas aperçu que tu avais inclus dedans ta variable i et comme j'ai changé son nom dans mon code, il faudra rectifier.

Ca va "exploser" je pense car formule SI/SOMMPROD trop lourde et + de 10 000 lignes à traiter....

De toute façon, quelle que soit la méthode, tu seras obligé de calculer la formule pour chaque ligne.

Donc, si c'est trop lourd à calculer pour ton poste, il faudra soit passer par des colonnes intermédiaires pour diluer la formule en plusieurs formules plus petites, soit traduire cette formule en code VBA.
Mais dans cette seconde hypothèse, il faudra passer par une (ou plusieurs) boucles For To, ce qui, sur 100 000 lignes, risque d'entrainer un temps d'exécution assez lourd.