XL 2013 Formule très longue en automatique par vba

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

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)))
 
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....
 
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
 
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

Réponses
5
Affichages
474
Réponses
1
Affichages
1 K
H
Réponses
19
Affichages
6 K
Retour