XL 2013 Formule très longue en automatique par vba

[maintroto]

Bonjour à tous
J'ai une formule à passer en automatique , donc je me tourne vers le vba, ik semble que ma formule soit trop longue, des lignes apparaissent en rouges systématiquement quand je vais voir la formule dans vba, pourtant cette formule fonctionne dans excel.
Y a t'il une taille limite pour les formules, ou est ce une question de syntaxe?
Merci de votre aide
Nico
ci dessous la formule qui fonctionne dans excel:

=SOMMEPROD((($U$2:$U$500="C215")*($D2:$D$500="34DM")+($D$2:$D$500="34GM")+($D$2:$D$500="34FM")+($D$2:$D$500="34RM")+($D$2:$D$500="34SM")+($D$2:$D$500="34PM")+($D$2:$D$500="34CM")+($D$2:$D$500="34LM")+($D$2:$D$500="37M")+($D$2:$D$500="38PM")+($D$2:$D$500="36M")+($D$2:$D$500="36BM")+($D$2:$D$500="36AM")+($D$2:$D$500="36CM"))*($Z$2:$Z$500))+SOMMEPROD((($U$2:$U$500="C215")*(($E$2:$E$500="31BM")+($E$2:$E$500="34DM")+($E$2:$E$500="34GM")+($E$2:$E$500="34FM")+($E$2:$E$500="34RM")+($E$2:$E$500="34SM")+($E$2:$E$500="34PM")+($E$2:$E$500="34CM")+($E$2:$E$500="34LM")+($E$2:$E$500="37M")+($E$2:$E$500="38PM")+($E$2:$E$500="36M")+($E$2:$E$500="36BM")+($E$2:$E$500="36AM")+($E$2:$E$500="36CM"))*($AA$2:$AA$500)))+SOMMEPROD((($U$2:$U$500="C215")*(($F$2:$F$500="31BM")+($F$2:$F$500="34DM")+($F$2:$F$500="34GM")+($F$2:$F$500="34FM")+($F$2:$F$500="34RM")+($F$2:$F$500="34SM")+($F$2:$F$500="34PM")+($F$2:$F$500="34CM")+($F$2:$F$500="34LM")+($F$2:$F$500="37M")+($F$2:$F$500="38PM")+($F$2:$F$500="36M")+($F$2:$F$500="36BM")+($F$2:$F$500="36AM")+($F$2:$F$500="36CM"))*($AB$2:$AB$500)))+=SOMMEPROD((($U$2:$U$500="C215")*(($G$2:$G$500="34DM")+($G$2:$G$500="34GM")+($G$2:$G$500="34FM")+($G$2:$G$500="34RM")+($G$2:$G$500="34SM")+($G$2:$G$500="34PM")+($G$2:$G$500="34CM")+($G$2:$G$500="34LM")+($G$2:$G$500="37M")+($G$2:$G$500="38M")+($G$2:$G$500="36M")+($G$2:$G$500="36BM")+($G$2:$G$500="36AM")+($G$2:$G$500="36CM"))*($AC$2:$AC$500)))+=SOMMEPROD((($U$2:$U$500="C215")*(($D$2:$D$500="34D")+($D$2:$D$500="34G")+($D$2:$D$500="34F")+($D$2:$D$500="34R")+($D$2:$D$500="34S")+($D$2:$D$500="34P")+($D$2:$D$500="34C")+($D$2:$D$500="34L")+($D$2:$D$500="37")+($D$2:$D$500="38")+($D$2:$D$500="36")+($D$2:$D$500="36B")+($D$2:$D$500="36A")+($D$2:$D$500="36C"))*($Z$2:$Z$500)))+=SOMMEPROD((($U$2:$U$500="C215")*(($G$2:$G$500="34D")+($G$2:$G$500="34G")+($G$2:$G$500="34F")+($G$2:$G$500="34R")+($G$2:$G$500="34S")+($G$2:$G$500="34P")+($G$2:$G$500="34C")+($G$2:$G$500="34L")+($G$2:$G$500="37")+($G$2:$G$500="38")+($G$2:$G$500="36")+($G$2:$G$500="36B")+($G$2:$G$500="36A")+($G$2:$G$500="36C"))*($AC$2:$AC$500)))+=SOMMEPROD((($U$2:$U$500="C215")*(($F$2:$F$500="34D")+($F$2:$F$500="34G")+($F$2:$F$500="34F")+($F$2:$F$500="34R")+($F$2:$F$500="34S")+($F$2:$F$500="34P")+($F$2:$F$500="34C")+($F$2:$F$500="34L")+($F$2:$F$500="37")+($F$2:$F$500="38")+($F$2:$F$500="36")+($F$2:$F$500="36B")+($F$2:$F$500="36A")+($F$2:$F$500="36C"))*($AB$2:$AB$500)))+=SOMMEPROD((($U$2:$U$500="C215")*(($E$2:$E$500="34D")+($E$2:$E$500="34G")+($E$2:$E$500="34F")+($E$2:$E$500="34R")+($E$2:$E$500="34S")+($E$2:$E$500="34P")+($E$2:$E$500="34C")+($E$2:$E$500="34L")+($E$2:$E$500="37")+($E$2:$E$500="38")+($E$2:$E$500="36")+($E$2:$E$500="36B")+($E$2:$E$500="36A")+($E$2:$E$500="36C"))*($AA$2:$AA$500)))

 

[Calvus]

Bonjour,

On n'écrit pas les formules en vba de la même façon que dans Excel.

A+

 

[maintroto]

Bonjour,
oui je sais, mais ne maîtrisant pas bien le vba j'utilise l'enregistreur automatique pour passer de ma formule excel vers le vba. j enregistre une macro et je copie/colle ma formule dans une cellule.
C'est peut être d'ailleurs ca la problème....

 

[job75]

Bonjour maintroto, Calvus,

https://docs.microsoft.com/fr-fr/office/vba/language/reference/user-interface-help/line-too-long

A+

 

[Calvus]

Re,

Et bien sans fichier.....ben sans fichier... 😉

A+

 

[job75]

Par ailleurs la formule n'est pas correcte, à l'intérieur il y a "+=SOMMEPROD", je suppose qu'il faut enlever le signe =

 

[job75]

Ce code permet d'entrer la formule dans une cellule :

Sub Test()
Dim f$
f = "=SUMPRODUCT((($U$2:$U$500=""C215"")*($D2:$D$500=""34DM"")+($D$2:$D$500=""34GM"")+($D$2:$D$500=""34FM"")+($D$2:$D$500=""34RM"")+($D$2:$D$500=""34SM"")+($D$2:$D$500=""34PM"")+($D$2:$D$500=""34CM"")+($D$2:$D$500=""34LM"")+($D$2:$D$500=""37M"")+($D$2:$D$500=""38PM"")+($D$2:$D$500=""36M"")+($D$2:$D$500=""36BM"")+($D$2:$D$500=""36AM"")+($D$2:$D$500=""36CM""))*($Z$2:$Z$500))+SUMPRODUCT((($U$2:$U$500=""C215"")*(($E$2:$E$500=""31BM"")+($E$2:$E$500=""34DM"")+($E$2:$E$500=""34GM"")+($E$2:$E$500=""34FM"")+($E$2:$E$500=""34RM"")+($E$2:$E$500=""34SM"")+($E$2:$E$500=""34PM"")+($E$2:$E$500=""34CM"")+($E$2:$E$500=""34LM"")+($E$2:$E$500=""37M"")+($E$2:$E$500=""38PM"")+($E$2:$E$500=""36M"")+($E$2:$E$500=""36BM"")+($E$2:$E$500=""36AM"")+($E$2:$E$500=""36CM""))*($AA$2:$AA$500)))" & _
    "+SUMPRODUCT((($U$2:$U$500=""C215"")*(($F$2:$F$500=""31BM"")+($F$2:$F$500=""34DM"")+($F$2:$F$500=""34GM"")+($F$2:$F$500=""34FM"")+($F$2:$F$500=""34RM"")+($F$2:$F$500=""34SM"")+($F$2:$F$500=""34PM"")+($F$2:$F$500=""34CM"")+($F$2:$F$500=""34LM"")+($F$2:$F$500=""37M"")+($F$2:$F$500=""38PM"")+($F$2:$F$500=""36M"")+($F$2:$F$500=""36BM"")+($F$2:$F$500=""36AM"")+($F$2:$F$500=""36CM""))*($AB$2:$AB$500)))+SUMPRODUCT((($U$2:$U$500=""C215"")*(($G$2:$G$500=""34DM"")+($G$2:$G$500=""34GM"")+($G$2:$G$500=""34FM"")+($G$2:$G$500=""34RM"")+($G$2:$G$500=""34SM"")+($G$2:$G$500=""34PM"")+($G$2:$G$500=""34CM"")+($G$2:$G$500=""34LM"")+($G$2:$G$500=""37M"")+($G$2:$G$500=""38M"")+($G$2:$G$500=""36M"")+($G$2:$G$500=""36BM"")+($G$2:$G$500=""36AM"")+($G$2:$G$500=""36CM""))*($AC$2:$AC$500)))" & _
    "+SUMPRODUCT((($U$2:$U$500=""C215"")*(($D$2:$D$500=""34D"")+($D$2:$D$500=""34G"")+($D$2:$D$500=""34F"")+($D$2:$D$500=""34R"")+($D$2:$D$500=""34S"")+($D$2:$D$500=""34P"")+($D$2:$D$500=""34C"")+($D$2:$D$500=""34L"")+($D$2:$D$500=""37"")+($D$2:$D$500=""38"")+($D$2:$D$500=""36"")+($D$2:$D$500=""36B"")+($D$2:$D$500=""36A"")+($D$2:$D$500=""36C""))*($Z$2:$Z$500)))+SUMPRODUCT((($U$2:$U$500=""C215"")*(($G$2:$G$500=""34D"")+($G$2:$G$500=""34G"")+($G$2:$G$500=""34F"")+($G$2:$G$500=""34R"")+($G$2:$G$500=""34S"")+($G$2:$G$500=""34P"")+($G$2:$G$500=""34C"")+($G$2:$G$500=""34L"")+($G$2:$G$500=""37"")+($G$2:$G$500=""38"")+($G$2:$G$500=""36"")+($G$2:$G$500=""36B"")+($G$2:$G$500=""36A"")+($G$2:$G$500=""36C""))*($AC$2:$AC$500)))" & _
    "+SUMPRODUCT((($U$2:$U$500=""C215"")*(($F$2:$F$500=""34D"")+($F$2:$F$500=""34G"")+($F$2:$F$500=""34F"")+($F$2:$F$500=""34R"")+($F$2:$F$500=""34S"")+($F$2:$F$500=""34P"")+($F$2:$F$500=""34C"")+($F$2:$F$500=""34L"")+($F$2:$F$500=""37"")+($F$2:$F$500=""38"")+($F$2:$F$500=""36"")+($F$2:$F$500=""36B"")+($F$2:$F$500=""36A"")+($F$2:$F$500=""36C""))*($AB$2:$AB$500)))+SUMPRODUCT((($U$2:$U$500=""C215"")*(($E$2:$E$500=""34D"")+($E$2:$E$500=""34G"")+($E$2:$E$500=""34F"")+($E$2:$E$500=""34R"")+($E$2:$E$500=""34S"")+($E$2:$E$500=""34P"")+($E$2:$E$500=""34C"")+($E$2:$E$500=""34L"")+($E$2:$E$500=""37"")+($E$2:$E$500=""38"")+($E$2:$E$500=""36"")+($E$2:$E$500=""36B"")+($E$2:$E$500=""36A"")+($E$2:$E$500=""36C""))*($AA$2:$AA$500)))"
[A1] = f 'pour tester
End Sub

 

[maintroto]

merci pour l'astuce, ca fonctionne parfaitement.
Bonne fin de journée