XL 2013 VBA : ajouter une formule qui additionne des mois sachant qu'ils sont flexibles

[Chrystel01]

Bonjour,

J'ai un fichier de consolidation qui récupère les données d'autres fichiers (établissements).
Le nombre de colonnes est flexibles en fonction du nombre de mois.
Je voudrais ajouter les cellules jaunes :une titre établissement en ligne 1 à droite du tableau et une formule qui additionne tous les heures facturées (01_fact, 02_fact...) sachant que ce nombre de mois peut varier.
Je vous joints le fichier et la macro
Pourriez-vous m'aider svp ?
Merci d'avance pour votre aide

Chrystel

 

[chris]

Bonjour

Faisable assez aisément avec PowerQuery, intégré à Excel 26016 et +, en add on sur 2013

 

[sylvanu]

Bonjour Christel, Chris,
Vous pouvez tenter en AD2 :

=SOMME.SI.ENS(F2:AB2;$F$1:AB$1;"*_fact";$F$1:AB$1;"<>Total*";$F$1:AB$1;"<>Montant*")

 

[Chrystel01]

Bonjour Sylvanu et Chris

Merci beaucoup pour vos réponses.
Je me rends compte que je n'ai pas été assez claire.
Dans le fichier joint, on voit les mois de janvier à septembre (colonne W).
Mais la macro de consolidation doit pourvoir marcher quel que soit le nombre de mois (par exemple de janvier à décembre)
=> Comment faire dans la macro pour que :
-les zones "établissement" et "cumul" se mettent sur les 2 colonnes juste après le tableau (après la colonne montant facturé)
- saisir en VBA une formule qui va consolider les heures facturées quel que soit le nombre de mois sans prendre le "Total"

Merci beaucoup pour votre aide
Bonne journée

Chrystel

 

[chris]

Bonjour à tous

Pour ma part j'ai suggéré PowerQuery : tu n'as pas répondu à cette proposition et indiqué si peux ou non installer l'add on

A noter qu'une restitution sous forme de tableau normalisé serait plus adaptée à l'analyse ultérieure...

 

[sylvanu]

Bonjour Chrystel, Chris,
Alors en PJ un essai avec une fonction perso :

Function Cumul(Lig As Integer)
Dim L As Integer, C As Integer, i As Integer, V As Variant
Application.Volatile
C = Application.Match("Cumul", Range("1:1"), 0)
L = Lig
Cumul = 0
For i = 1 To C - 1
    V = LCase(Trim(Cells(1, i)))    ' Tout minuscule sans espace
    If V Like "*_fact" And V <> "montant_fact" And V <> "total_fact" Then
        Cumul = Cumul + Cells(L, i)
    End If
Next i
End Function

Syntaxe de la fonction : =Cumul(Ligne())
Les intitulés doivent être en ligne 1.

 

[Chrystel01]

Bonjour Sylvanu, Chris,

Merci beaucoup pour vos retours très réactifs et complets !.

Chris, désolée je n'ai malheureusement pas la possibilité d'utiliser power query...

Sylvanu,
Un grand merci pour cette superbe fonction personnalisée.
J'espère un jour arriver à votre niveau car pour l'instant je débute en VBA.
Si j'essaye d'intégrer cette fonction dans la macro VBA, cela ne fonctionne pas
ThisWorkbook.Sheets("Consolider").Cells(ligne_coller, 29).Formula = Cumul(Ligne())
Je me trompe dans la syntaxe ou n'est-ce pas possible de l'intégrer dans le VBA ?

Merci d'avance

Chrystel

 

[sylvanu]

Bonjour Chystel,
Ligne() est une instruction XL, n'est pas utilisable en l'état. Ici c'est juste un paramètre à passer à la fonction. Désolé pour le choix de la variable, j'aurais du l'appeler Param_Ligne pour ne pas porter à confusion.
En VBA, ThisWorkbook.Sheets("Consolider").Cells(ligne_coller, 29).Formula = Cumul(ligne_coller) si c'est bien ce que vous voulez faire.

En PJ un ex. Les calculs se font automatiquement quand la feuille Consolider est sélectionnée, avec :

Sub Worksheet_Activate()
    Dim DerLig As Integer, L As Integer
    DerLig = Sheets("Consolider").Range("A65500").End(xlUp).Row
    For L = 2 To DerLig
        Cells(L, 30) = Cumul(L)
    Next L
End Sub

 

[Chrystel01]

Merci beaucoup beaucoup Sylvanu !
Pour votre disponibilité, réactivité et vos réponses très détaillées !
C'est exactement ce que je souhaitais faire, à la différence que j'y passe des heures....je suis encore dans la phase d'apprentissage.
Grâce à vous j'apprends le VBA et je pourrais réutiliser ces exemples dans d'autres macro