XL 2016 comment ecrire formule par macro

[grisouille]

Bonjour à tous,

Je travaille actuellement sur un fichier assez complexe pour lequel j'ai besoin de votre expertise.
Sur le premier onglet (Recap) je rentre plusieurs informations concernant ma généalogie.
Sur le deuxième onglet (Naissance) j'effectue des calculs dans les colonnes G-H-J-L.
Dans ces formules je suis obligé de faire un saut de référence après le premier "EQUIV" . Voir formules ci-dessous dans les colonnes G3 et G4.
G3 =SI(Recap!$D3="";"";diffdates(INDEX(Recap!$D$3:$D$17;EQUIV($M4;Recap!$S$3:$S$17;0));INDEX(Recap!$D$3:$D$17;EQUIV($M3;Recap!$S$3:$S$17;0));1))

G4 =SI(Recap!$D4="";"";diffdates(INDEX(Recap!$D$3:$D$17;EQUIV($M6;Recap!$S$3:$S$17;0));INDEX(Recap!$D$3:$D$17;EQUIV($M4;Recap!$S$3:$S$17;0));1))

Le fichier à l'origine comportant 15000 lignes, je souhaiterai par une macro remplir automatiquement toutes les cellules.
Je ne me vois pas écrire les formules dans chaque ligne pour les colonnes G-H-J-L !
Pour plus de clarté voir le fichier !
Merci pour votre aide.

 

[vgendron]

Bonjour
Dans ta feuille Naissance, pour chaque ligne (AA PP / BB QQ / CC RR .....), comment sait on qui est le père et la mère?
cette info est necessaire pour savoir quelles cellules il faut prendre dans ta formule
dans ton fichier, pour AAPP, il faut prendre equiv M3 et equiv M4
intuitivement, j'aurai dis pour BBQQ il faut prendre M5 et M6 , visiblement, c'est pas le cas.;

Au final, tu souhaites une solution avec formule (ce qui risque d'etre long en calculs pour 15 000 lignes) ou une macro qui te donne directement le résultat?

 

[vgendron]

Ci jointe proposition avec macro
1) j'ai écrit une fonction DiffDates2 (sur la meme base que l'existante) mais en enlevant certains test qui faisaient bugger==> problème de format car j'appelle la fonction avec les dates, non avec les cellules qui contiennent les dates

2) je colle le résultat dans la feuille Re (c'était pour garder tes data d'origine et pouvoir les comparer


et une remarque.. je ne comprend pas comment tu calcules l'age du père et de la mère...
le calcul que tu fais (date naissance Père - date naissance enfant) = age du père au moment de la naissance de l'enfant..

 

[grisouille]

Ci jointe proposition avec macro
1) j'ai écrit une fonction DiffDates2 (sur la meme base que l'existante) mais en enlevant certains test qui faisaient bugger==> problème de format car j'appelle la fonction avec les dates, non avec les cellules qui contiennent les dates

2) je colle le résultat dans la feuille Re (c'était pour garder tes data d'origine et pouvoir les comparer


et une remarque.. je ne comprend pas comment tu calcules l'age du père et de la mère...
le calcul que tu fais (date naissance Père - date naissance enfant) = age du père au moment de la naissance de l'enfant..

 

[vgendron]

euh.. il y a un message ? :-D

 

[grisouille]

Merci à vgendron pour votre réponse.
Le N° 1 a comme parents les N° 2&3
Le N° 2 a comme parents les N° 4&5
Le N° 3 a comme parents les N° 6&7
Et ainsi de suite.
Je calcule l'âge des parents à la naissance de leur enfant.

Au départ je pensais à une macro pour écrire les formules mais si la macro donne directement le résultat, cela me convient.
Maintenant j'ai rajouté les N° 16&17 pour le N° 8. La macro a bien inscrit l'âge des parents pour le N°8 sur l'onglet Re.
Par contre les colonnes J & L ne se sont pas remplies.
Voir le fichier ci-joint.
Merci

 

[vgendron]

Voir PJ

les tableaux sont devenus des Tables ==> plus simple pour récupérer les données d'une feuille à l'autre
j'ai ajouté une colonne "NOM Complet" ==> plus pratique que de devoir "recalculer" à chaque fois
au pire. si elle gêne à la lecture, elle peut etre masquée

la feuille "Recap" ne sert finalement pas à grand chose pour ce qui nous concerne en ce moment
la feuille Naissance recopie les données de la feuille Récap
j'ai donc bien suivi la logique "Enfant ligne i ==> Père ligne 2*i et Mère en ligne 2*1+1

pour les colonnes Père et Mère: tu calcules quoi exactement??
dans le fichier envoyé, il y a une formule xyeardate avec un autre fichier extérieur..

 

[grisouille]

Les colonnes J & L me servent après pour faire un graphique. Je ne prends en compte que le nb d'années de l' âge des parents au moment de la naissance de leur enfant..
Les formules sont tirées de l'onglet Recap. C'est le même principe que pour les colonnes G & H.

 

[vgendron]

ok, suffit donc juste de récupérer le nombre d'années qu'on vient de calculer pour les colonne H et I
voir PJ

 

[grisouille]

Bonjour,
Je reprends le fil de la discussion.
J'ai inséré de nouvelles lignes dans l'onglet "Recap". J'ai omis volontairement certaines dates de naissance.
Lorsque je lance la macro, dans l'onglet "Naissance" apparaissent des valeurs dans la colonne E des dates.
Ces cellules doivent rester vierges puisqu'il n'y a pas de date dans l'onglet "Recap". De plus les colonnes H-I-K-M font apparaître des informations jusqu'à la ligne 15.
A quoi l'onglet "Naissance" vous sert-il ?
Dans l'onglet "Re", je retrouve les mêmes anomalies. Je les ai mises en rouge gras. Ces cellules ne doivent avoir aucune indication.
Par contre la macro prend bien en compte toutes les lignes.

 

[vgendron]

Hello

correction pour ne pas remplir quand il n'y a pas de date dans Recap
la feuile Re ne servait que pour les tests ==> je l'ai supprimée, et je remplis directement la feuille Naissance

Question: es tu sure qu'une personne aura TOUJOURS son père et sa mère en dessous de lui?
ca me semble bizarre.. ne serait ce pas plus "simple" d'ajouter deux colonnes (Nom du Père et Nom de la Mère) dans l'onglet Recap?

 

[grisouille]

Bonjour,

Je reprends la discussion.
Merci pour votre suggestion.
J'ai repris le fichier en supprimant les informations pour certains individus (père et/ou mère).
Voir N°14 et N°28 à 31. Cela fonctionne très bien.
Je ne sais pas si le fait de rajouter les 2 colonnes dans l'onglet Recap me soit utile. Je ne comprends pas bien la manip.

 

[vgendron]

l'idée est la suivante:

le numéro 1 a pour parents 2 et 3
le numéro 2 a pour parents: 4 et 5
..
le numéro 20 a pour parents: 40 et 41
à chaque fois, on doit décaler *2 et *2+1
mais que se passe t il si un jour il manque un des parents, ou s'il n'est pas la bonne place?
ex: numéro 1 a pour parents 2 et 3
numéro 2 a pour parents: 4 et 6 (quelqu'un s'est incrusté en ligne5)
La macro n'en verra rien et elle te donnera des résultats érronés..

par contre
si en face de chaque numéro, on indique directement le nom du père et de la mère, le code se "contenterait"
d'aller chercher les infos du père et de la mère quelque soit leur place dans le tableau récap..
en plus.. il me semble que ce serait plus "visuel" d'avoir le nom des parents directement à coté..
mais, c'est juste un avis personnel,
si ca te convient tel quel, pas de raison de changer.

 

[grisouille]

Les numéros correspondent à ce que l'on appelle en généalogie la numérotation Sosa.
C'est un numéro unique qui permet d'identifier chaque ancêtre. On attribue les N° pairs à tous les hommes et les N° impairs à toutes les femmes. Maintenant si c'est pas compliqué de rajouter les colonnes pourquoi pas.
En tout cas merci beaucoup pour cette aide ! Je me voyais mal embarqué avec mes formules.
Il ne me reste plus qu'a appliquer cette méthode à mon fichier d'origine !