Xl/Vba: Alerte sur critères TCD

Fermé
7808622H Messages postés 292 Date d'inscription samedi 23 février 2008 Statut Membre Dernière intervention 19 mai 2018 - 19 sept. 2012 à 17:20
eriiic Messages postés 24603 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 15 décembre 2024 - 30 sept. 2012 à 17:11
Bonjour


Je vous joins ci apres un fichier pour lequel j'aurai besoin de pistes, voir eventuellement de code.

Le principe de ce fichier est le suivant:

Nous recevons des demandes de paiement papier et les inserons dans ce fichier pour assurer un suivi

Le souci que j'ai est que pour passer par ces modes de règlements manuels , il faut que le fournisseur ai au maximum de 6 factures dans l'année et que le montant total de ces factures n'excède pas 10000€ ( meme si il y a moins de 6 factures)

J'ai pensé a un TCD pour faire l'analyse par fournisseur pour obtenir la somme du nombre de facture( qui correspond à la somme de la colonne H de l'onglet SUIVI) et le montant total des factures (somem de la colonne I de l'onglet SUIVI)

Le probleme est que cela implique une actualisation systématique du TCD pour vérifier les critères et chercher dans le TCD ( celui de démo joint plus bas reprend juste quelques info de base la liste est en realité beaucoup plus longue avec un grand nombre de fournisseur


Le but recherché idéalement, serait que Excel nous alerte lorsque l'on procède a la saisie si un même fournisseur atteint les 6 factures ( cumul des Nb ) ou si le montant dépasse les 10000€ à l'aide d'un message d'alerte type :

"Attention le fournisseur a dépassé le nombre de facture autorisé pour le PLR. Faire une demande de référencement"
et / ou
"Attention le fournisseur a dépassé le montant maximul de règlement autorisé pour le PLR. Faire une demande de référencement"

ou meme plus simplement quelque soit la raison :
"Attention le fournisseur n'est plus éligible au PLR. Veuillez procéder à son référencement"


La contrainte supplémentaire, au vu du TCD c'est que cette analyse et les alertes ne doit paraitre que lorsque l'on utilise un paiement a un fournisseur ( donc dans Type PLR de l'onglet suivi, uniquement lorsque Règlt Fournisseur est sélectionné)


Et là j'avoue que je n'ai aucune idée de comment gérer le truc, mes compétences en Vba sont pas aussi évoluées

Si quelqu'un pouvait me donner des pistes, et eventuellement des idées de codes pour que je puisse résoudre ce problème , ca serait sympa


Merci d'avance a ceux qui se pencheront sur le sujet pour m'aider


Bonne journée


Fichier Test : https://www.cjoint.com/?0ItroMpQbNo


Contrainte a prendre en compte : je suis sous excel 2002



A voir également:

5 réponses

7808622H Messages postés 292 Date d'inscription samedi 23 février 2008 Statut Membre Dernière intervention 19 mai 2018 4
22 sept. 2012 à 14:06
bonjour tout le monde,

bon je sais que remonter le sujet est pas tip top, mais j'ai beau faire des recherches a droite a gauche, j'arrive a rien , donc si y'a une bonne âme super doué en Vba qui peut m'aider .....

Bon week end
0
eriiic Messages postés 24603 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 15 décembre 2024 7 249
Modifié par eriiic le 23/09/2012 à 17:07
Bonjour,

J'ai vu ton appel au secours ;-)
On peut faire une fonction personnalisée.

Seulement si tu veux vraiment une mise à jour au fil de l'eau il faut la faire 'volatile' et elle est réévaluée à chaque saisie dans la feuille.
Si tu as beaucoup de ligne ça risque d'être gourmand en ressources. Si c'est le cas dis-le et on fera plutôt un sub que tu appelleras au coup par coup pour faire la mise à jour.
Ou bien tu établis une liste des fournisseurs dans Données et met une seule fois la fonction à coté. La mise à jour sera à faire manuellement (ou une petite macro mais bon, il n'y a pas grand chose à ajouter) en cas d'ajout de fournisseur, et tu récupères le PLR du fournisseur par un recherchev(). Ca allègera considérablement les recalculs.

Function PLR(Fournisseur As String) As Boolean     
    Dim lig As Long, tabl As Variant     
    Dim nbFact As Long, montant As Double     
    Application.Volatile     
    tabl = Worksheets("SUIVI").Range("E5:I" & Worksheets("SUIVI").Cells(Rows.Count, 7).End(xlUp).Row)     
    For lig = 1 To UBound(tabl)     
        If tabl(lig, 1) = "Règlt Fournisseur" And tabl(lig, 3) = Fournisseur Then     
            nbFact = nbFact + tabl(lig, 4)     
            montant = montant + tabl(lig, 5)     
        End If     
    Next lig     
    PLR = nbFact > 0 And nbFact <= 6 And montant <= 10000     
End Function

Syntaxe : = PLR("nom_du_fournisseur")
Ex: = PLR(G5)
J'adresse directement les colonnes dans SUIVI, si elles bougent il faut adapter la macro.

Après tu l'utilises comme tu veux : soit une colonne dédiée ou bien dans une MFC.

Evite la fusion des colonnes, ça fout toujours le bazar. J'ai dû te mettre le titre 'centré sur plusieurs colonnes' pour que la MFC fonctionne.
https://www.cjoint.com/?BIxq1cQdRM3

eric

PS: j'ai supposé que nb était un nombre de factures...

Jamais tu ne répondras à un mp non sollicité...
Bon, ça c'est fait.
0
7808622H Messages postés 292 Date d'inscription samedi 23 février 2008 Statut Membre Dernière intervention 19 mai 2018 4
23 sept. 2012 à 18:36
Bonjour Eric et merci de m'aider ( c'ets pas la premiere fois en plus ;-) ))


Concernant le quantitatif de données varie en effet, si je me base sur ce que j'ai deja cette année , j'ai en gros 700 lignes excel dans le tableau


Par contre en reprenant ton fichier j'ai essayer de tester de remettre des lignes et soit j'ai pas pigé le fonctionnement soit je suis totalement blond ( bon ok c est le cas)

Nb est en effet le nombre de facture, mais c est le cumul qui ne doit pas depasser 6 par an et ou le cumul du montant qui ne doit pas depasser 10 000 €


En janvier on peut avoir 1 facture APF de 2000 en juillet 3 autres facture qui feraient au total 8500 ( mais la colonne nb est saisie manuellement ca n'est pas un cumul, juste la saisie du jour )

Dans ce cas je devrais avoir une alerte

Je t'explique le contexte générale, dansm a boite on travaille avec un systeme informatique qui a sa base fournisseur

Afi nde ne pas alourdir le ficheir fournisseur on accepte de payer, par chèque ou virement manuel ( c'est un autre systeme informatique qui gère) les fournisseurs occasionnels a concurrence de 6 factures maximales ou 10 000€ maximum par an

Si ces critere sont depassés on doit referencer le fournisseur dans la base

voilou est ce que cela t'aide un peu ?

J'ai acces a CCM au bureau ainsi qu'a cjoint, et vu que j ai franchement pas grand chose a faire en ce moment je pourrais voir regulierement si tu reponds tardivement



0
eriiic Messages postés 24603 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 15 décembre 2024 7 249
Modifié par eriiic le 23/09/2012 à 18:56
re,

Nb est en effet le nombre de facture, mais c est le cumul qui ne doit pas depasser 6 par an et ou le cumul du montant qui ne doit pas depasser 10 000 €
C'est bien ce que j'ai fait dans le classeur exemple.
Tu as une formule qui ramène VRAI/FAUX en K + une MFC en G.
En ligne 9 tu vois APF en vert et VRAI en K. Si tu changes le montant ou mets nb>6 (ou si tu ajoutes une autre ligne APF) : plus de vert et FAUX.

Si tu as 700 lignes fais plutôt ce que je t'avais indiqué :
Ou bien tu établis une liste des fournisseurs dans Données et met une seule fois la fonction à coté....etc
Mais essaie de le marcher correctement avant
eric
0
7808622H Messages postés 292 Date d'inscription samedi 23 février 2008 Statut Membre Dernière intervention 19 mai 2018 4
23 sept. 2012 à 19:04
ca marche je vais retenter, je dois m absenter mais je m'en occupe en rentrant ou demain au taf sans faute

merci encore je te tiens au courant
0
7808622H Messages postés 292 Date d'inscription samedi 23 février 2008 Statut Membre Dernière intervention 19 mai 2018 4
23 sept. 2012 à 21:48
Re bonsoir Eric

alors ce que j'ai pu constater si je ne me trompe pas ( arrête moi si c'est le cas )

- lors des saisies tant que les critères ne sont pas atteint, le nom du fournisseur passe en vert
- Dès que les critères sont atteint la couleur de fond disparait ( d'ailleur pour le nombre de facture c'est supérieur a 6 j'ai fait la modif dans le code et modifié en inferieur a 7)


Bon par contre ca m'embête un peu, car du coup la mise en évidence revient justement a ne rien y avoir comme trame de fond. Ca passe un peu inaperçu avec les remboursements agents par exemple.

Donc là ca fait justement l'inverse de ce que je cherche :
- ca passe en vert quand c'est bon et ca n'active rien quand c'est faux


De plus pourrais tu m'expliquer la colonne K ?? je n'arrive pas a identifier "=plr", je vois bien que ca renvoi au nom de fournisseur de la ligne, mais je ne saisie pas


A la limite, je préfère encore un petit bouton a cliquerr une fois les saisies terminées, mais qui me ferai vraiment un message d'erreur qui s'affiche, là en l'état, c'est juste l'inverse de ce que je cherchais..

Bon je sais suis chiant , par contre dans le fichier que j'ai envoyé, j'ai juste un tit souci, normalement la colonne k devrait etre utilisée pour indiquer une date de paiement ( j avais pas mis sur le fichier demo, je pensais pas que ca serait génant), par contre c'est totalement libre après


Donc voilou mes petites constatations

je te rejoins le fichier avec la modif sur le critere du +6 enfin du <7

https://www.cjoint.com/?0IxvUKQeAUO


Merci d'avance

Bonne soirée

Céd

--
0
eriiic Messages postés 24603 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 15 décembre 2024 7 249
23 sept. 2012 à 23:17
Je t'avais mis la MFC et la colonne K à titre d'exemple, et tu pouvais inverser le test de la MFC si tu voulais l'inverse.
Ce qui compte c'est la fonction personnalisée PLR().
=PLR(fournisseur) appelle cette fonction personnalisée, de la même façon que tu pourrais appeler une fonction native d'excel comme SIN() et obtenir son résultat.

Plutôt que d'inverser le test dans la MFC j'ai inversé la valeur retournée par la fonction pour raccourcir la formule de la MFC
Par contre, je viens de tester, on est obligé de garder une colonne intermédiaire. La MFC ne se met pas à jour sur une fonction volatile...
J'ai pris L. Tu peux la masquer, mais un #N/A en retour te permet de voir qu'il manque un fournisseur dans la liste.
J'ai modifié ton classeur en ajoutant la liste des fournisseurs dans 'Données' et leur PLR.

Et puis pas de différence entre <=6 et <7 à moins que tu ne puisses avoir 6,5 factures (?).
De toute façon tous ces tests ont été inversés.

Regarde si ça te va.
https://www.cjoint.com/?BIxxjitMSSA
Au passage APF n'apparait pas en vert car tu n'avais pas mis Règlt Fournisseur en ligne 6.

eric
0
7808622H Messages postés 292 Date d'inscription samedi 23 février 2008 Statut Membre Dernière intervention 19 mai 2018 4
24 sept. 2012 à 21:27
Bonsoir Eric

bon j'ai commencé a faire la mise a jour du fichier au bureau, mais honnetement ca rame pas mal, le tableau se recalcul regulierement, et ca prend pas mal de temps , est ce normal ? ( je devrais avoir fini demain, je le reposterai pour que tu vois)
0
eriiic Messages postés 24603 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 15 décembre 2024 7 249
24 sept. 2012 à 21:42
Bonsoir,

il y avait des chances que ça fasse ça, mais difficile de se rendre compte de la réalité sans le fichier.
Il vaudrait mieux prévoir la saisie, puis la mise à jour manuelle.
eric
0
eriiic Messages postés 24603 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 15 décembre 2024 7 249
27 sept. 2012 à 16:05
J'ai supprimé ton message à cause des données non anonymisées, du coup j'ai supprimé ma réponse aussi...
J'ai sauvegardé ton fichier.

Oui, c'est insupportable, je regarde ce que je peux faire mais il faudra un peu de réflexion...

eric
0
eriiic Messages postés 24603 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 15 décembre 2024 7 249
Modifié par eriiic le 27/09/2012 à 20:42
Re,

C'est quoi ça en I155: 10000SEK ?
Une chaine fait tout bloquer, je ne peux traiter que des numériques.
Faut-il que je filtre (ça rajoute un traitement) ou c'est une erreur qui n'arrivera jamais ?
J'ai transformé en €...
eric
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
7808622H Messages postés 292 Date d'inscription samedi 23 février 2008 Statut Membre Dernière intervention 19 mai 2018 4
30 sept. 2012 à 16:15
Bonjour Eric


alors mes constats sur le fichiers

- En premier lieu j'ai modifié pour plus de clarté la liste des fournisseurs en mettant des numerotations ca sera plus simple pour s'y retrouver pour monter le fichier
- Comme je ne sais pas exactement ou pointent les formules et codes, penses tu qu'il serait possible de faire une feuille dédiée pour les fournisseurs ?

Concernant les rsesultats des tests que j'ai fait :

-régulièrement la colonne L laisse apparaitre des N/A a la place des VRAI / FAUX
-Si tu filtre sur Agent-001 qui ne devrait pas pointer en rouge, tu verras que certains le sont (ligne 9 et d'autres selon les réouvertures de fichier)
-Si tu filtre sur fournisseur-0001 , j'ai un nombre de nb supérieur a 6 et 2 lignes sur 4 se mettent en rouge alors qu'elle pointent en FAUX, et les deux lignes qui pointent en vrai reste en blanc alors qu'elles devraient etre toutes les 4 en rouge


Voila a vu de nez c'est ce que j'ai pu voir ... j'espère que cela te donneras les pistes necessaires

Ci joint le fichier utilisé : https://www.cjoint.com/?3IEqouDxjVH

Bon courage et merci

Céd
0
eriiic Messages postés 24603 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 15 décembre 2024 7 249
Modifié par eriiic le 30/09/2012 à 17:18
Bonjour,

La liste des fournisseur est crée par la macro, adapter la macro si tu la déplaces. Ne pas la modifier à la main.

La seule chose qui manque éventuellement est de la trier dans la macro avant de la coller si tu veux mettre une liste déroulante ordonnée sur ta feuille. Si les filtres sont enlevés et la feuille triée par fournisseurs avant d'appeler la macro ils seront triés.

-régulièrement la colonne L laisse apparaitre des N/A a la place des VRAI / FAUX
N/A en ligne 43 : en L: =RECHERCHEV(G1;FOURPLR;2;FAUX) au lieu de =RECHERCHEV(G43;FOURPLR;2;FAUX)

-Si tu filtre sur fournisseur-0001 , j'ai un nombre de nb supérieur a 6 et 2 lignes sur 4 se mettent en rouge alors qu'elle pointent en FAUX
Revois ta MFC, elle est toute décalée.
Par ex sur la ligne 23: formule de MFC: =L65 au lieu de =L23

Tu as dû faire des insertions/suppressions malheureuses.
Tu peux remarquer que le fichier que je t'ai fourni était correct de ce coté là.

Dans ce cas enlève tous les filtres, remet la MFC sur G5:Gxxx avec =L5, et recopie la formule en G5 vers le bas pour rétablir.

eric
0