Erreur #VALEUR! avec la méthode EVALUATE + IF/SUMPRODUCT

Version imprimable

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:

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

23/04/2020, 11h38
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.
23/04/2020, 15h09
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 ?:)

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:

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

23/04/2020, 16h56
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....
23/04/2020, 17h33
Menhir

Citation:

Envoyé par 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.

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.

Citation:

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.