Bonjour mes ami(e)s,

 

J'ai un problème d'exécution automatique de macro sur Excel 2007 pour ma base de données de suivi des Producteurs du Cacao.

pour dire vrai je suis débutant sur les macros pourtant qui sont nécessaire pour ma base de données

 

1- chaque département à des Axes Ex: Axe1,2,etc....... et chaque Axe à des villages Exp: village1,village2, etc....

je veux qu'on choisissant un Département sur la colonne A que ceci donne automatiquement la liste de ces Axes pour faire un choix et une fois l'Axe choisi parmi tant d'autres sur le menu déroulant dans B, automatiquement dans E la liste des village de cet Axe apparait.

2- les numéros des Producteurs dans F doivent se créer de façon automatique pour éviter les doublons suivant les Axes.

Le N° de l'axe est suivi des deux initiales qui composent le nom de l'axe,
Ex: Axe Ouesso/Sembé le 1er producteur enregistré doit avoir comme code 0001os et le second 0002os quelque soit le village et appareil pour les autres Axes.

Donc si l'axe Ouesso/Sembé à pour code du début 0001os
l'axe Sembé/Souanké aura pour code 0001ss
mais les codes qui doivent se créer automatiquement c'est ceux des producteurs dans un axe:

si le 0001ss est attribué au 1er producteur
donc 2ème producteur doit avoir 0002ss et le 3ème 0003ss.

Pour plus d'éclaircissement je vous joins le fichier Excel avec quelques annotations sur la feuille.

Bonjour eudocha, bonjour à tous,

 

Cela n'engage que moi mais je trouve dommage d'alourdir ton fichier de VBA alors que des listes de validations de données peuvent suffire. Du coup, j'ai bien conscience de ne pas répondre à ta question VBA mais au cas où cela puisse t'intéresser quand même je te propose une piste sans VBA.

 

Dans ton fichier joint tu trouveras un onglet "Tables" regroupant les nomenclatures, puis la liste des zones nommées dans le gestionnaire de noms, les validations de données sont dans les colonnes couleur cacao ;) .

 

Tu trouveras un peu de VBA quand même dans le code de la feuille, mais simplement pour réinitialiser les cellules dépendantes en cas de modification colonne 1 ou colonne 2.

 

Pour la création des producteurs je ne suis pas sûre d'avoir compris, j'ai juste placé une validation de données dans l'onglet Tables (colonne S) qui interdira la saisie de doublons.

 

Vois ci cela peut t'intéresser, mais tu auras sûrement d'autres réponses plus proches de ta question initiale.

 

Très bonne soirée,

 

Mth

Bonjour Mth,

 

 je l'avoue que tu es formidable et te remercie pour l'attention que vous avez accorder à ma requête.

 Cette approche de solution est bonne et j'essaie ça marche bien, il reste juste la question des N° des producteurs. Et je comprends que c'est moi qui avait mal présenté la question.

 

 En fait, un Axe a un n° suivi des deux initiales du nom de cet axe comme vous l'avez constaté puisque vous avez réussi avec brio de reproduire ça dans la colonne F.

 donc le 0001os est attribué au premier producteur du Axe puisque la codification doit se faire uniquement par Axe peu importe les villages des producteurs ;

 

 sur l'Axe oeusso/sembé le 1er producteur enregistré aura 0001suivi des o et s qui sont des initiale respective de ouesso et sembé. Et le 2ème son n° doit être attribuer automatiquement 0002os et etc ......

hors pour l'instant quand je choisi le même Axe, le code ne change pas au lieu de 0002os il donne 0001os.

 Dans un village en peut avoir plusieurs producteurs mais les n° doivent suivre l'ordre selon l'axe

 

Ex:  Axe oeusso/sembé - village Goa - n°0001os pour le 1er Producteur

       -//-                    village Goa - n°0002os pour le 2ème Producteur

       -//-                     village Biessi -n°0003os pour le 3ème Producteur

  Axe sembé/ngbala-1 - village Mickel - n°0001sn1

        -//-                        village Dia1   - n°0002snl

 

 Et enfin dans la colonne T je veux que les deux possibilités de calcul puise être intégré dans une seule formule.  Soit c'est O6*S6 ou O6*R6 selon la saisie pour déterminer le  Tolal gain

 

Encore merci grand merci à vous

 

Eudocha

Bonjour Eudocha, bonjour le forum,

 

Merci Eudocha pour tes explications, concernant ce numéro de producteur tu trouveras une approche parmi d'autres dans le fichier joint.

J'ai créé un autre onglet "TableProducteurs", permettant d'ajouter un producteur pour les axes, l'axe se choisit colonne D dans une liste déroulante, la formule colonne E permet de lui attribuer son numéro sans doublon.

J'ai placé du code dans la feuille pour trier cette liste lors de chaque ajout ou modification colonne D, la liste triée se place à coté colonne I et c'est cette liste que j'utilise dans l'onglet principal pour la liste déroulante de la colonne F.

 

Concernant le calcul colonne T, j'ai cru comprendre qu'il y avait deux cas de figure Sacs ou Kilos, et que les colonnes n'étaient donc pas toutes remplies en même temps d'où une formule toute simple.

 

Vois si cela peut t'aider sinon dis nous pour que l'on cherche autre chose.

 

Bien à toi,

 

Mth

Bonsoir Mth, ainsi qu'à tout les membres

 

Excuse moi pour l'interruption c'est du à un problème depuis hier et c'est ce matin que cela c'est résolu réseau,  je ne saurais te remercier pour l'aide incessante que tu m'apporte.

je sens déjà le succès approché grâce à ta détermination.

 

Dans la colonne T c'est bon pour la formule tout simple que tu as mise et le petit problème reste du coté des numéros des producteurs à mon avis qui doit s'incrémenter automatiquement.

 

ce que vous avez fais c'est déjà bon pour avancer, sauf qu'il y a encore des petites coquilles sur ça

les N° ne s'arrête qu'à 0002 et pour chaque Axe.

 Nous ne connaissant pas le nombre des producteur dans chaque axe mais pour ce que je sache il peut y avoir plus d'une centaine 100 dans chaque.

 

je sais que ce pas facile, mais mon souhait serai que ceci se créer dans F dès que l'axe  est choisi ainsi que le ICQ qui va avec l'axe et le nom du producteur sera saisie après le numéro dans G.

 

Si cela n'est peut se faire qu'avec la macro qui pourra certainement te prendre beaucoup de temps, donc faite que ça soit comme tu l'a fais avec axes en le liant avec une liste de série des numéro soit de 0001es à 0200es pour l'axe ouesso/sembé) et également pour les autres.

 

là encore je ne sais pas si c'est possible.

 

Mais excuse moi d'être trop long, c'est parce que j'ai du mal m'expliqué

 

Meilleurs salutations

 

Eudocha

Bonjour Eudocha, bonjour à tous,

 

Concernant le numéro du producteur il ne s'arrête pas à 4, mais mes explications étaient sans doute trop sommaires, désolée.

Si tu vas dans l'onglet "TableProducteurs", colonne D dans la première cellule vide, tu as une liste déroulante te permettant de choisir l'axe pour lequel tu souhaites créer un nouveau numéro de producteur, cette liste déroulante fait référence à la zone nommée ListeAxes

Dès que tu as effectué ton choix, la formule dans la colonne E indique le nouveau numéro du producteur (j'ai ajouté par macro l'inscription de la date au cas où, en colonne F)

 

Cette formule est la suivante en E2 par exemple:

 

=SI(D2<>"";REPT("0";3-NBCAR(NB.SI(D$2:D2;D2)))&NB.SI(D$2:D2;D2)&MINUSCULE(GAUCHE(D2;1)&STXT(D2;CHERCHE("_";D2)+1;1));"")

 

En détaillant un peu:

• CHERCHE("_";D2) indique la position du caractère _ dans le nom de l'axe. En cellule E2 le résultat renvoyé est 6, effectivement pour Epena_Nord le - est en 6ème position, il suffit d'ajouter 1 pour avoir la position du caractère suivant soit le N qui sera utilisé dans le nom du producteur. -voir ICI pour la fonction CHERCHE()-
• La fonction STXT() (voir ICI) permet d'extraire une chaine de caractères, ici elle va extraire au sein de la cellule D2 un chaîne de caractères commençant à la position 7 (caluclée ci-dessus avec CHERCHE()) et d'une longueur de 1. STXT() renvoie donc la lettre N
• GAUCHE(D2;1)  (voir ICI pour la fonction GAUCHE() ) cette fonction permet de renvoyer le premier caractère à gauche de la cellule D2, soit la lettre E
• le signe & est équivalent à la fonction CONCATENER() -voir ICI-, cela permet d'obtenir les deux caractères l'un à la suite de l'autre soit EN
• La fonction MINUSCULE() permet de transformer ce résultat en lettres minuscules pour obtenir: en
• NB.SI(D$2:D2;D2) permet de calculer le nombre de fois où le contenu de la cellule D2 apparaît dans la plage de cellule D$2:D2. Ligne 2 cette fonction renvoie 1 car le libellé Epéna_Nord n'est présent qu'une fois. En passant ligne 2 qui contient également Epéna_Nord, cette fonction NB.SI() renvoie 2 car ce libellé apparaît pour la deuxième fois. D'autres libellés différents se succèdent jusqu'à la ligne 30 où l'on voit à nouveau Epéna_Nord, la fonction NB.SI() sur cette ligne renvoie 3 car c'est effectivement la 3ème fois que ce libellé est présent dans la colonne. Ce numéro passera à 4, 5, 6, etc. en augmentant de 1 à chaque fois que Epéna_Nord apparaîtra.

Comme tout à l'heure le signe & permet de concatener les résultats, les différents calculs ci-dessus permettent d'obtenir par exemple lige 2 le résultat 1en

 

Afin de faire précéder ce résultat de 1 ou 2 zéros j'utilise cette partie de la formule:

REPT("0";3-NBCAR(NB.SI(D$2:D2;D2)))

• NB.SI(D$2:D2;D2) donne la même chose que tout à l'heure
• la fonction NBCAR() (voir ICI)permet de compter le nombre de caractères  d'une chaîne, ainsi si la fonction NB.SI() renvoie 3, le nombre de caractères renvoyé par NBCAR() sera 1, si NB.SI() renvoie 25, NBCAR() renverra 2 caractères.
• Si tu peux avoir 100 producteurs, cela signifie que l'on peut avoir 3 caractères maximum.
• C'est là qu'intervient la fonction REPT() (voir ICI) qui permet de répéter des caractères un certain nombre de fois, ici le caractère à répéter est le 0, qui apparaîtra 2 fois (exemple 002) 1 fois (exemple 025) ou jamais.
• Ce nombre de répétition du caractère zéro est calculé par 3-NBCAR(NB.SI(D$2:D2;D2)), 3 parce que c'est lé maximum, NBCAR(NB.SI(D$2:D2;D2)) pouvant renvoyer 1 2 ou 3. si j'ai 2 caractères je complète par un seul zéro devant (3-2=1), si j'ai un caractère je complète par deux zéros devant (3-1=2) etc.

 Pour la ligne 2 j'obtiens deux fois le caractère 0 

 

En concaténant le tout j'obtiens donc 001en ligne 2.

 

Voilà pour les explications de la formule. La logique que j'ai suivie consiste à créer d'abord le nouveau numéro du producteur si le cas se présente. Une fois le numéro créé, il sera alors disponible dans la liste déroulante de la colonne F de la table principale.

 

D'après ce que je comprends tu souhaiterais éviter cette étape intermédiaire en remplissant ta base directement soit avec un numéro existant soit en en créant un dans la foulée.  J'imagine qu'il faudrait ouvrir un Userform au clic des cellules colonne F, ce Userform présenterait les numéros existants pour l'axe en question, tout en offrant la possibilité d'en créer un nouveau. C'est certainement faisable mais beaucoup plus compliqué (me semble-t-il) y compris pour toi qui devra par la suite assurer la maintenance de cette base.

 

Vois si déjà tu peux tirer quelque chose du fichier joint, on pourra peut-être affiner ensuite.

 

Très bonne soirée,

 

mth

 

 

Bonjour Mth,

 

t'es un vrai prof et vraiment sans vouloir te flatter, tu es spécial t'as pris tout ton temps de m'expliquer pas à pas, merci

 

En fait hier malgré tout tes explications je n'avais pas constaté l'ajout d'autres onglets (Tables;TableProducteurs) ma pensée était beaucoup plus focalisé sur l'onglet principal et c'est pourquoi cela été impossible que je me retrouve sur les références des colonnes que tu avais donné.

 

Rassure toi que je viens de télécharger tout ces cours jointes pour une bonne application

 

Maintenant que j'ai pris connaissance de ces deux onglets que faire pour intégrer ces listes sur l'onglet principal?

 

dans l'onglet TableProducteurs colonne D dès que je choisi un axe le 1er N° sort et c'est au fur et à mesure. Que dois-je faire pour appliquer ça sur l'onglet principal?

 

je me tiens à ta disposition

 

Je te  demande peut être trop, est-il possible que tu me passe ton adresse e-mail avec lequel je pourrai te contacter puisque j'aimerai bien me correspondre avec toi. Mais pour ta discrétion tu ne pas obligé de le publié ici mais moi je te passe le mien pour que tu m'envoie ça (xxxxxxxx @ xxxxx .fr). Franchement je serai très ravi de te lire surtout de vous avoir comme ami(e) sur le net.

 

Salutations

Eudocha

 

Edition par le webmaster : adresse mail supprimée.

Bonjour Eudocha, bonjour à tous

 

Je te remercie pour ton message, mais je te conseille de retirer très vite ton adresse mail en clair dans le forum, sans quoi les robots du net vont te repérer et ta boite mail sera envahie de spams .

Quant aux explications, c'est un peu l'esprit du site XLPages, si tu as l'occasion de lire des réponses de JeanMarie par exemple, tu verras qu'il prend soin de toujours expliquer les choses, ses posts sont souvent de véritables cours

 

Concernant les listes de validation de l'onglet principal:

 

1. Colonne A "Département": La liste de validation fait référence à la zone nommée "Départements". Tu peux le voir en sélectionnant une cellule colonne A et en cliquant dans le ruban sur l'onglet "Données" puis "Validation des données", dans la fenêtre de validation de données tu vois la source: =Departements

Pour voir à quoi correspond ce nom "Departements", dans le ruban onglet "Formules" tu cliques sur "Gestionnaire de noms", dans la fenêtre qui s'ouvre le nom "Departements" apparaît et en cliquant dessus tu vois qu'il fait référence aux cellules A1:C1 de l'onglet Tables, soit =Tables!$A$1:$C$1

 

1. Colonne B "Axe": Dans le Gestionnaire de noms, apparaissent 3 zones nommées pour les Axes:

l'axe SANGHA faisant référence à la plage A2:A10 de l'onglet Tables soit:  =Tables!$A$2:$A$10

l'axe LIKOUALA : =Tables!$B$2:$B$6

CUVETTE_OUEST: =Tables!$C$2

Dans la colonne B de l'onglet principal, j'utilise la fonction INDIRECT() pour la liste de validation afin de faire référence à la zone nommée SANGHA LIKOUALA ou CUVETTE_OUEST qui est renseignée colonne A. La formule de la source de valilation de cette colonne B est =INDIRECT($A6) pour la première ligne.

 

1. Colonne E "Village": Tu vois dans l'onglet "Tables"que j'ai organisé un peu différemment la liste des villages par rapport à ton fichier initial, les villages sont en colonne (colonne P) avec leur Axe à coté colonne O, et, point très important, ils sont triés par Axe.

Dans le gestionnaire de noms apparaît le nom "Village" qui fait référence aux cellules remplies de cette colonne P, avec cette formule:

=DECALER(Tables!$P$2;;;NBVAL(Tables!$P:$P)-1;)

C'est ce qu'on appelle une zone dynamique, tu trouveras quelques explications sur les zones dynamiques ICI.

Sur le même principe j'ai également nommé les axes de la colonne O, la zone nommée "Axe" est une zone dynamique qui fait référence aux cellules remplies de la colonne O avec cette formule:

=DECALER(Tables!$O$2;;;NBVAL(Tables!$O:$O)-1;)

J'utilise ces zones dynamiques Village et Axe dans la liste de validation de l'onglet principal colonne E. Cette liste a comme source:

=DECALER(Village;EQUIV($B6;Axe;0)-1;;NB.SI(Axe;$B6))

Un peu d'explications en complément de ce que tu trouveras dans le tutoriel sur les zones dynamiques:

- Point de départ de la fonction DECALER est la zone Village

- Le décalage de ligne est calculé avec EQUIV($B6;Axe;0)-1. La fonction EQUIV est bien expliquée dans l'aide Excel, elle permet d'indiquer la position d'un élément dans une plage. Ici, je cherche la position de la valeur en B6 (soit Epéna_Sud) au sein de la zone Axe. Cette formule renvoie 83, et je retire 1 pour tenir compte de la présence d'une entête colonne P. Si tu descends le long de cette colonne P tu trouves effectivement pour la première fois le libellé Epéna_Sud ligne 84

- Pas de décalage de colonne (les deux point-virgules sont l'un à coté de l'autre ce qui équivaut à zéro)

- Hauteur de la zone: elle est calculée grâce à: NB.SI(Axe;$B6), cette fonction renvoie le nombre de fois où apparaît le libellé cellule B6, dans la zone Axe. Ici cette fonction renvoie 14, il y a effectivement 14 lignes concernant Epéna_Sud dans la colonne P.

 

Pour finir, notre formule =DECALER(Village;EQUIV($B6;Axe;0)-1;;NB.SI(Axe;$B6)) part de la zone Village colonne P, se décale de 83 lignes vers le bas pour se retrouver en cellule P84, puis prend une hauteur de 14. Cette formule permet donc de définir toute la zone de P84 à P97 qui correspond à Epéna_Sud de la cellule B6 de l'onglet principal.

 

1. Colonne F: Numéro Producteur.

Pour la gestion de ces numéros j'utilise les données de l'onglet "TableProducteurs".

La liste des Axes se trouve colonne A.

Dans la colonne D se trouve une liste déroulante permettant de choisir l'axe sur lequel on veut créer un producteur. Elle fait référence à la zone nommée ListeAxes, qui est simplement définie comme ceci: =TableProducteurs!$A$2:$A$16

La formule de la colonne E est décrite dans le post précédent.

A chaque modification de la colonne D de cet onglet "TableProducteurs", la macro (que tu peux voir dans le code de la feuille) copie les données colonnes D E F et les met colonnes I J K , mais en les triant (très important pour la suite) par axe  et par numéro de producteur.

C'est cette zone triée que j'utilise pour la validation de données de la colonne F de l'onglet principal.

Exactement sur le même principe, je nomme la zone NumProducteurs comme ceci:

=DECALER(TableProducteurs!$J$1;;;NBVAL(TableProducteurs!$J:$J)-1;)

Puis la zone AxesTriés comme ceci:

=DECALER(TableProducteurs!$I$2;;;NBVAL(TableProducteurs!$I:$I)-1;)

Je fais référence à ces zones dans la validation de données:

=DECALER(NumProducteur;EQUIV($B6;AxesTriés;0);;NB.SI(AxesTriés;$B6))

Tu reconnais là le même type de formule que pour les villages ci-dessus.

 

 

A partir de l'onglet principal, une fois que tu as mis en place une validation de données, tu peux sélectionner ta cellule, puis "Copier", sélectionner les cellues où tu souhaites mettre en place ta liste de validation, puis "Collage Spécial", choisir "Validation".

 

Voilà Eudocha, en espérant que cela puisse t'aider, si non dis nous.

A très bientôt

 

mth

bonjour Mth;

 

Excuse moi pour la rupture c'est du aux problèmes de réseau vue que nous sommes dans une zone enclavée, il y a des interruptions à tout moment.

 

J'ai tenté de faire et malgré que tu as pris tout le temps de me mettre des explications, mais je n'arrive pas. Je crois qu'il me faudra encore beaucoup de temps pour comprendre tout ça.

 

L'urgence m'oblige de revenir vers toi afin d'avoir encore ton aide pour finaliser cette base, tout marche bien c'est seulement les Numéros qui posent encore problème.

 

Pourtant tu m'as donné la solution puisque ça marche bien dans l'onglet TableProducteurs, l'application dans l'onglet principal BB Producteurs kko colonne F me pose problème.

 

Pour la date sur l'onglet principale elle doit être saisie à la main toujours dans la colonne C

 

STP j'ai vraiment besoin de ton aide pour finaliser cette base

Merci

 

Eudocha

Bonjour eudocha, bonjour le forum,

 

Je suis vraiment ennuyée que tu n'arrives pas à reproduire ce numéro producteur dans ta base réelle.

Si l'on reprend étape par étape pour ce numéro:

 

Dans l'onglet "TableProducteurs" as-tu bien ce code:

Il permet trier les Axes et Producteurs dans les colonnes I J K:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Columns(4)) Is Nothing Then
Dim lig As Long, Plage As Range
    With ActiveSheet
        Target.Offset(0, 2) = Date
        lig = .Cells(Rows.Count, 4).End(xlUp).Row
        Set Plage = .Range("D1:F" & lig)
        Plage.Copy
        .Range("I1").PasteSpecial Paste:=xlPasteValues
            With .Sort.SortFields
             .Clear
             .Add Key:=Range("I2:I" & lig), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                 xlSortNormal
             .Add Key:=Range("J2:J" & lig), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                 xlSortNormal
            End With
            
            With .Sort
             .SetRange Range("I1:K" & lig)
             .Header = xlYes
             .MatchCase = False
             .Orientation = xlTopToBottom
             .SortMethod = xlPinYin
             .Apply
            End With
    End With
End If
Application.CutCopyMode = False
Target.Select
Application.ScreenUpdating = True
End Sub

Ensuite dans les zones nommées, as-tu bien dans ton fichier cette zone: "Numproducteur", qui fait référence à:

=DECALER(TableProducteurs!$J$2;;;NBVAL(TableProducteurs!$J:$J)-1;)

ainsi que cette zone "AxesTirés":

=DECALER(TableProducteurs!$I$2;;;NBVAL(TableProducteurs!$I:$I)-1;)

Enfin, dans ton onglet BDD colonne F, ta liste de validation fait-elle bien référence à ceci:

=DECALER(NumProducteur;EQUIV($B6;AxesTriés;0);;NB.SI(AxesTriés;$B6))

Peux-tu vérifier étape par étape, si tu n'y arrivais pas pourras-tu nous dire à quel endroit tu bloques?

 

Bon courage eudocha et à très bientôt,

 

mth