Transcrire formule Excel en VBA

bonjour le forum

j'ai trouvé sur le forum un code pour transcrire une formule excel en code vba car mes sommeprod sont trés long au calcul

je l'ai donc recopié et rentré la formule là ou il le disait mais elle ne fonctionne pas et n'ayant jamais fais de vba je ne sais pas ou est l'erreur

si quequ'un peut m'aider merci d'avance

merci

fichier joint

Bonsoir,

Essaie cela :

Option Explicit

Sub sommeprod()
   Dim rCellule As Range, lVal As Long
    lVal = 0
   For Each rCellule In Range("Y2:Y" & Range("Kdonnées").Rows.Count + Range("Kdonnées").Row - 1)
       lVal = lVal + 1
      rCellule.FormulaArray = "=SUMPRODUCT(FREQUENCY(IF(Kdonnées=1,ROW(KcolC)),ROW(Kdonnées)),FREQUENCY(IF(Kdonnées=" & lVal & ",ROW(KcolC)),ROW(Kdonnées)))"
   Next rCellule
End Sub

Autofil ne fonctionne pas avec les matricielles. De plus les formules matricielles ne peuvent s'écrire qu'en anglais.

Et n'oublie pas supprimer le code qui se trouve dans Feuil1.

A+

Benead

bonjour Benead, le forum

merci pour ta réponse mais j'ai un probleme.

le code fonctionne bien sur le bout de fichier que j'ai joint mais lorsque je l'adapte a ma feuille qui comprend 8900lignes la il est beaucoup,beaucoup plus long que les SOMMEPROD,de plus les caculs vont de la ligne Y2:Y71 et la les calculs continuent jusqu'a la ligne 8900.j'ai remis le fichier avec quelques lignes supplementaires et on voit que les cellules en dessous de la ligne Y71 contiennent des 0 jusqu'a la fin des données,j'ai esayé de modifier les valeurs("Y2:Y") en ("Y2:Y71") mais c'est pareil

Que faudrait-il rajouter au code.

merci

Bonjour,

Pour que la macro s'exécute plus rapidement il faut mettre en début de code :

   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual

et en fin de code :

   Application.ScreenUpdating = True
   Application.Calculation = xlCalculationAutomatic

J'ai simplement automatisé la saisie de la formule.

Concernant la formule même, je ne suis pas un pro et ne la comprends pas. De plus, je ne vois pas pourquoi cela fonctionne jusqu'à la ligne 71 et pas après. Par contre je ne vois aucune différence de plage entre Kdonnées et KcolC, elles font toutes les deux références à =DECALER(Feuil1!$C$2;;;NBVAL(Feuil1!$C:$C);20). Je ne suis pas sûr que le résultat affiché corresponde au résultat attendu .

Si tu as des problème de formules, il doit y avoir des pros sur ce forum, il faut leur expliquer ce que doit faire ta formule.

A+

Benead

Bonjour,

Ouaip ! Je serai aussi très curieux de savoir ce que calcule cette formule bizarre

Après on pourra peut-être essayer de mettre ça en VBA.

... mais c'est clair que Kdonnées et KcolC c'est bonnet blanc et blanc bonnet

A+

bonjour galopin,Benead,le forum

a l'origine la formule en Excel est celle-ci

=SOMMEPROD(FREQUENCE(SI(Kdonnées=1;LIGNE(KcolC));LIGNE(Kdonnées));FREQUENCE(SI(Kdonnées=1;LIGNE(KcolC));LIGNE(Kdonnées)))

j'ai voulu la transformer en code vba par un post que j'ai lu sur le forum de "vba-new" qui disait

• lancer l'enregistreur de macro
• aller en Y2 et coller la formule
• valider avec entrée
• arréter l'enregistreur de macro

Cette formule sert a calculer combien de fois un numéro est sorti avec un autre,ici le 1(Z1)

kdonnées est pour délimiter ma plage de données,kColC sert aussi pour la premiere formule

la formule excel a l'origine

=SOMMEPROD(FREQUENCE(SI($C$2:$V$9000=1;LIGNE($C$2:$C$9000));LIGNE($2:$9000));FREQUENCE(SI($C$2:$V$9000=2;LIGNE($C$2:$C$9000));LIGNE($2:$9000)))

tous les jour ma plage de données s'agrandit, je l'est donc renommée"kdonnées,kColC"

j'ai voulu transformer en vba car les formules SOMMEPROD sont trés longues en temps de calcul et un forumiste m'a dit qu'il fallait transformer en vba,mais comme en code je n'y connais rien ,j'ai essayé par des informations glanées sur les forums.

merci pour votre aide

Je savais que j'étais loin d'être un formuleux, mais là je suis vraiment bluffé !

Bonjour jad73,

Je n'y comprends toujours rien dans ta formule, mais là n'est pas le problème. Je reprend le classeur exemple et met cette formule :

=SOMMEPROD(FREQUENCE(SI($C$2:$V$9000=1;LIGNE($C$2:$C$9000));LIGNE($2:$9000));FREQUENCE(SI($C$2:$V$9000=LIGNE()-1;LIGNE($C$2:$C$9000));LIGNE($2:$9000)))

Cela veut dire que ta formule n'est pas correcte. Il faut donc que tu commences par trouver a bonne formule avant que l'on puisse automatiser quoi que ce soit.

A+

Benead

Salut le forum

Tu as des zéros, car les nombres 71 et plus n'existe pas

=SOMMEPROD(FREQUENCE(SI(Kdonnées=1;LIGNE(KcolC));LIGNE(Kdonnées));FREQUENCE(SI(Kdonnées=71;LIGNE(KcolC));LIGNE(Kdonnées)))

Mytå

bonjour Myta,Benead,le forum

bien sur que le 71 et plus n'existe pas, les calculs vont de 1 a 70,c'est justement les zéros qui ne devraient pas apparaitre.

Benead la formule a l'origine fonctionne trés bien,c'est celle transcrite en code vba qui ne fonctionne pas.

Je l'ai mise sur un forum vba mais je n'ai pas encore reçu de réponse

merci

Bonsoir,

Qu'est-ce que tu entends par :

mes sommeprod sont trés long au calcul

10 secondes ? 30 secondes ? 10 minutes ?

A+

Re le forum

Si c'est juste pour arreter la formule à la ligne 71.

Sub sortie()
   Dim rCellule As Range, lVal As Long
    lVal = 0
   For Each rCellule In Range("Y2:Y71")
       lVal = lVal + 1
      rCellule.FormulaArray = "=SUMPRODUCT(FREQUENCY(IF(Kdonnées=1,ROW(KcolC)),ROW(Kdonnées)),FREQUENCY(IF(Kdonnées=" & lVal & ",ROW(KcolC)),ROW(Kdonnées)))"
   Next rCellule
End Sub

Mytå

Bonsoir,

J'ai testé le truc sur 8900 lignes avec la dernière formule de Myta et en tout VBA (c'est à dire sans aucun SOMMEPROD)

C'est quand même plus rapide en "pur VBA" environ 33 secondse chez moi contre 86 secondes avec le code de myta,

Je vous mets donc le fichier démo :

C'est un big fichier puisque j'ai testé avec 8900 lignes et c'est un .xlsm :

Si vous avez IE 9 vous pouvez le télécharger directement sinon vous devez le renommer avec l'extension .xlsm. En aucun cas chercher à le dézipper !

A+

Hum...

Par contre j'ai re-testé avec des SOMMEPROD (sur la base de la formule Myta à peine modifiée) mais sans aucune macro... ça me donne 3 secondes au recalcul !

Du coup je ne comprend plus l'objet de ce topic.

La formule pour Y2 (Formule matricielle à valider par Ctrl + Maj + Entr) :

=SOMMEPROD(FREQUENCE(SI(Kdonnées=Z$1;LIGNE(KcolC));LIGNE(Kdonnées));FREQUENCE(SI(Kdonnées=X2;LIGNE(KcolC));LIGNE(Kdonnées)))

....ensuite on incrémente la formule jusqu'en Y71

Et YAPUKA modifier Z1 pour modifier les paires !

A+

bonjour galopin,myta,le forum

pour ce qui est du temps le recalcul de ma feuille ce fait en 6 a 7 minutes,j'ai oublié de préciser que le calcul X2:Y71 se répete 70 fois dans ma feuille c'est a dire que j'ai 490 cellules comportant la formule SOMMEPROD et puis ça peut venir je pense aussi de mon processeur c'est dual-core E7400,et aussi que dans cette meme feuille j'ai encore d'autre calculs avec d'autres formules,je vais séparer tous ça.

Je vais tester les différentes formules et je vous tiendrais au courant.

merci

Quand tu nous aura tout dit !

Dans ces conditions passer par une macro pour réinstaller un SOMMEPROD ne sert à rien autant laisser les SOMMEPROD en place mais, au lieu de rester en recalcul automatique tu bloques le recalcul automatique et tu ne recalcules qu'une seule fois quand tous les changements ont été effctués. (Pour lancer le recalcul, pas besoin de macro : tu appuies sur la touche [F9]

ça n'accélèrera pas le traitement mais au moins ça n'arrivera qu'une seule fois !

Une autre stratégie -probablement la meilleure- est de geler tes SOMMEPROD pour les 8900 premières lignes avec un Copier/CollageSpécial Valeur Les statistiques pour ces 8900 premières lignes ne bougeront plus quoi qu'il arrive n'est-ce pas ?

Ensuite tu fais ton SOMMEPROD traditionnel sur les quelques lignes restantes et YAPUKA faire une addition avec les SOMMEPROD gelés. Je te fiche mon billet que ton vieux PC y va retrouver une seconde jeunesse d'un seul coup !

A+

Bonsoir jad73,

Je n'y comprends plus rien : Je pensais que la variable était le numéro de ligne et qu'il fallait aller incrémenter la formule jusqu'à la dernière ligne.

Comme quoi, à question "Très" imprécise, réponse "complètement" à coté de la plaque.

A mon avis aucun intérêt de passer en VBA.

Chez moi, le changement de valeur en Z1 recalcule les formule en environ 4 secondes.

Tu peux essayer de laisser en calcul manuel, mais pour recalculer, au lieu de faire F9, fais plutôt Shift+F9, cela ne recalcule que la feuille active.

Par contre, Galopin01, j'ai essayé ton code (Core Duo P7450 avec 4go de mémoire et Seven 64) : 20 minutes, je me demande bien quel type d'ordinateur te permets d'aller aussi vite.

A+

Benead

Re le forum

Benead, tu dois avoir des problèmes

61 secondes sur un Portable Toshiba (Satellite)

Windows Vista SP2 / Excel 2007

AMD Athlon X2 Dual-Core QL-60 1,90 GHz / 3 Go de Ram

Mytå

Bonjour,

Mytå a écrit :

Benead, tu dois avoir des problèmes

Je confirme

Portable SAMSUNG R530

W7 x64 SP1

Intel Core i3 CPU M380 @ GHz 2.53, 2 coeurs 4 proc...

RAM 4 Go

Mais bien sur la conclusion reste la même : aucun intéret d'utiliser VBA

A+

Re...

J'ai testé ma deuxième suggestion : Comme notre ami j'ai précalculé puis figées les 70 possibiltés de couples pour les 8900 premières lignes.

Ensuite un sommeprod prend le relais et rajoute les quelques lignes supplémentaires. Bien sur le recalcul est instantané.

Bien sur ça ne marche qu'en cas d'ajout (pour une loterie par exemple) Si les enregistrements antérieures viennent à être modifié ça ne peut pas fonctionner. Mais la méthode mérite d'être signalée.

Pour de nombreux calculs statistiques ou on reprend les données des années antérieures, inutile de recalculer en permanence des données qui ne changeront plus : YAKA figer les données des antérieures puis calculer/rajouter les données les plus récentes. Le gain de temps est considérable !

A+