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

 

[Dranreb]

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

Il convertit notamment les VRAI en 1 et FAUX en 0.

 

[phirexian]

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

Ah ok, c'est donc une fonction d'excel? Une fonction N

=> J'ai l'air con con à poser cette question désolé -_-'!

 

[phirexian]

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

Ahhhh ca y est je viens de comprendre!!! Merci infiniment Dranreb pour ton aide tu viens de m'enlever une belle épine du pied, en tout cas je l'espère ;-)!

 

[Staple1600]

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

Re

phirexian
Tant mieux pour pour l'épine

Mais cela n'a plus rien à voir avec la question de départ, non ??
Ajout automatique d'éléments dans une somme en VBA

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

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

[h=2][/h]

 

[phirexian]

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

Si la solution de Dranreb m'aide énormément à résoudre mon problème ;-)!

Si je vous montre le code dans lequel je souhaite utiliser sa solution je crois qu'on va pleurer ^^!

 

[Staple1600]

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

Re,

phirexian

Si la solution de Dranreb m'aide énormément à résoudre mon problème ;-)!

Je ne dis pas le contraire ...

Je dis simplement que:
=SOMMEPROD(N(ESTNUM(EQUIV($C$3:$C$22;$E$3:$E$6;0)));$B$3:$B$22)
1) c'est une formule (alors que le titre du post parle de VBA)
2)

Par contre j'aurai souhaiter une solution qui n'utilises pas les ":"

je compte 3 : dans la formule
3) références absolues ($) (alors que j ai cru lire dynamique)
4) et surtout j'ai du mal à voir le rapport avec ci-dessous.

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 :
Code vb:
ActiveCell.FormulaR1C1 = "=SUM( Slot(1) , Slot(2) , Slot(3) )"

Si je vous montre le code dans lequel je souhaite utiliser sa solution je crois qu'on va pleurer ^^!

Non, c'est juste que cela infirmera ou confirmera ce que je viens d'écrire ci-dessus
(et accessoirement cela éclairera ma lanterne)

NB: Précisions : je ne critique absolument pas la réponse de Dranreb
Je dis simplement que vous estimez qu'elle solutionne votre question
(quand moi je dis que ce n'est pas la question de votre premier message)

 

[Dranreb]

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

Si je vous montre le code dans lequel je souhaite utiliser sa solution je crois qu'on va pleurer ^^!

Ou alors, on va … rigoler. Surtout si on reprend tout à zéro en faisant complètement différemment…

 

[phirexian]

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

Bonsoir à tous, excusez moi j'ai mis un peu de temps pour revenir sur le sujet.

J'en ai profiter pour appliquer la formule VBA de Dranreb, par contre j'ai une erreur de syntaxe et je suis infoutu de mettre la main dessus, je ne comprends pas ce qui se passe :S!

Voilà la ligne de code qui me gène :

    Worksheets(NomFeuilleAnalyse).Range(Cells(TabCoordonnéesMois(g, 1) + TabRefDonnéesAnalyse(k, 3), TabCoordonnéesMois(g, 2)).Address).FormulaR1C1 = _
            "=SUMIFS(Débit_Euros,N°_Ligne,""<=""& R["& RefN°deLigneRéelDansTableau1_Ligne - TabRefDonnéesAnalyse(k, 1) & "]C,N°_Ligne,"">""& Analyse_" & AnnéeSuivante & "!R[" & RefN°deLigneRéelDansTableau1_Ligne - TabRefDonnéesAnalyse(k, 1) & "]C[" & DifferenceNegColonne & "])- _
            - SUMPRODUCT(N(ISNUMBER(MATCH( R["&RefAlimentaireAnalyse_Ligne-TabRefDonnéesAnalyse(k, 1)&"]C[" &TabRefDonnéesAnalyse(k, 2) - TabCoordonnéesMois(g, 2) & "] _
            :R[" & RefImpotContraventionsAdministrationsAnalyse_Ligne - TabRefDonnéesAnalyse(k, 1) & "]C[" & TabRefDonnéesAnalyse(k, 2) - TabCoordonnéesMois(g, 2) & "] _
            ,Sources! R["&RefAlimentaireSources_Ligne - TabRefDonnéesAnalyse(k, 1)&"]C["&TabRefDonnéesAnalyse(k, 2) - RefListeCatégoriesSources_Colonne&"] _
            :R[" & RefImpotContraventionsAdministrationsSources_Ligne-TabRefDonnéesAnalyse(k, 1)&"]C[" & TabRefDonnéesAnalyse(k, 2) - RefListeCatégoriesSources_Colonne & "]),0)))_
            ,R[" & RefAlimentaireAnalyse_Ligne - TabRefDonnéesAnalyse(k, 1) & "]C:R[" & RefImpotContraventionsAdministrationsAnalyse_Ligne - TabRefDonnéesAnalyse(k, 1) & "]C)"

A la 6ème ligne j'ai une erreur sur

SUMPRODUCT(N(ISNUMBER(MATCH( R["&RefAlimentaireAnalyse_Ligne-TabRefDonnéesAnalyse(k, 1)&"]

mais je ne sais pas pourquoi, avez vous une idée?

Et excusez moi pour vous avoir donné l'impression de vous roulez dans la farine ca n'était pas le but :S!

Bien à vous

 

[Staple1600]

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

Bonsoir à tous


phirexian
Tu avoueras que ton dernier code est très éloigné de celui présent dans ton message #1 ...
Tu peux joindre un fichier exemple avec des données fictives et le code VBA de ton dernier messsage
Ceci afin que l'on puisse faire des tests sur nos PC.

 

[phirexian]

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

Bonsoir Staple1600,

Je dois te donner raisons pour ce coup là ;-)!

En fait j'ai essayé d'ouvrir un autre classeur excel et d'y copier la ligne de code que je vous ai partagé à l'instant.

Et pour vérifier la syntaxe je compile et là excel me remonte l'erreur de syntaxe, essais si tu veux !

 

[Staple1600]

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

Re

???
Pour tester il nous faut un fichier qui inclue les variables présentes dans ton dernier code VBA, non ?

 

[phirexian]

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

Euh tu me dis si je me goure complètement ^^!

Mais lorsqu'on compile un code VBA, VBA vérifie que les fonctions sont utilisées sous le bonne syntaxe avant e prendre en compte les variables?

 

[Staple1600]

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

Re

?????
Comment je sais à quoi correspond ces mots en bleu (entre autres) sans fichier exemple et le code VBA ??
Worksheets(NomFeuilleAnalyse).Range(Cells(TabCoordonnéesMois(g, 1) + TabRefDonnéesAnalyse(k, 3), TabCoordonnéesMois(g, 2)).Address).FormulaR1C1

Donc prends le temps de joindre un fichier exemple actualisé allégé et anonymisé afin que l'on puisse tester ton code VBA actuel !!

 

[phirexian]

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

Bon ok ^^ Si c'est vraiment nécessaire je le fais de ce pas ;-)

 

[phirexian]

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

J'ai réussi à simplifier au mieux mon travail pour qu'il soit, je l’espère compréhensible.

Le code qui pose des soucis se trouve dans le module 6

Merci encore ;-)

Bien à vous