rempacer sommeprod par un code VBA

[CHRISTOPHEAGPM]

Bonjour à tous,

J'essaie de diminuer et la taille de mon classeur et la vitesse d'execution des calculs.
Le nombre de cellules comprenant une formule sommeprod() est énorme (11880 exactement).
J'aimerai savoir si en remplacant les formules par un code VBA cela améliorerai la vitesse de calcul et si possible avoir une idée de la manière de proceder.

En Pj un classeur amaigri de plus de 39000 Ko.

Je souhaiterai si possible que cette macro remplace pour chaque cellule la formule (exemple pour la formule de la cellule F7 :

=SOMMEPROD((récapitulatif!$G$10:$G$1099=Unités!F$3)*(récapitulatif!$I$10:$I$1099=Unités!$C7)*(récapitulatif!$H$10:$H$1099=Unités!$B$4))

Si l'un d'entre vous me me donner une piste, j'essaierai ensuite de l'adapter aux autres mois, autres tableaux et autres onglets.

Merci d'avance et bon week-end.

Christophe.

 

[gilbert_RGI]

Re : rempacer sommeprod par un code VBA

bonsoir

en VBA

ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((récapitulatif!R10C7:R33C7=U!R3C)*(récapitulatif!R10C9:R33C9=U!RC3)*(récapitulatif!R10C8:R33C8=U!R4C2))"

 

[CHRISTOPHEAGPM]

Re : rempacer sommeprod par un code VBA

Merci Gilbert,

ça me semble ouvrir des perspectives particulièrement interressantes pour mon projet. Reste à comprendre le fonctionnement, rajouter quelques boucles, determiner les limites... du boulot en perspective mais ça avance, et certainement un peu plus vite grace à vous.

Encore merci.

Christophe.

 

[eriiic]

Re : rempacer sommeprod par un code VBA

Bonsoir,

Ce que t'a donné Gilbert c'est comment réinscrire tes formules dans les cellules.
Vu qu'elles seront toujours présentes tu n'auras pas de gain de temps.
Il faudrait que tu mettes le résultat.
Pour ça tu as evaluate("ta_formule_en_anglais") ou bien
application.worksheetfunction.ta_fonction_en_anglais(ses paramètres)

Ex avec worksheetfunction :
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
dim v as double
Set sh1 = Worksheets("récapitulatif")
Set sh2 = Worksheets("Unités")
Set sh3 = Worksheets("U")
v = Application.WorksheetFunction.SumProduct((sh1.[$G$10:$G$1099] = sh2.[F$3]) * (sh1.[$I$10:$I$1099] = sh2.[$C7]) * (sh1.[$H$10:$H$1099] = sh2.[$B$4]))
sh3.cells(7,15)=v

Bien sûr il faudrait que tu fasses des boucles et que tu passes en paramètre les références mais vu qu'on a que des #REF dans ton fichier difficile d'être plus concret.
eric

 

[CHRISTOPHEAGPM]

Re : rempacer sommeprod par un code VBA

Hello Eriiiic,

Je venais de me rendre compte des limite de la proposition de gilbert_RGI (qui m'a fait plancher sur formular1c1 ce qui est déja une bonne chose )
Je tente de comprendre ta proposition et si tu le permets je reviendrai vers toi si je bloque (et il y'a des chance que ça arrive...)

Merci beaucoup.

 

[CHRISTOPHEAGPM]

Re : rempacer sommeprod par un code VBA

Je poste un fichier sans #REF, et demande un peu d'aide.
En tenant compte du message d'Eriiiic, je comprend le principe mais ne parviens pas à l'appliquer;
J'aimerai comprendre comment délimiter la plage sur laquelle s'applique la formule (e7:h55) dans le fichier en Pj.
et surtout comment integrer les variables à la proposition d'Eriiiic pour remplir les 220 cellules uniquement lorsqu'il y'a un resultat.
merci d'avance pour votre aide.

Christophe.

 

[Bebere]

Re : rempacer sommeprod par un code VBA

bonjour Christophe,Eric,Gilbert
code dans module1
1 procédure pour créer ou mettre à jour les noms définis
et 1 autre pour les formules
à bientôt

 

[kjin]

Re : rempacer sommeprod par un code VBA

Bonjour,
Remplacer la formule par du vba pour augmenter la vitesse est illusoire.
VBA permet éventuellement de lancer le recalcul à la demande et si c'est envisageable, de gérer les événements dans la feuille qui déclencheront le recalcul avec des critères de filtrage
Rien ne t'empêche également de mettre le calcul en mode manuel et de recalculer la feuille en appuyant sur F9
A+
kjin

 

[CHRISTOPHEAGPM]

Re : rempacer sommeprod par un code VBA

Bonjour à tous,

Bebere, c'est "bluffant", je retrouve le sourire et vais de ce pas essayer de l'appliquer à mon projet.

Kjin, je ne peux qu'etre d'accord avec toi (vu mon niveau...) mais je pense néanmoins que la taille du fichier devrait s'en trouver considérablement réduite non ?

Merci beaucoup à vous quatre, l'efficacité de cette communauté m'impressionne encore une fois.

Christophe.

 

[CHRISTOPHEAGPM]

Re : rempacer sommeprod par un code VBA

(Re)bonjour,

J'ai saisie les boucles, les limites de mes tableaux mais je rencontre un "petit" problème, je crois qu'il vient de la variable E

E as double

Tant que je m'entrainais sur le fichier de Bebere pas de pb, mais une fois transposé à mon fichier...

Dans mon projet les titres de la ligne 4 de la feuille "U" ne sont pas des chiffres (comme sur la 1ere Pj.) mais des actions (cf nouvelle Pj.)

L'un d'entre vous peut t'il m'orienter vers une piste pour résoudre cette nouvelle difficulté.

En vous remerciant par avance.

Christophe.

 

[Bebere]

Re : rempacer sommeprod par un code VBA

Christophe
changement effectué et ajout de screenupdating
à bientôt

 

[eriiic]

Re : rempacer sommeprod par un code VBA

Bonjour tout le monde,

Je ne t'avais pas abandonné mais j'étais au boulot ;-)
Merci a bebere d'avoir pris le relais
eric

 

[Bebere]

Re : rempacer sommeprod par un code VBA

bonjour Eric
tandis que moi peinard,comme un pantouflard
j'ai eu le temps d'écrire ma prose
à bientôt

 

[CHRISTOPHEAGPM]

Re : rempacer sommeprod par un code VBA

Et quelle prose,

J'ai parfois l'impression d'etre un successeur de Champolion quand je la découvre, mais quel plaisir de voir ensuite les chiffres apparaitrent sous mes yeux. A bientôt et encore une fois un grand merci.

Christophe.