Modification formule par VBA apres duplication feuille

Bonsoir a tous

J'aurais besoin des lumieres du forum une fois de plus...

j'ai cree une macro("CREATION NOUVEAU MOIS") pour dupliquer la feuille précédente de mon classeur

du coup les formules dans les cellules sont elles aussi dupliquées

pour la nouvelle feuille ("Décembre 2016") j'aimerai que la formule qui se trouve dans la cellule v14 soit modifiée

Le but étant de décaler de 3 colonnes a chaque nouvelle feuille et d'ajouter le mois précédent

voici un exemple plus parlant:

la formule dans la cellule de la feuille d'origine (Novembre 2016)

=AE31+V11*15+'Octobre 2016'!AE31

je voudrais que dans la nouvelle feuille (Décembre 2016) elle devienne apres modif par vba

=AH31+V11*15+'Octobre 2016'!AH31+'Novembre 2016'!AH31

et pour "Janvier 2017", dupliquée a partir de "décembre 2016" elle devienne

=AK31+V11*15+'Octobre 2016'!AK31+'Novembre 2016'!AK31+'Décembre 2016'!AK31

et ainsi de suite...

je joins mon fichier

merci a vous

Bonjour

Es-tu sur de ta formule en V14 de la Feuille Novembre 16

=AE31+V11*15+'Octobre 2016'!AE31

car 'Octobre 2016'!AE31 fait référence à la colonne Novembre de cette feuille Octobre 2016

A te lire

Cordialement

Bonjour amadeus,

Merci de t intéresser à mon problème...

Oui la formule est bonne

Elle me permet de calculer ma prime mensuelle de 2% par rapport à la date de facturation qui peut être postérieure à la date de création de la ligne plus une prime de 15 euros par rdv fixé dans le mois

La ligne est renseignée au mois correspondant à la prise de rdv

La facturation peut avoir lieu en novembre mais je vais aller la renseigner en octobre si le rdv à eu lieu en octobre.

J espère que je ne t ai pas perdu en chemin...

Encore merci

Bonjour

Si tu sais transcrire cette formule en VBA....

En attendant, et à tester en réel.

sur la feuille Novembre 2016, en V14, la formule

=DECALER($AA$31;;EQUIV(DATE(ANNEE(D1);MOIS("1/"&D1);1);$AB$2:$DG$2;0))+V11*15+DECALER(INDIRECT("'"&TEXTE(MOIS.DECALER(D1;-1);"mmmm aaaa")&"'!$AA$31");;EQUIV(DATE(ANNEE(D1);MOIS("1/"&D1);1);$AB$2:$DG$2;0))

Pour que cette formule fonctionne, j'ai ajouté dans ton Code (Module9, ligne huit)

ActiveSheet.Range("D1") = Format(nom, "MMMM YYYY")

Tu testes et me dis...

Cordialement

11copie-de-testjc.xlsm (111.71 Ko)

Merci pour ta réponse rapide

Malheureusement je ne suis pas assez calé pour retranscrire cette formule en vba...

Je teste ça demain et je te tiens au courant.

Bonne soirée à toi

Cordialement

Jcjez

Bonjour au forum et a toi Amadéus,

je viens de tester ce que tu m'as proposé , la formule ne prends pas en compte tous les mois, elle prend en compte Novembre 2016 (origine de la duplication) et Décembre 2016(résultant de la duplication) mais pas octobre.

Elle ne pourra pas fonctionner pour les feuilles suivantes, car a chaque création de feuille supplémentaire , il faut que la formule évolue:

- elle s'allonge en prenant en compte tous les mois précédents, et chaque feuille précédente doit renvoyer la valeur du mois de la derniere feuille creee.

C'est pas évident a expliquer...je joins mon fichier a nouveau avec explications.

je te renvoie mon fichier avec les formules d'origine et une explication en Décembre 2016

Merci

12copie-de-testjc.xlsm (150.80 Ko)

Bonjour

Teste. Nous viendrons aux explications si c'est Ok

Cordialement

14copie-de-testjc.xlsm (138.13 Ko)

Bonsoir a toi Amadeus,

c'est exactement ce que je voulais, cela fonctionne parfaitement

Je ne pensais pas que cela était possible sans vba...

Je t'avoue que meme sans vba, je ne serai pas arrivé tout seul a mes fins...pour moi cette formule est tres complexe ,c'est du chinois....

Si tu pouvais m'expliquer ta formule, ce serait cool; mais rien ne presse, quand tu auras le temps.

Encore merci a toi , ta disponibilité et tes compétences.

longue vie au forum!!

Cordialement

jcjez

Bonjour

Ces explications figurent sur la Feuille Novembre

Comme toutes les Feuilles sont la copie de la Feuille Novembre 2016, elles ont toutes la même présentation.

Toutes ces explications référent donc à la feuille Novembre 2016

3 problèmes étaient donc à règler:

  • Trouver le décalage des cellules à additionner.
  • Définir quelles feuilles étaient concernées.
  • Trouver la méthode pour les sommer.
1) Décalage de la cellule à sélectionner

Le nom de la Feuille en D1 étant au format texte, on établit l'équivalent au format Date avec

=DATE(ANNEE(D1);MOIS("1/"&D1);1) 

qui appliquée à la Feuille Novembre renvoie 01/11/2016, Date que nous retrouvons aux lignes 2 et 32 dans le tableau AB1:DG33 de chaque Feuille.

Une simple formule EQUIV indique pour le mois l'adresse de la cellule qui nous intéresse.

=EQUIV(DATE(ANNEE(D1);MOIS("1/"&D1);1);$A$32:$DG$32;0) 

donne le No de la colonne correspondant à la date recherchée, ici colonne 31.

En utilisant ce résultat on peut donc obtenir l'adrese de cette cellule sur toutes les Feuilles avec la formule en Y14 (que j'ai laissée pour information)

=ADRESSE(31;EQUIV(DATE(ANNEE(D1);MOIS("1/"&D1);1);$A$32:$DG$32;0))

Sur la Feuille Novembre 2016, elle renvoie $AE$31 en changeant simplement le NO de ligne 32 par 31.

2) Définir quelles Feuilles sont concernées pour sommer les différentes cellules $AE$31

Nettement plus difficile à appréhender.

Pour Sommer une même cellule de plusieurs feuilles, une Formule 3D du type

=SOMMEPROD(SOMME.SI(INDIRECT(Liste_des_Feuilles_Concernées&Cellule_à_Sommer);Critère;INDIRECT(Liste_des_Feuilles_Concernées&Cellule_à_Sommer)))

est requise.

Cette formule nécessite donc d'établir la liste des Feuilles concernées. La première étant toujours Octobre 2016, renseignée dans les feuilles en AB32, la dernière étant donnée par la formule EQUIV précédente (EQUIV celle de la ligne 32 correspondant au nom de la feuille Active. ici, Novembre 2016.

Les Feuilles concernées pour le calcul sont donc pour Novembre 2016, Octobre 2016 et Novembre 2016 dont la Liste pourrait être établie avec le champ AB32:AE:32

Malheureusement, les cellules fusionnées font que la formule 3D prend en considération les 3 cellules AB32,AC32 et AD32 alors que seule la cellule AB32 donne une indication du nom de la Feuille. Le résultat est donc une formule 3D renvoyant une erreur si elle est appliquée à ce champ AB32:AD32.

Pour contourner ce problème, il est nécessaire de ne retenir dans la Liste utilisée par la Formule 3D que la cellule AD32.

Pour cela, une première Formule Nommée Liste_Feuilles renvoie pour Novembre 2016 le champ AD32:AG32 (Purement virtuel)

Cette Formule Nommée Liste_Feuilles(A partir de la Feuille Novembre 2016 et dont l'étendue doit être le Classeur), est

=DECALER('Novembre 2016'!$AB$32;;;1;EQUIV(INDIRECT(ADRESSE(32;EQUIV(DATE(ANNEE('Novembre2016'!$D$1);MOIS("1/"&'Novembre 2016'!$D$1);1);
'Novembre 2016'!$A$32:$DG$32;0)));'Novembre 2016'!$AB$32:$DG$32;0))

Elle représente le Champ AB32:AG32 inapproprié pour la formule 3D

Il faut, à partir de cette Liste qui affiche Octobre 2016,"","",Novembre 2016;"";""

obtenir, Octobre 2016, Novembre 2016

Pour cela, la liste définitive est établie en colonne DM et est Nommée Liste_Onglets. Cette formule dont le choix de l'étendue doit aussi être le classeur est:

=DECALER('Novembre 2016'!$DM$2;;;SOMMEPROD(N('Novembre 2016'!$DM$2:$DM$33<>"")))

Puisque Liste_Feuilles ne représente que les valeurs de la ligne 32 du champ AB32:AG32, elle ne contient que 2 valeurs numériques qui sont 1/10/2006 et 1/11/2006

L'extraction en colonne DM se fait avec la formule (en DM2)

=SIERREUR(TEXTE(PETITE.VALEUR(Liste_Feuilles;LIGNE()-1);"mmmm aaaa");"")

et n'affiche donc que Octobre 2016 et Novembre 2016

Nous savons donc que nous devons sommer les cellules AE31 de ces deux feuilles

3) Méthode pour les sommer

La formule 3D mentionnée plus haut devient

SOMMEPROD(SOMME.SI(INDIRECT("'"&Liste_Onglets&"'!"&ADRESSE(31;EQUIV(DATE(ANNEE(D1);MOIS("1/"D1);1);$A$32:$DG$32;0)));"<>"&"";
INDIRECT("'"&Liste_Onglets&"'!"&ADRESSE(31;EQUIV(DATE(ANNEE(D1);MOIS("1/"&D1);1);$A$32:$DG$32;0)))))

Formule à laquelle vient s'ajouter le V11*15 initial

4) Remarque

Le choix de l'étendue Classeur des Formules Nommées Liste_Feuilles et Liste_Onglets fait que Excel crée le même nom pour chaque feuille en adaptant automatiquement

ces formules aux champs concernés.

Tu peux le contrôler dans le Gestionnaire de Noms à chaque création de nouvelle Feuille

Un peu difficile à suivre et consoles toi, aussi difficile à appréhender lors de la mise en oeuvre.

Cordialement

13copie-de-testjc.xlsm (141.94 Ko)

Bonjour Amadéus,

Je n'arrive pas a creer liste feuilles et liste onglets, peux tu me dire comment tu fais?

c'est la seule chose que je n'arrive pas a mettre dans une formule dans un autre classeur...

Merci

Bonjour

La marche à suivre en images dans le PDF

Cordialement

10methode.pdf (152.21 Ko)

Bonjour amadeus

J ai compris c bon

Merci à toi

Cordialement

Bonne journée

Bonsoir a toi Amadeus, je me permet de t'embeter a nouveau et j'espere que apres ca ce sera bon...

J'ai bien compris le fonctionnement du gestionnaire de noms; mais j'ai beau faire comme il faut , le résultat de la formule me donne #REF...

J'ai voulu appliquer la formule du fichier que tu m as envoyé a une version plus complete de mon fichier (j'ai rajouté des colonnes facturation dans le tableau principal et des colonnes correspondantes dans la tableau récapitulatif des primes...

Je pense que les formules DECALER( dans le gestionnaire de noms doivent en etre modifiées...mais je suis incapable de les adapter...trop complexe pour moi...

Si tu pouvais y jeter un coup d'oeil

Merci par avance

cordialement

jcjez


ci joint le fichier...

8testjc3.xlsm (288.53 Ko)

Bonjour

2 règlages effectués.

1) Formule en BC40

sur le précédent fichier, cette formule était en DM2

=SIERREUR(TEXTE(PETITE.VALEUR(Liste_Feuilles;LIGNE()-1);"mmmm aaaa");"")

Les formules PETITE.VALEUR (ou GRANDE.VALEUR) renvoient la nème valeur numérique d'un champ.

Pour avoir la plus petite, on écrit donc

=PETITE.VALEUR(Champ;1)

puis pour la seconde

=PETITE.VALEUR(Champ;2)

en ré_écrivant une nouvelle formule.

Placé en DM2, soit en ligne 2, et incrémentée vers le bas, il est plus pratique d'écrire

=PETITE.VALEUR(Champ;LIGNE()-1) et d'incrémenter.

Placée en ligne 40, la correction est donc

=SIERREUR(TEXTE(PETITE.VALEUR(Liste_Feuilles;LIGNE()-39);"mmmm aaaa");"")

incrémentée vers le bas.

2) Code de création de Feuille.

Cette ligne de Code place une espace avant le mois dans le nom d'onglet créé et provoque une erreur

 nom = Application.WorksheetFunction.Text(CDate(nom) + 31, "[$-040C] MMMM YYYY")

Je l'ai donc remplacé par

 nom = Application.WorksheetFunction.Text(CDate(nom) + 31, "[$-040C]MMMM YYYY")

Cordialement

12testjc3.xlsm (259.80 Ko)

Bonsoir Amadéus,

merci pour tout j'ai enfin pu finaliser mon logiciel...

bonne continuation a toi et longue vie au forum,

cordialement

jcjez

Rechercher des sujets similaires à "modification formule vba duplication feuille"