XL 2013 Modifier un champ calcule de tcd grace au vba

[Chrystel01]

Bonjour

Je sollicite votre aide svp car je tourne en rond ...

J'ai TCD avec un champ calculé : Tx de fermeture resto vs jours de cantine = nb de jour de fermetures de restaurants / nb de jours théorique d'ouverture
sachant que ce nb théorique est fixe quel que soit le restaurant et évolue juste tous les mois.

Je voudrais donc pouvoir modifier ce nb de jours théoriques dans le champ calculé sans devoir le modifier manuellement (car j'ai plusieurs champs calculés de ce type)

Aussi, je pensais modifier le champ calculé grace à un code vba :
- saisir le nb de jours théorique dans une cellule
- stocker ce montant dans une variable
- puis réutiliser cette variable pour l'appliquer dans le champ calculé.
Mais cela ne fonctionne pas

Je vous joints un exemple pour que cela soit plus clair ainsi que le code vba
Pourriez-vous m'aider SVP ?

Dim nbjour
nbjour = Range("I14").Value
ActiveSheet.PivotTables("Tableau croisé dynamique3").CalculatedFields( _
"TX Fermeture resto vs Jours de cantine").StandardFormula = "=Restaurant/nbjour"


J'avais par ailleurs essayé de saisir ce nb de jours par établissement et par mois dans la base du TCD mais cela ne fonctionne pas. Il faudrait le créer sur de multiples champs (1 10aine * le nb de périodes * le nb d'établissements)... ce qui n'est pas possible.

Je vous remercie d'avance pour votre aide

Chrystel

 

[chris]

Bonjour
Dans ton exemple on n'a pas de période dans le source...

Il faudrait :

1. un tableau de paramètres : mois, nombre de jours
2. l'ajout de 2 colonnes à la source afin de calculer les ratios dans la source en fonction de ce tableau

j'ai fait un exemple juste avec la formule

 

[Chrystel01]

Bonsoir Chris, Bonsoir le forum,

Je ne peux malheureusement pas intégrer le nb de jour ou les ratios directement dans la base pour une même raison :
J'ai de multiples dimensions sur lesquels portents les indicateurs et ratios : il y a l'EI, le niveau, la zone, la période, ... tous ceux à minima qui apparaissent dans la zone de filtre du TCD dans le fichier joint (1er onglet). J'ai donc 10 ratios à créer qui doivent pouvoir être calculés sur ces multiples dimensions....

Je vous joints un extrait du fichier qui comporte 3 onglets
1- le tableau croisé qui sera disponible sur 5 indicateurs et 10 champs calculés ds le TCD
2- une base qui va grossir puisqu'elle sera alimentée par plusieurs mois / années (j'ai déjà du supprimé des lignes pour pouvoir envoyer le fichier)
3- la saisie du nb de jours théoriques à récupérer pour le mois en cours (cellules B2 C2)

C'est pourquoi j'avais cette idée de passer par VBA :
stocker le nbde jours théoriques (ex de la cellule B2) comme une variable qui serait ensuite intégrée dans le champ calculé du TCD

J'avais donc joint ds mon 1er post une version simplifiée avec une tentative de code que j'aurais ensuite adaptée sur mon cas plus complexe.

Dim nbjour
nbjour = Range("I14").Value
ActiveSheet.PivotTables("Tableau croisé dynamique3").CalculatedFields( _
"TX Fermeture resto vs Jours de cantine").StandardFormula = "=Restaurant/nbjour"

Merci d'avance pour votre aide ...

 

[chris]

RE

Curieusement le TCD n'inclut toujours pas de période ou date...

Je ne saisis pas quelle est la logique entre des données passées et le mois en cours... ni quel déclencheur devrait modifier la formule des champs calculés
Or c'est cette logique qui doit permettre de définir la meilleure approche...

Si l'EI est présent dans le TCD on est donc au niveau unitaire où en dehors de la date et du type, les autres champs associés à un EI semblent identiques, ce qui apriori limite le nombre des cas

 

[Chrystel01]

Re

Date , type et origine seront différents mais secteur/onglets / territoires sont aussi amenés à changer, même si c'est à une fréquence moindre. (Je n'ai pas pu envoyer toutes les lignes de la base en raison de la taille du fichier)

Par ailleurs, mes excuses si je n'ai pas été claire sur la période...

Idéalement, j'aurais souhaité pouvoir ajouter un filtre période sur le TCD pour avoir ce tableau sur différents mois.
Mais je ne trouvais pas de solutions et afin de ne pas alourdir un fichier très lourd qui pourra comporter jusqu'à 100000 lignes, je suis partie sur l'idée de faire un cumul à date.

Donc dans l'onglet du nb de jours théoriques, je pensais récupérer le cumul du nb de jours en fonction du mois en cours, où mois en cours =date max de la colonne J (date) dans la base.


Ex : si les dernières données extraites de la base datent :

-de janvier =>
je recherche le nb de jours cumulés théoriques entre sept 2020 et janvier 2021 = 86 j cumulés depuis sept 2020 jusqu'au mois en cours de janvier et j'inclus cette valeur dans le champ calculé du TCD

- de février =>
si la période en cours (max date dans la base) est février, j'inclus dans le champ calculé du TCD 96J...

C'est pourquoi, je pensais stocker la cellule qui donne le nb de jours cumulés jusqu'au mois en cours dans une variable VBA qui alimenterait le champ calculé du TCD.

J'espère avoir été plus claire.
Merci beaucoup pour votre aide !

 

[job75]

Bonjour Chrystel01, chris, le forum,

A partir du fichier du post #1, le code de la 2ème feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [I14]) Is Nothing Then
    Application.ScreenUpdating = False
    On Error Resume Next 'si I14 est vide ou non numérique
    With Me.PivotTables(1)
        .PivotFields("Tx accueil pique-nique vs jours de cantine").Delete
        .CalculatedFields.Add "Tx accueil pique-nique vs jours de cantine", "=Restaurant/" & [I14], True
        .AddDataField .PivotFields("Tx accueil pique-nique vs jours de cantine"), "Tx accueil P  / j. cantine", xlSum
        With .TableRange1
            If .Cells(1, 5) <> "" Then
                .Cells(1, 4).AutoFill .Cells(1, 4).Resize(, 2), xlFillFormats 'copie les formats
                .Columns(5).Borders(xlEdgeRight).Weight = xlThin
                .Columns(5).NumberFormat = "0.0%"
            End If
        End With
    End With
    Application.ScreenUpdating = True
End If
End Sub

A+

 

[Chrystel01]

Bonjour,

Merci beaucoup Job75 !
Grace à votre proposition et en essayant de comprendre votre code, j'ai trouvé un bout de programme similaire sans supprimer le champ mais juste en le modifiant pour ne pas refaire le format de tableau.

Je l'ai adapté à notre fichier joint en utilisant la fonction target.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not IsNumeric(Target) Then Exit Sub
If Target.Address = "$I$14" Then
Me.PivotTables("Tableau croisé dynamique3").CalculatedFields("Tx accueil pique-nique vs jours de cantine") _
.StandardFormula = "='Restaurant'*" & Target
End If
End Sub

Mais j'aurais 2 questions svp :

1-Dans notre exemple précédent, Target est dans la même feuille. Mais dans mon cas plus complexe il sera sur un autre onglet ("jours"). Que devrais-je écrire dans l'expression
"if Target.Address = "$I$14" Then... pour prendre en compte la cellule I14 de l'onglet "jours" ?

2- Par ailleurs, l'utilisateur ne fera pas un clic/ validation sur la cellule.
Ce code sera inclus dans une longue macro qui créé une base consolidée qui importe différents fichiers, étend les formules, actualise les TCD.
Je dois écrire une ligne "select I14" pour déclencher le code attaché à la feuille?

Merci encore pour votre aide vraiment très utile qui me permet par ailleurs d'apprendre un peu plus le VBA...

Bonne journée

 

[job75]

J'ai répondu à la question posée au post #1, il fallait diviser par I14.

 

[Chrystel01]

Oui bien sûr, pardon : je divise par I14

Merci beaucoup. J'ai vu votre 'post" qui me permet de bien avancer !
Grâce à votre code, j'ai essayé de voir s'il y avait un moyen d'éviter l'ajout / suppression d'un champ dans le TCD ( cela m'obligerait à refaire les formats de tous les tcd)

Pour les 2 codes vba possibles, je me pose une même question :
- comment puis-je faire référence à la cellule I14 si elle est sur un autre onglet "jours"
(ex avec if Target.Address = "$I$14" ?)
- par ailleurs, pour déclencher le code avec une autre macro et sans intevention manuelle :
quel code faut il mettre pour valider la cellule I14 ?

Merci d'avance !

 

[job75]

Vous avez dit :

2- Par ailleurs, l'utilisateur ne fera pas un clic/ validation sur la cellule.

Donc on n'utilisera pas Target.

Pour définir une cellule en VBA la syntaxe est Set c =Feuil2.[I14]

 

[Chrystel01]

Merci pour la syntaxe !

L'utilisateur ne fait pas de clic directement dans la cellule mais ce clic peut être fait automatiquement via une macro non ? C'est ce code que je cherche et que je ne trouve pas...

Merci !

 

[job75]

Vous pouvez écrire une macro et l'affecter à un bouton, c'est le plus simple.

 

[Chrystel01]

Je ne suis pas sure de comprendre ?
J'ai une macro principale affectée à un bouton qui importe des fichiers, créé une base consolidée et actualise des tcd liée à cette base...
Avant l'actualisation des TCD, il me faut changer le nb de jours grâce au code de la feuille mais je ne peux la déclencher qu'en cliquant sur la cellule "nb de jours" ?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not IsNumeric(Target) Then Exit Sub
If Target.Address = "$I$14" Then
Me.PivotTables("Tableau croisé dynamique3").CalculatedFields("Tx accueil pique-nique vs jours de cantine") _
.StandardFormula = "='Restaurant'/" & Target
End If

Ou sinon est il possible de l'intégrer drectement dans la macro principale ? En supprimant juste la 1ère ligne ?
Désolée de toutes ces questions mais j'apprends et découvre au fur et à mesure car je débute, n'ai jamais appris le vba et je passe beaucoup de temps à comprendre ...Et vraiment merci pour votre aide.

 

[job75]

Le problème posé au post #1 était simple, je l'ai résolu.

Maintenant on ne sait plus ce que vous voulez, créez une nouvelle discussion.

 

[Chrystel01]

ok, merci.