XL 2013 Formule très longue en automatique par vba

maintroto

XLDnaute Nouveau
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:

VB:
=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)))
 

maintroto

XLDnaute Nouveau
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

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

job75

XLDnaute Barbatruc
Ce code permet d'entrer la formule dans une cellule :
VB:
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
 

Créez un compte ou connectez vous pour répondre

Vous devez être membre afin de pouvoir répondre ici

Créer un compte

Créez un compte Excel Downloads. C'est simple!

Connexion

Vous avez déjà un compte? Connectez vous ici.

Haut Bas