XL 2019 renvoie d'une nom si cellule appartient à une plage de données

[youpi457032]

bonjour,
Je recherche un code Vba qui me permettrait de renvoyer pour chaque cellule d'une colonne (boucler sur la colonne) un nom si la cellule d'une autre colonne appartient à une plage de données.
Ex dans ma base de données "BD" en colonne M: M1 "cours 1" en M2 "cours 2" en M3 "cours 1"etc...
et en colonne R renvoyer le nom du prof en fonction du cours : cours1 = prof 1, cours2 = prof 2.
La plage de reference porterait sur les cours. Elle pourrait par exemple se situer sur un onglet "références" en plage A:A comme suit :
a1 : cours 1
a2 : cours 2
a3 : cours 3
a4 cours 4
etc...
Merci d'avance...

 

[sylvanu]

Bonjour Youpi,
Un petit fichier test serait le bienvenu.
D'une part cela nous éviterait de le construire, d'autre part de coller à votre réalité et non à la compréhension qu'on en a, et donc de se tromper et de répondre à coté.

 

[youpi457032]

Bonjour sylvanu
je vous envoie cela...
j'aurai bien d'autres questions à poser aussi....
Merci de l'attention portée....
pour votre information cette fonction de filtrage a pour vocation a faire de l'emailing... j'ai tout de prêt...
je cherche a peaufinner ma fonction de filtrage pour que l'ensemble de mon classeur soit prêt...
----------
Merci beaucoup

en autre question par exemple, dans le fichier que je joins, sur l'userform il y a la Listbox "Site" ( pas encore construite)
je vais fusionner deux bases en une...la premiere base etant celle djà présente...
la seconde venant d'un autre site de danse....avec des intitulés de cours différents...
j'aimerai que lorsque je vais faire apparaitre mes deux sites (site 1 et site 2 ) comme critère de recherche, si je selectionne le critere site 1 ou site 2 seul les cours du site selectionné apparaisse dans la listeBox "choix des cours"....un genre de sous filtrage par site ....comme des listes dépendantes....

 

[sylvanu]

Bonjour Youpi,
La règle est de ne soumettre qu'un problème par post. De cette façon le titre est plus précis et le lecteur futur pourra mieux cibler sa recherche.
Par ex si un lecteur recherche une solution sur un userform il ne trouvera ce post, puisque le titre porte sur "renvoie d'une nom si cellule appartient à une plage de données".

En PJ un essai.
A chaque fois qu'on sélectionne la feuille Reférences je reconstruit automatiquement et totalement la feuille, elle est ainsi toujours à jour, et je n'indique que les profs concernés. La macro Worksheet_Activate est dans la feuille Références.
J'ai fait précéder les jours avec leur N°, ce qui permet de les ranger dans l'ordre du Lundi au Dimanche.

 

[sylvanu]

Une V2 sans les N° de jours, pour être plus "esthétique"

 

[youpi457032]

Bonjour Youpi,
La règle est de ne soumettre qu'un problème par post. De cette façon le titre est plus précis et le lecteur futur pourra mieux cibler sa recherche.
Par ex si un lecteur recherche une solution sur un userform il ne trouvera ce post, puisque le titre porte sur "renvoie d'une nom si cellule appartient à une plage de données".

En PJ un essai.
A chaque fois qu'on sélectionne la feuille Reférences je reconstruit automatiquement et totalement la feuille, elle est ainsi toujours à jour, et je n'indique que les profs concernés. La macro Worksheet_Activate est dans la feuille Références.
J'ai fait précéder les jours avec leur N°, ce qui permet de les ranger dans l'ordre du Lundi au Dimanche.

Une V2 sans les N° de jours, pour être plus "esthétique"

re...
Ah ca c'est tres tres bon ! pour remplir la feuille référence ! excellent même ! je n'y avais pas pensé ! donc je garde !!! déjà un grand merci pour ça c'est au top !
par contre ce que je voulais au départ c'est une macro Vba qui me permette de lister tous les élèves par prof dans mon userform ( comme je l'ai fait pour les cours).....
pour cela il faut que j'arrive à identifier pour chaque elève avec quels profs il a des cours...(sur les colonnes M à Q ) ... hors avec ma macro en place je n'arrive à lister qu'a partir de la colonne M et pas les suivantes N à Q
commment réaliser cela ?
Je pense que dans mon code le travail se fait uniqhement sur ma colonne 18 (correspond à M), j'aimerai a trouver comment le realiser sur l'ensemble des colonnes 18 à 22 (M à Q)

Sub Affiche()
  Set dchoisis1 = CreateObject("Scripting.Dictionary")
  For i = 0 To Me.OptionsGenre.ListCount - 1
    If Me.OptionsGenre.Selected(i) Then dchoisis1(Me.OptionsGenre.List(i, 0)) = ""
  Next i
  Set dchoisis2 = CreateObject("Scripting.Dictionary")
  For i = 0 To Me.OptionsArtiste.ListCount - 1
    If Me.OptionsArtiste.Selected(i) Then dchoisis2(Me.OptionsArtiste.List(i, 0)) = ""
  Next i
     Set dchoisis3 = CreateObject("Scripting.Dictionary")
  For i = 0 To Me.OptionAlbum.ListCount - 1
    If Me.OptionAlbum.Selected(i) Then dchoisis3(Me.OptionAlbum.List(i, 0)) = ""
    Set dchoisis4 = CreateObject("Scripting.Dictionary")
  Next i
    [COLOR=rgb(247, 218, 100)]Set dchoisis4 = CreateObject("Scripting.Dictionary")
  For i = 0 To Me.OptionIntervenant.ListCount - 1
    If Me.OptionIntervenant.Selected(i) Then dchoisis4(Me.OptionIntervenant.List(i, 0)) = ""
  Next i
  Dim Tbl2(): N = 0: Ncol = UBound(TblBD, 2)
  For i = 1 To UBound(TblBD)
    tmp13 = TblBD(i, 13): tmp14 = TblBD(i, 14):: tmp15 = TblBD(i, 15): tmp16 = TblBD(i, 16): tmp17 = TblBD(i, 17): tmp2 = TblBD(i, 5): tmp3 = TblBD(i, 6):[COLOR=rgb(247, 218, 100)] tmp4 = TblBD(i, 18) 
la comment mettre (i, 18 à 22) ???[/COLOR]
    If ((dchoisis1.exists(tmp13) Or dchoisis1.Count = 0) Or (dchoisis1.exists(tmp14) Or dchoisis1.Count = 0) Or (dchoisis1.exists(tmp15) Or dchoisis1.Count = 0) Or (dchoisis1.exists(tmp16) Or dchoisis1.Count = 0) Or (dchoisis1.exists(tmp17) Or dchoisis1.Count = 0)) And (dchoisis2.exists(tmp2) Or dchoisis2.Count = 0) And (dchoisis3.exists(tmp3) Or dchoisis3.Count = 0) And (dchoisis4.exists(tmp4) Or dchoisis4.Count = 0) Then
        N = N + 1: ReDim Preserve Tbl2(1 To Ncol, 1 To N)
        For k = 1 To Ncol: Tbl2(k, N) = TblBD(i, k): Next k
    End If
  Next i
  If N > 0 Then Me.ListBox1.Column = Tbl2 Else Me.ListBox1.Clear
  Me.LabelLigne.Caption = N & " élèves"
End Sub
Sub Tri(a, gauc, droi) ' Quick sort
 ref = CStr(a((gauc + droi) \ 2))
 g = gauc: d = droi
 Do
  Do While CStr(a(g)) < ref: g = g + 1: Loop
  Do While ref < CStr(a(d)): d = d - 1: Loop
  If g <= d Then
    temp = a(g): a(g) = a(d): a(d) = temp
    g = g + 1: d = d - 1
  End If
 Loop While g <= d
 If g < droi Then Call Tri(a, g, droi)
 If gauc < d Then Call Tri(a, gauc, d)
End Sub

 

[sylvanu]

Désolé de n'avoir pas compris, j'ai répondu à ce que j'avais compris de votre post #1 et au fichier joint feuille Références, où il n'apparait pas d'élèves mais de profs.
Je regarde.

 

[sylvanu]

J'ai un petit souci.
J'ai créé en PJ 2 élèves NomEx1 et NomEx2, avec comme prof MoiLeProf et des cours sur les colonnes M à Q.
Dans le Userform quand je sélectionne MoiLeProf, j'ai bien tous les cours qui s'affichent.
Quelle config faut il pour faire apparaitre le problème ?

 

[youpi457032]

J'ai un petit souci.
J'ai créé en PJ 2 élèves NomEx1 et NomEx2, avec comme prof MoiLeProf et des cours sur les colonnes M à Q.
Dans le Userform quand je sélectionne MoiLeProf, j'ai bien tous les cours qui s'affichent.
Quelle config faut il pour faire apparaitre le problème ?
Regarde la pièce jointe 1116116

 

[youpi457032]

je te renvoie ton fichier avec l'exemple de la ligne 106 : élève Ex1
j'ai mis trois cours en face avec des couleurs différentes. A chaque couleur son prof...
dans la fonction de filtre (userform) par prof seul le critère "Moi le prof" fonctionne parce que la macro travaille sur la colonne M qui identifie le prof.... comme ma macro ne travaille par sur les colonnes N à Q .
cet élève a donc trois profs différents... mais n'apparait que " Moi le Prof", alors qu'on devrait le retrouver cet élève si on choisit comme critère LUI LE PROF ou Toi le prof.... et là ca ne fonctionne pas....
solution a envisager :
colonne R : macro pour identifier le prof selon le cours de la colonne M ( en fonction de la table dans l'onglet référence )
colonne S : macro pour identifier le prof selon le cours de la colonne N ( en fonction de la table dans l'onglet référence )
colonne T : macro pour identifier le prof selon le cours de la colonne O ( en fonction de la table dans l'onglet référence )
colonne U : macro pour identifier le prof selon le cours de la colonne P ( en fonction de la table dans l'onglet référence )
colonne V : macro pour identifier le prof selon le cours de la colonne Q ( en fonction de la table dans l'onglet référence )

Ensuite je récris ma macro comme pour le critère filtre pour les cours de M à Q.... je l'applique au Prof de R à V
il faut juste trouver la solution pour identifier le prof pour chaque ligne de R à S en fonction des cours choisis. C'est là que je pêche.... comment dire en macro par exemple en M106 cours 1 = Moi le prof , puis en N106 cours 2 = Lui le Prof....etc ...

Me comprends tu ?
Si tu as une solution macro plus simple que travailler sur chaque colonne independament... je prends aussi
Merci,

 

[sylvanu]

Bonjour Youpi,
Vraiment désolé, je ne peux pas vous aider. Je ne comprends vraiment rien. Je vais vous faire perdre votre temps. Sorry.

J'ai fait un test. Voir PJ.
Un unique élève NOM_1 à 5 cours ( COURS_PROF_1 à 5 ) avec 5 profs PROF_1 à 5.
Chaque cours est respectivement de la colonne M à la colonne Q.
Dans l'userform je sélectionne les 5 profs et j'ai bien NOM_1 avec les 5 cours, donc les colonnes M:Q ont bien été analysées.

 

[youpi457032]

Bonjour Youpi,
Vraiment désolé, je ne peux pas vous aider. Je ne comprends vraiment rien. Je vais vous faire perdre votre temps. Sorry.

J'ai fait un test. Voir PJ.
Un unique élève NOM_1 à 5 cours ( COURS_PROF_1 à 5 ) avec 5 profs PROF_1 à 5.
Chaque cours est respectivement de la colonne M à la colonne Q.
Dans l'userform je sélectionne les 5 profs et j'ai bien NOM_1 avec les 5 cours, donc les colonnes M:Q ont bien été analysées

 

[youpi457032]

Merci beaucoup....
J'ai vu que ca ne fonctionnait pas....
on peut faire autrement...
Est-il possible de trouver une macro simple qui ecrirait :
en colonnne R le nom du prof en fonction de la colonne M. M etant le cours. Soit je liste tous les cours directement en Macro ( c'est fastidieux).... soit on fait appel à une liste de référence (liste des cours ) dans la colonne indiquée...
Je m'explique :
exemple : onglet reference colonne A : liste de tous les cours assurer par Prof 1, (saisie manuelle....)
conne B : liste de tous les cours assurer par le prof 2, (saisie manuelle....)
colonne C : liste de tous les cours assurer par le prof 3, (saisie manuelle....)
colonne D : liste de tous les cours assurer par le prof 4, (saisie manuelle....)
colonne E:..... etc.....

analyse de la conne M en feuille BD, et on ecrit en R (feuille BD), cellule après cellule le Prof en fonction de la liste de reference (onglet reference)
Concretement si cellule M2 correspond à cours présent en colonne A (onglet reference) alors R2 = nonm du prof saisi en A1 (onglet reference)...., ou prof B1 si correspond à liste cours B, ou prof C1 si liste de cours correspond à colonne c...etc....
Si cellule M2 correspond à cours présent en colonne D (onglet reference) alors R2 = nom du prof saisi en D1 (onglet reference)....ou prof B1 si correspond à liste cours B, ou prof C1 si liste de cours correspond à colonne C..etc....
ET on fait ça pour chaque colonne de M à Q (onglet BD).....
analyse M on ecrit le nom du prof en R
analyse N on ecrit le du prof en S
analyse O en ecrit nom du prof en T
analyse P on ecrit nom du pro en U
analyse Q on ecrit nom du prof en V

Pour la fonction de tri par prof.....j'ai la solution... je l'ai deja écrite pour le tri par cours....je peux arriver à me débrouiller.... si on arrive à lister les profs dans les colonnes R à V....
Merci d'avance, si vous arrivez à faire cela....