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

Bonsoir à tous


Si j'ai bien compris la question, voici une V3 et un V4 en bonus

Sub ADDITIONNERV3()
Dim NbLigneTableauValeur&
NbLigneTableauValeur = ActiveCell.Row - 3
ActiveCell.FormulaR1C1 = "=SUM(R[-" & NbLigneTableauValeur & "]C[-2]:RC[-2])"
End Sub

Sub ADDITIONNERV4()
Dim NbLigneTableauValeur&
NbLigneTableauValeur = ActiveCell.Row
ActiveCell.FormulaLocal = "=SOMME(C2:C" & NbLigneTableauValeur & ")"
End Sub

Et pour le fun, une one-liner en guise de V5

Sub ADDITIONNERV5()
ActiveCell = Application.Sum(Range("C2:C" & ActiveCell.Row))
End Sub

et une dernière variante pour préserver la formule

ActiveCell = "=SUM(" & Range("C2:C" & ActiveCell.Row).Address & ")"

 

[phirexian]

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

Bonsoir Staple1600,

Merci pour tes réponses rapide ca fait plaisir à lire !

Par contre j'aurai souhaiter une solution qui n'utilises pas les ":" dans la fonction SUM

Donc ta proposition n°4 correspondait à mon besoin sauf que j'aurai préféré un truc du genre :

Sub ADDITIONNERV3()
Dim NbLigneTableauValeur as Integer
NbLigneTableauValeur = ActiveCell.Row - 3
ActiveCell.FormulaR1C1 = "=SUM(RC, R[-1]C , ......... , R[NbLigneTableauValeur]C)"
End Sub

Si tu as d'autres idées je suis preneur ^^.

Je réexplique mon problème en gros si NbLigneTableauValeur = 3 alors j'insère 2 virgules dans ma fonction SUM séparant 3 couples de coordonnées de cellules dont je souhaite ajouter la somme, je veux proscrire les ":" !!

 

[Dranreb]

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

Bonsoir.
Un peu confus tout ça.
Si vous connaissez le numéro de ligne LDéb de la 1ère cellule et LFin celui de la dernière, vous pouvez faire comme la 1ère solution ci après. Si la formule est à mettre sous le dernier faite comme la seconde :

Sub ADDITIONNER()
Const Ldéb& = 3, Lfin& = 22 ' en constrantes ici mais supposées variables
Range("E10").FormulaR1C1 = "=SUM(R" & Ldéb & "C3:R" & Lfin & "C3)"
Cells(Lfin + 1, "C") = "=SUM(R" & Ldéb & "C:R[-1]C)"
End Sub

Edit: Vos cellules à sommer sont bien contiguës, pourquoi ça n'irait pas ??

Bonsoir Stapple 1600.

 

[Staple1600]

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

Re, Bonsoir Dranreb

phirexian
Je ne vois pas où tu veux en venir car pour moi
=SOMME(C3:C4;C5:C6) est équivalent à =SOMME(C3:C6) , non ??
(cela donne 16 avec ton fichier exemple)

PS: J'ai ajouté d'autre formes d'écriture dans l'édition de mon précédent message

 

[phirexian]

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

Bonsoir Dranreb, bonsoir Staple1600,

Merci pour vos réponse je me réjouis de l'intérêt que vous portez à mon sujet !

Par contre ca ne répond pas à mon souci, je dois mal m'exprimer ^^!!

Après une dure journée, j'ai cherché à mieux m'exprimer :

En fait ce que je cherche à faire c'est ca :

- J'ai besoin d'insérer dans une cellule d'une feuille excel une formule de calcul

- Cette formule de calcul doit être la somme de valeurs de cellules dont les coordonnes sont connues

- Ces cellules à sommer appartiennent à la même colonne et ne sont pas forcément les unes en dessous des autres

- La cellule qui recevra la formule de SUM doit être dynamique, c'est à dire que le montant de la somme doit évoluer en temps réel avec l'évolution des valeurs des cellules sommées

Algorithme recherché :
Etape n°1:
Si NbCellulesàSommer = 3
Alors création de 3 slots dans la fonction SUM : Slot(1),, Slot(2), Slot(3)
=> Slot(1), Slot(2) et Slot(3) n'ont pas de valeurs encore : Obj: Conditionner la "forme" de la fonction SUM
=> Le code pourrait ressembler à cela :

ActiveCell.FormulaR1C1 = "=SUM( Slot(1) , Slot(2) , Slot(3) )"

Etape n°2:
Ensuite j'attribue à Slot(1), Slot(2) et Slot(3) les coordonnées des cellules dont je veux sommer les valeurs
=> Le code pourrait ressembler à cela :

For i = 1 to NbCellulesàSommer
      Slot(i) = CoordonnéeValeur(i)
      Next i

Etape n°3:
Au final on se retrouverai avec une cellule dont la fonction SUM ressemblerait à :

 ActiveCell.FormulaR1C1 = "=SUM( CoordonnéeValeur(1) ,CoordonnéeValeur(2) , CoordonnéeValeur(3) )"

L'objectif final : Les cellules sont dans un tableau dont les lignes peuvent évoluer, et je dois être capable à tout moment de m'adapter et d'obtenir le bon résultat

Je pense que mon besoin réside dans l'élaboration d'une fonction particulière mais je ne sais pas faire :S!

Auriez vous une idée? Merci d'avance

 

[Staple1600]

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

Bonsoir à tous

Je récapépéte

=SOMME(C3:C4;C5:C6) est équivalent à =SOMME(C3:C6) , non ??
(cela donne 16 avec ton fichier exemple)

tout comme
=SOMME(C3:C4;C5:C6;C6:C7) est équivalent à =SOMME(C3:C7)
etc ...

Tu n'es pas d'accord ???

 

[phirexian]

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

A si si si je suis bien d'accord, jusque là tout va bien ^^! Que cherches tu à me démontrer?

 

[Staple1600]

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

Re

phirexian

A si si si je suis bien d'accord, jusque là tout va bien ^^! Que cherches tu à me démontrer?

Simplement que mes propositions dans ce message
https://www.excel-downloads.com/threads/ajout-automatique-delements-dans-une-somme-en-vba.229584/
donnent la bonne formule et ou la bonne somme, non ???

EDITION: Bonsoir Dranreb

 

[Dranreb]

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

Bonsoir.

Ces cellules à sommer appartiennent à la même colonne et ne sont pas forcément les unes en dessous des autres

Effectivement si les plages à sommer sont disjointes il y a peut être autre chose à faire. Mais c'est trop vague.

Cette formule de calcul doit être la somme de valeurs de cellules dont les coordonnes sont connues

Connues de qui de quoi et sous quelle forme ? Pas assez d'info de votre part pour savoir s'il faut faire une somme de plusieurs plages nommées dans le classeur, ou d'expressions DECALER, ou d'une plage multi-zones, voire d'une plage pouvant s'obtenir avec SpecialCells, où s'il n'existe pas un critère dans une colonne voisine permettant de le faire avec un SOMME.SI

Bonsoir Staple1600.

 

[phirexian]

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

Re

phirexian
A si si si je suis bien d'accord, jusque là tout va bien ^^! Que cherches tu à me démontrer?
Simplement que mes propositions dans ce message
Lien supprimé
donnent la bonne formule et ou la bonne somme, non ???

EDITION: Bonsoir Dranreb

Staple1600, je suis d'accord avec toi, le résultat est correct mais ce sont les ":" dans la fonction SUM qui me dérangent ^^!

 

[phirexian]

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

Merci Dranreb pour ta réponse ,

Bonsoir.
Ces cellules à sommer appartiennent à la même colonne et ne sont pas forcément les unes en dessous des autres
Effectivement si les plages à sommer sont discontinues il y a peut être autre chose à faire. Mais c'est trop vague.
Cette formule de calcul doit être la somme de valeurs de cellules dont les coordonnes sont connues
Connues de qui de quoi et sous quelle forme ? Pas assez d'info de votre part pour savoir s'il faut faire une somme de plusieurs plages nommées dans le classeur, ou d'expressions DECALER, ou d'une plage multi-zones, voire d'une plage pouvant s'obtenir avec SpecialCells, où s'il n'existe pas un critère dans une colonne voisine permettant de le faire avec un SOMME.SI

Permettez moi de reprendre mon problème, j'ai certainement une solution beaucoup plus simple en fait.

Je cogite pas mal ^^ mais j'ai du mal à décoincer!

Mon autre proposition consiste à faire une somme d'élément dans un tableau à 2 colonnes (colonne 1 = les valeurs à sommer, colonne 2 = des catégories.

Savez-vous comment faire une somme d'élément appartenant à une plage en fonction d'une plage de critères?

J'ai illustré mon besoin dans le fichier joint.

Si nous trouvons une solution à ce second problème, je pourrai adapter la solution à ma problématique principale ;-)!

Avez vous des idées?

 

[Dranreb]

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

J'obtiens 55 avec cette formule :

=SOMMEPROD(N(ESTNUM(EQUIV($C$3:$C$22;$E$3:$E$6;0)));$B$3:$B$22)

.FormulaR1C1 = "=SUMPRODUCT(N(ISNUMBER(MATCH(R3C3:R22C3,R3C5:R6C5,0))),R3C2:R22C2)"

 

[phirexian]

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

Dranreb , ca à l'air top ta proposition, je regarde si ca peut coller chez moi et je reviens vers vous

Merci encore

 

[phirexian]

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

Dranreb petite question,

A quoi correspond le "N" dans :

=SOMMEPROD(N(ESTNUM(EQUIV($C$3:$C$22;$E$3:$E$6;0)));$B$3:$B$22)

Merci d'avance