Ajout automatique d'éléments dans une somme en VBA

[phirexian]

Bonsoir à tous,

J'ai une problématique fort peu trivial.

Imaginer que vous ayez une colonne de valeurs sous forme de tableau sur une feuille d'un classeur Excel.

Vous voulez insérer dans une cellule de cette même feuille la somme des valeurs dudit tableau.

Je sais qu'il y a plusieurs solutions, en tout cas j'en connais deux, il me faut la troisième ^^ :
1ère solution :

ActiveCell.FormulaR1C1 = _     "=SUM(R[-7]C[-2],R[-6]C[-2],R[-5]C[-2],R[-4]C[-2],R[-3]C[-2],R[-2]C[-2],R[-1]C[-2],RC[-2],R[1]C[-2],R[2]C[-2],R[3]C[-2],R[4]C[-2],R[5]C[-2])"

2nd solution :

ActiveCell.FormulaR1C1 = "=SUM(R[-10]C[-2]:R[2]C[-2])"

Donc vous l'avez compris je ne recherche ni l'une ni l'autre solution mais un mix entre les deux.

Problématique:
Admettons que la taille du tableau varie, vous connaissez la valeur de la première valeur et de la dernière du tableau, mais dans le temps le nombre de lignes que constitue ce tableau varie donc les lignes varient entre [1 ; X].

Je souhaite trouver un code en VBA qui me permettent d'insérer dans une somme autant de valeurs à sommer qu'il y a de lignes à sommer dans le tableau. C'est à dire une sorte de moulinette qui détermine le nombres de valeur <> 0 dans le tableau et les insère dans la fonction SUM.

Exemple n°1 :
Si NbLigneTableauValeur = 5
Alors le résultat du code attendu serait :

ActiveCell.FormulaR1C1 = _     "=SUM(R[-7]C[-2],R[-6]C[-2],R[-5]C[-2],R[-4]C[-2],R[-3]C[-2])"

=> Dans la fonction SUM, nous avons 5 ocurences de modules RC, séparées par des virgules

Exemple n°2 :
Si NbLigneTableauValeur = 3
Alors le résultat du code attendu serait :

ActiveCell.FormulaR1C1 = _     "=SUM(R[-7]C[-2],R[-6]C[-2],R[-5]C[-2])"

=> Dans la fonction SUM, nous avons 3 occurrences de modules RC, séparées par des virgules

En gros ce que je cherche à faire c'est d'élaborer un code VBA pour Excel 2010 permettant de définir automatiquement le nombre d'occurrences dans une formule SUM pour y insérer les coordonnées RC des cellules dont les valeurs sont à sommer.

Savez vous comment faire ca? Je bûche complètement car j'ai besoin de prendre ces éléments à sommer un par un et donc de les séparer, dans les fonction SUM, par des virgules et non pas prendre la première valeur puis la dernière et les séparer seulement par les ":".

En pièce jointe, un petit support de mon fameux tableau ;-)

J'espère être compréhensible

Merci d'avance pour vos idées

Bien à vous Valentin

 

[Staple1600]

Re : Ajout automatique d'éléments dans une somme en VBA

Re


Il est trop tard ce soir pour moi.
Grosse journée de taf demain, je vais sous la couette

Je laisse Dranreb ou tout autre XLDnaute prendre le relais ce soir.

 

[phirexian]

Re : Ajout automatique d'éléments dans une somme en VBA

Pas de soucis je ne suis pas pressé non plus hein Si tu me réponds que lundi j'en survivrai ;-)!

Merci encore et bonne nuit ;-)

 

[phirexian]

Re : Ajout automatique d'éléments dans une somme en VBA

Bonjour à tous,

J'ai un souci de syntaxe dans mon fichier pour insérer une formule dans une cellule avec VBA.

Le problème de syntaxe n'est pas évident à trouver puisque j'utilise le plus possible de variables dans mon code VBA de manière à avoir une formule dynamique.

Vous trouverez en pièce jointe un fichier simplifié avec tous les éléments nécessaires pour comprendre, enfin je l'espère

Si vous avez des idées ou si vous comprenez ce qui se passe je vous en serait reconnaissant

 

[Dranreb]

Re : Ajout automatique d'éléments dans une somme en VBA

Bonjour.

À mon avis vous n'allez jamais y arriver en étant parti sur ce principe.
Ça semble pourtant assez proche d'un tableau qu'on pourrait remplir au fur et à mesure de ce qu'on trouverait en explorant la base dans cet ordre : Années, sens des mouvements, catégories, mois.
J'aurais de quoi vous permettre d'explorer facilement la base dans cet ordre en ayant tout mis dans une collection organisée facile à parcourir par des For Each…In imbriqués. Mais il faudrait qu'il y ait des dates correctes partout dans la colonne A, et que vous acceptiez que les catégories apparaissent classées par ordre alpha dans le résultat.

 

[phirexian]

Re : Ajout automatique d'éléments dans une somme en VBA

Bonjour Dranreb, merci pour ton message et ta proposition !

Par contre je ne comprends pas le lien avec ma problématique de syntaxe de mon code avec SUMPRODUCT :S!

Pourrais tu m'éclaier?

Bien à toi

 

[Dranreb]

Re : Ajout automatique d'éléments dans une somme en VBA

Bonjour.

Je ne vous suivrai jamais dans une telle mise en place d'un SUMPRODUCT sur de telles bases imbitables.
Lorsqu'on tombe sur une instruction aussi difficile à mettre au point, c'est qu'on est parti dans une mauvaise direction. Et même si on parvenait à la régler, ce serait un piège de maintenance à effectuer beaucoup trop souvent dans le code. De fait, des variables trop nombreuses, aux noms trop longs, et se référant de trop près à des cas d'espèces existant dans les données à traiter sont la pire des conceptions, à ne surtout pas envisager. Utilisez des tables, des collections, des dictionnaires, dans une programmation travaillant plus "en aveugle" mais qui s'adaptera d'elle même aux apparitions de nouveaux éléments dans les données.

 

[phirexian]

Re : Ajout automatique d'éléments dans une somme en VBA

Dranreb, jugé mon travail n'est pas le sujet bien que vos remarques puisses être pertinentes. Il est vrai que je n'ai pas forcément la meilleure des approches en terme de programmation mais j'essaie de faire mon possible pour obtenir les résultats que je souhaite.

J'ai exposé mon problème, j'essaie de trouver des solutions constructives car je sais, et je vous en remercie, que vous en avez les connaissances nécessaires et que vous passez du temps à les partager avec les internautes ce que je salue bien évidemment.

Mais dire que mon travaille est imbitable, quelque part cela n'est pas le sujet. Mais vous avez sans doute raison.

Qu'appelez vous une programmation plus en aveugle?

Bien à vous

 

[Dranreb]

Re : Ajout automatique d'éléments dans une somme en VBA

Eh bien par exemple (et ce n'est qu'un exemple) au lieu d'avoir autant de variables pour leurs numéros de lignes qu'il y a de choses différentes dans une colonne, mettre tout ce qu'on y trouve dans un dictionnaire avec comme item ce fameux numéro de ligne. Ça oblige peut être à un raisonnement plus abstrait, plus détaché des données pour concevoir les algorithmes nécessaires, mais ça aboutit à une programmation plus simple et souvent auto-adaptative aux données existantes.

 

[phirexian]

Re : Ajout automatique d'éléments dans une somme en VBA

Eh bien par exemple (et ce n'est qu'un exemple) au lieu d'avoir autant de variables pour leurs numéros de lignes qu'il y a de choses différentes dans une colonne, mettre tout ce qu'on y trouve dans un dictionnaire avec comme item ce fameux numéro de ligne. Ça oblige peut être à un raisonnement plus abstrait, plus détaché des données pour concevoir les algorithmes nécessaires, mais ça aboutit à une programmation plus simple et souvent auto-adaptative aux données existantes.

Whaou merci Dranreb, j'avoue que mes connaissances sont limités sur tout ce qui est collections par exemple et d'après ce que tu me dis, j'ai nettement moyen de m'améliorer et fluidifier mon code.

Je suis preneur, aurais tu des exemples de fichiers qui fonctionnent ainsi ou un totu qui puisse m'aider en ce sens?

Merci beaucoup de participer à mon amélioration ;-)

 

[Dranreb]

Re : Ajout automatique d'éléments dans une somme en VBA

Je pense depuis un moment à cette discussion qui commençait aussi par un SumProduct en VBA et a bénéficié d'une solution par une procédure complètement différente pour obtenir le résultat souhaité.
Là aussi il est question d'y mettre horizontalement certaines choses, des années et non des mois comme pour vous, mais le principe reste le même.
Je joint le dernier fichier relatif à cette affaire.

 

[phirexian]

Re : Ajout automatique d'éléments dans une somme en VBA

Bonsoir Dranreb,

Je dois vous avouer quelque chose, votre travail est super propre, c'est très impressionnant oO!

J'aimerai bien en faire autant par contre je comprends pas du tout comment prendre la chose. La manière de coder est tellement différente et tellement épurée que je suis un peu perdu oO! => C'est paradoxal vous me direz par contre là je sèche complètement !

En gros comment ca marche les collections ^^!!! Vous avez votre logique, je n'en suis qu'admiratif!! Mais auriez vous un tuto un peu plus accessible pour un noob comme moi ^^!!?

Bien à vous

 

[Dranreb]

Re : Ajout automatique d'éléments dans une somme en VBA

Bonjour.

La seule chose à savoir pour l'instant est qu'une collection peut être exploré par un For Each Membre In elle.
Membre pouvant être un Variant ou un objet du type approprié, c'est à dire qui y aura été mis.
Vous n'avez pas besoin d'étudier en détail comment la fonction GroupOrg s'y prend pour fabriquer une collection d'objets de type SsGroup conformément à ce que vous lui demandez.