XL 2021 Créer Macro permettant d'indexer des nouveaux clients ? [Résolu]

[fanou06]

Bonjour,

Dans l'onglet NbRDV je calcule le nombre de RDV du client selon les mois.
J'ai mis la formule suivante :

=SOMME((TEXTE(RDV!$B$2:$B$3000;"mmmm")=NbRDV!B$1)*(RDV!$A$2:$A$3000=NbRDV!$A2))

Elle semble fonctionner.

Par contre :
Est-il possible par une macro de mettre à jour le tableau de l'onglet NbRDV au sur et à mesure de la saisie de nouveau RDV dans l'onglet RDV ?
Et de ce fait, d'incrémenter les nouveaux noms ?

Je pensais faire ainsi :

1. BDD Clients : Une fois la mise à jour faite, copier A2:A500
2. NbRDV : Copier en A2 les cellules copiées de l'onglet BDD Clients
3. Trier A:A
4. Glisser les formules.

Macro :

Sub NbRDV()
'
' NbRDV Macro
'

'
    Range("A2:A614").Select
    Selection.Copy
    Sheets("NbRDV").Select
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWorkbook.Worksheets("NbRDV").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("NbRDV").Sort.SortFields.Add2 Key:=Range("A2:A614") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("NbRDV").Sort
        .SetRange Range("A2:A614")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=66
    Range("B80:M80").Select
    Selection.AutoFill Destination:=Range("B80:M153"), Type:=xlFillDefault
    Range("B80:M153").Select
    ActiveWindow.ScrollRow = 124
    ActiveWindow.ScrollRow = 120
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 113
    ActiveWindow.ScrollRow = 111
    ActiveWindow.ScrollRow = 108
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 100
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 78
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 65
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 1
    Range("A2").Select
End Sub

Mais est-ce qu'il n'y a pas plus simple ? Et en gardant les formules ?
Car la Macro est fastidieuse.
Peut être copier la dernière ligne et copier la formule ?
Mais je pense il doit y avoir plus simple (plus propre surtout) ?


Merci.

 

[fanou06]

Bonjour,

Je vais chercher. Merci en tout cas pour l'aide
C'est ok mais toujours pas pour NbRDV2023.
Je nomme la table B1;Bxxx en t_2023 pourtant et j'ai mis la formule

SOMMEPROD((t_2023[@[Nom du client]:[Nom du client]]=t_RDV[[NOM]:[NOM]])*(MOIS(t_RDV[[DATE]:[DATE]])=COLONNE()-1))

Du coup :

=SOMME((TEXTE(RDV!$B$2:$B$2996;"mmmm")=NbRDV2023!C$1)*(RDV!$A$2:$A$2996=NbRDV2023!$A8))

fonctionne. Mais pas de possibilité de classer par ordre alphabétique.

En fait, quand je rentre avec la macro un nouveau client, donc en A86.
Les formules allant de A2 à A85 sont OK.
Mais une fois le tri fait, A86 est par exemple en A83.
Les formules AVANT A86 sont OK, mais à partir de A83 sont erronées.
Celle de A86 donne un calcul sur A84.

De plus, impossible d'effacer certaines tableau, c'est en grisé (t_2023 par exemple) : cf.photo :

Merci pour votre aide.

 

[vgendron]

Hello,
bon. y a un moment, faut arrêter de faire tout et n'importe quoi !
dans la dernière PJ que tu as mise, ce n'EST PAS celle que je t'ai donnée !!
tu n'a pas gardé les tables de la feuille BDD, la table des catégories s'appelle encore "tableau15, alors que je l'avais renommée "t_Catégories"
idem pour les mois et box

Dans la feuille "Formulaire"
tu persistes à définir les listes de validation avec du "='BDD CLIENTS'!$A:$A"
ce qui prend TOUTE la colonne.. donc avec 1 000 000 de lignes vides ==> AUCUN intérêt
moi je t'avais mis des listes de validation à base de =indirect("t_Nom") ou indirect("t_Tarifs").....

Dans la feuille "NbRDV2023"
tu persisites à mettre tes formules à base de Somme(texte.....), alors qu'il y avait des formules à base de sommeprod..

donc
soit tu reprends ce que je t'ai donné
soit tu fais ce que tu veux, mais tu arretes de dire que ca ne marche pas.. c'est ce que TU fais qui ne marche pas!

tu as défini une plage nommée (CE N'EST PAS UNE TABLE STRUCTUREE... rien à voir)
elle s'appelle "t_formulaire"
et sa définition
=Formulaire!$1:$1048576 ==> ca veut dire TOUTE la feuille.. à quoi ca sert??

et pour les macros.. ce ne sont PAS celles que j'ai écrites..
pourquoi demander de l'aide et ne pas en tenir compte ?

 

[fanou06]

Simplement que lorsque je copie de tableau final vers anonyme j'ai des tables qui se copies et qui sont eronnées. Pour ça que je cherche à changer les noms de certains tableaux et tables.

Je n'arrive pas à avoir les résultats quand je fait du final vers anonyme.
J'ai donc recopié les macros vers le final. Et elles ne fonctionnaient pas. Bug aussi bien sur le final que sur l'anonyme quand je copie les cellules du final vers anonyme. Sinon je ne prendrais pas la peine de demander de l'aide.

Bref on n'a pas tous la science infuse et je suis désolé d'avoir pris de votre temps.
Je vais regarder tes formules et regarder des tuto pour voir comment renommer les tableaux et tables car 'cf.photo ci-dessus' je n'y parviens pas.

Et pour info je n'ai pas renommé en tableau 15 puisque je ne sais pas faire cela . . . Je viens de tester et je ne peux nomer tableau 15 en t_Catégories

Merci en tout cas.

 

[vgendron]

Je t'ai donné tous les éléments pour utiliser mon dernier fichier "Anonymisé" avec les macros corrigées

tu as JUSTE des copier + coller special VALEUR à faire
pas de modif de macro
pas de modif de nom de table
pas de création de table

j'ai meme mis des commentaires pour expliquer le fonctionnement des macros..
je ne vois honnetement pas ce que je peux faire de plus

 

[fanou06]

Merci.

Je vais tout reprendre.
J'ai fait un erreur en recopiant en fait tout un onglet

Je laisse tomber. La formule

SOMMEPROD((t_2023[@[Nom du client]:[Nom du client]]=t_RDV[[NOM]:[NOM]])*(MOIS(t_RDV[[DATE]:[DATE]])=COLONNE()-1))

Renvoie une erreur alors que les tables sont bien nommées.
Je vais faire à l'ancienne et utiliser la fonction poignée pour les calcules sur le tableau NbRDV.
N'empêche que le tri de ce tableau ne s'effectue pas. Même avec Anonyme.


Merci en tout cas pour l'aide.

 

[fanou06]

C'est bon j'ai tout renommé comme tu m'as dit.
Tout est OK, je regarde pour le tri sur NbRDV2023 je l'ai renommé RDV2023 et changé le nom dans les macros Je vais y arriver. J'ai compris mon erreur et désolé encore....

 

[vgendron]

pour le tri

je viens de voir que le tri se faisait sur le NOM (ok) et l'heure (colonne 3) au lieu de la date (colonne2)
==>à corriger avec ceci.. juste un changement d'indice..

Sub TriDate() ' TriDate Macro
    With ActiveWorkbook.Worksheets("RDV").ListObjects("t_RDV") 'avec la table "t_RDV" de a feuille "RDV
        .Sort.SortFields.Clear 'on supprime les tris en cours
        .Sort.SortFields.Add2 Key:=.ListColumns(1).DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'on ajoute un tri sur la colonne 1 SANS prendre la ligne d'entete
        .Sort.SortFields.Add2 Key:=.ListColumns(2).DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'on ajoute un tri sur la colonne 2 SANS prendre la ligne d'entete
        With .Sort 'on applique le tri
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

pour le tri de NbRDV2023:
chez moi, ce code fonctionne parfaitement

Sub Tri2023() ' TriDate Macro
    With ActiveWorkbook.Worksheets("NbRDV2023").ListObjects("t_2023")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 Key:=.ListColumns(1).DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

 

[fanou06]

Pour "tridate" c'est pas par nom mais par date et heure dans l'onglet RDV. Mais ça marche. Je change l'ordre des colonnes merci.

L'autre je vais tester.
Par contre Macro Tri2023 je ne peux que CREER et non modifier ou effacer. Et quand je fais créer rien ne se passe.

Merci.

 

[fanou06]

Alors ça fonctionne pour le tri.
Par contre avec sommeprod ca décale encore les résultats à partir de la ligne insérée automatiquement.
Je teste avec Somme(Texte( et c'est pareil.
Pourtant les tables sont correctes. Les macros marchent. Mais pas le fait de calculer automatiquement le Nombre de RDV

J'ai renommé des entêtes et donc modifié le SommeProd :

SOMMEPROD((t_2023[@[Nom du client]:[Nom du client]]=t_RDV[[Nom du client]:[Nom du client]])*(MOIS(t_RDV[[Date]:[Date]])=COLONNE()-1))

Le souci est peut être du au nom du tableau t_RDV ?
Mais impossible de modifier ou supprimer son nom :

Tout le reste est ok, les validations de données reprises avec tes conseils.


Merci

 

[vgendron]

Je ne vois pas à quel moment tu as ce décalage??
sur ton image, je vois que la formule a une erreur #NOM?
as tu bien recopié la formule?

 

[fanou06]

Je ne vois pas à quel moment tu as ce décalage??
sur ton image, je vois que la formule a une erreur #NOM?
as tu bien recopié la formule?

Oui, j'ai bien copié la formule.
Par exemple sur la photo; ffffffffffffffff n'a aucun RDV et le somme prod en affecte 3-4-2-2 . . .
Les formules se copient bien mais quand on trie les données par ordre alphabétique, toutes les formules se trouvant après la dernière entrée sont fausses. Celles avant le dernier nom sont OK.
Et de plus, impossible de renommer t_RDV ou de le supprimer (Voir photo). Car peut être là le soucis ?

Sinon j'ai tout recopié garce à ton aide sans souci.


merci encore.

 

[vgendron]

MAIS POURQUOI veux tu supprimer le nom de la table !!!

la formule fait appel à ce nom.. donc si tu supprimes la table, la formule ne marche plus
si tu as changé le nom de la table, il faut AUSSI changer le nom DANS la formule

tu as 1176 lignes de rendez vous??

 

[fanou06]

Au fait un énorme merci, je n'utilise plus que ta formule pour la validation des données :

Elle est plus claire, pro et je pense moins "gourmande".


Non, je n'ai pas changé la table.
C'était pour 'réinitialiser' au besoin.
Dans RDV (t_RDV) j'ai 478 entrées pour le moment. mais la formule n'indique pas du coup les lignes ?


Merci

 

[vgendron]

dans ton gestionnaire de noms je vois encore des plages qui n'ont aucun sens!
BDD = BBB!1:1048576 ==> tu définis une plage nommée "BDD" qui correspond à TOUTE la feuille BDD
ca ne sert à RIEN !

idem pour t_dépenses, t_formulaire, t_déptet, ....

1) un nom qui commence par le préfixe "t_" c'est juste une convention pour signifier qu'on a à faire à une table structurée

2) définir une plage nommée a pour intérêt de LIMITER une plage de données
ex: dans ta maison, il y a plusieurs pièces
à Pâques, si je te dis avoir caché UN oeuf dans la maison.(= le classeur entier). tu vas devoir chercher partout dans la maison. ca va te prendre des plombes
si je te dis l'avoir caché dans la salle à manger (= la plage nommée) ==> ca va etre plus simple, et plus rapide


3) autres plages qui n'a pas de sens
t_catégorie = t_catégories[Catégories]
ca ne sert à rien de donner un nom à la colonne "Catégories" de la table "t_Catégories"

 

[fanou06]

Merci en tout cas de l'aide.
J'aide cette association à but non lucratif mais c'est vrai j'ai voulu me lancer dans des macros et les tuto (et ton aide) m'ont permis de la lecture (formulaires etc . . . ) mais encore des points que je dois travailler . . .