extraire une liste de nom renvoyant a un code de deux lettres

Bonsoir Youen,

je te propose ce fichier : https://www.cjoint.com/c/MJsvsHERzI0

* regarde la 1ère feuille, et note qu'en colonne Z, le code AA
est en ligne 8, pour PIERRE Henri.

* va sur la 2ème feuille ; comme en B2 il y a Z, on va faire
une recherche en colonne Z ; comme en B3 il y a AA,
c'est le code AA qu'on va chercher en colonne Z.

* fais Ctrl e ➯ ça affiche les résultats de la recherche :
en ligne 8, ça a trouvé un code AA pour PIERRE Henri,
en ligne 139, ça a trouvé un code AA pour YOUEN Luc.

ton prénom n'est pas Luc ? ah, désolé.  ;)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

tu aurais aussi pu faire Ctrl e depuis la 1ère feuille : ça aurait
fait la même chose ; fais Alt F11 pour voir le code VBA, puis
de nouveau Alt F11 pour revenir sur Excel.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

tout ceci est seulement une piste, car il manque des infos,
et aussi des données ; pour t'aider davantage : joins un
fichier via cjoint.com avec plusieurs données anonymes,
et précise mieux ce que tu veux.

rhodo

Bonjour,

Merci beaucoup pour votre réponse. 

Je n'arrive pas à utiliser la fonction Ctrl e comme vous la décrivez. Lorsque je clique ça recentre ma case. Qu'est ce que je devrais sélectionner pour que ça fonctionne?

Voici mon document ci-joint anonymisé. J'espère que l'exposé de mon problème sera plus clair.

https://www.cjoint.com/c/MJtiKarvN3l

Bonsoir youen,

ton fichier en retour : https://www.cjoint.com/c/MJtuo1Fn4e0

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

à l'ouverture du fichier, tu es sur la 2ème feuille "Planning Par Jour".

en A1, saisis 1 ➯ ça affiche les résultats de la 1ère semaine ;
vérifie bien attentivement tous les résultats.

en A1, saisis 2 ➯ ça affiche les résultats de la 2ème semaine ;
à vérifier aussi.

en A1, appuie sur la touche Suppression ➯ ça efface tous les résultats
➯ tu peux faire une pause et aller boire un café : y'a rien à vérifier.  ;)

en A1, saisis 3 ➯ ça affiche les résultats de la 3ème semaine ;
à vérifier aussi.

en A1, saisis 4 ➯ ça affiche les résultats de la 4ème semaine ;
à vérifier aussi.

en A1, saisis 1 ➯ ça affiche de nouveau les résultats de la 1ère semaine,
que tu as déjà vérifiés.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

sur les 2 feuilles, tu peux faire Ctrl e ; si le n° semaine est correct, ça fera
la même chose que ci-dessus ➯ ça permet d'actualiser les résultats,
notamment si tu as changé des données sur la 1ère feuille "2023-2024".

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

fais Alt F11 pour voir le code VBA, puis fais de nouveau Alt F11 pour
revenir sur Excel.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

sur l'image ci-dessous, tu pourras voir un extrait des résultats pour
la 1ère semaine, Lundi seulement (matin et après-midi).

lien : https://www.cjoint.com/c/MJtuVFtmCF0

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

si besoin, tu peux demander une adaptation.
à te lire pour avoir ton avis.  :)

rhodo

Bonjour youen,

tu as écrit :

« La formule  =SI(ET(A1>0;A1<5);DECALER($'2023-2024'.C2;0;(A1-1)*10);"")
fonctionne bien. »

ok, mais bizarrement, y'a un « $ » et un « . » dans « $'2023-2024.' » ; dans mon
fichier, pour ma formule de B1, je n'ai pas mis de « $ » ni de « . » :

=SI(ET(A1>0;A1<5);DECALER('2023-2024'!C2;0;(A1-1)*10);"")

un « $ » sert à « fixer » une colonne ou une ligne pour une future recopie de
la formule (vers la droite ou vers le bas) ; comme ici y'a pas besoin d'étendre
la formule, un « $ » est inutile ; de plus, tu as mis un « $ » non pas devant
une colonne ou une ligne mais devant un nom de feuille ; ça ne se fait pas,
car de toute façon ça ne sert à rien : on n'a jamais besoin de fixer un nom
de feuille !

et pourquoi tu as mis un « . » juste après « '2023-2024' » ? dans ma formule,
c'est un « ! » car dans « '2023-2024'! » le « ! » est un caractère final pour
indiquer à Excel que '2023-2024' est un nom de feuille ; en même temps,
ce « $ » est un séparateur des 2 éléments nom de feuille et cellule :
il sert à séparer « '2023-2024' » et « C2 ».

mébon, peut-être que tu as juste fait 2 erreurs de frappe ? l'essentiel, c'est
que ma formule fonctionne bien vu qu'elle retourne ce qu'il faut.  :)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

tu as écrit : « c'est exactement ce qu'il me faut. La capture d'écran correspond
à ce dont j'ai besoin. » ; parfait : j'suis bien content que les résultats sont ceux
que tu attendais.  :)

tu as écrit : « je n'arrive pas à obtenir ces résultats. Rien ne s'affiche sous les
cases "Buanderie", etc. » ; « mais rien d'autre ne s'affiche dans le document. »
et « Je n'arrive pas à utiliser la fonction "Ctrl+e" qui devrait me permettre
d'obtenir les résultats. ».

suis toute la procédure décrite ci-dessous.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

a) ouvre ton fichier réel ; un fichier .xlsx ne peut pas contenir de code VBA,
donc il ne peut pas contenir de macros ; il faut d'abord le convertir en un
fichier .xlsm (le « m » signifie qu'il peut contenir des macros) ; appuie sur
la touche F12 ➯ fenêtre "Enregistrer sous" ; en bas, il y a "Type :" ; dans
la liste, sélectionne "Classeur Excel (prenant en charge les macros)" puis
clique sur le bouton "Enregistrer" ; c'est fait : conversion réussie.

b) fais Alt F11 ➯ fenêtre "Microsoft Visual Basic" ; fais Alt i m ➯ ça insère
un nouveau module, qui s'appelle Module1 ; à droite, clique sur la page
blanche ; c'est là que tu dois faire un copier / coller de tout le code VBA de
Module1 qui est dans le fichier Excel joint dans mon post du 19 octobre à
23:02 ; côté gauche, sélectionne "Feuil2 (Planning Par Jour)" puis appuie
sur la touche Entrée ➯ ça ouvre une autre page blanche ; clique dessus ;
c'est là que tu dois faire un copier / coller de ces 4 lignes :

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Address = "$A$1" Then PPJ
End Sub

c) fais Alt F11 ➯ ça retourne sur Excel ; fais Alt F8 ➯ fenêtre "Macro" ; dans
la liste des macros, il y a la macro "PPJ" ; comme y'a qu'une seule macro, elle
est déjà sélectionnée : la ligne bleue ; à droite et en bas, clique sur le bouton
"Options..." ➯ fenêtre "Options de macro" ; dans la petite case blanche,
saisis e ; clique sur le bouton "OK" ➯ ça retourne à la fenêtre précédente
"Macro" ; ferme cette fenêtre ; tu viens d'ajouter le raccourci clavier Ctrl e.

d) sur la 2ème feuille "Planning Par Jour", en A1, saisis par exemple 1 ➯
en A1 : "Semaine 1" ; et en B1 : "Semaine A" ; dessous, y'a les résultats.

si ça le fait : OK, le problème est réglé ; sinon, c'est que les macros sont
désactivées ; tu dois activer les macros pour que ça fonctionne.

fais une recherche google pour trouver comment activer les macros.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

tu as écrit : « Je ne trouve pas d'autres formules dans le document. » ;
je le confirme : il y a une seule formule, qui est celle de B1.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

pour mon 1er fichier "Exo Youen", tu avais écrit dans ton post d'hier à 10:39 :
« Je n'arrive pas à utiliser la fonction Ctrl e comme vous la décrivez. Lorsque
je clique ça recentre ma case. Qu'est ce que je devrais sélectionner pour que
ça fonctionne? »

y'a un problème de raccourci clavier Ctrl e qui n'est pas mis ; c'est pour ça
que ça recentre la case au lieu d'exécuter la macro "Essai" ; et là aussi,
tu avais sans doute un problème de macros désactivées.

mébon, c'est juste pour info car ce 1er fichier "Exo Youen" ne sert plus ;
tu peux quand même regarder ce que ça fait.  ;)

rhodo

@youen

Lis d'abord mon post précédent de 15:04 ; puis lis ce post, qui est pour
montrer tout le code VBA du fichier "Formule 2023-2024 TM".

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

code VBA de Module1 (41 lignes) :

Option Explicit

Sub PPJ()
  Dim LC, m As Byte
  'LC = Liste des Codes ; ATTENTION : les codes doivent être dans l'ORDRE des
  'colonnes de DESTINATION : colonnes B à K de la feuille "Planning Par Jour"
  LC = Array("B", "M", "EV", "F", "V", "AA", "FL", "NL", "MB", "STR")
  Dim v$, c1 As Byte, c2 As Byte, c As Byte, k As Byte, d&, p&, r&, i&
  Application.ScreenUpdating = 0: Worksheets("2023-2024").Select: m = UBound(LC)
  With Worksheets("Planning par jour")
    For i = 0 To 9: .Cells(9 * i + 4, 2).Resize(7, 10) = Empty: Next i
    If .[B1] = "" Then .Select: Exit Sub 'sortie, car pas de n° semaine valide ;
    'attention, y'a pas d'erreur : c'est bien B1 qu'il faut tester et pas A1.
    d = Cells(Rows.Count, 2).End(3).Row: If d < 6 Then Exit Sub 'aucun item
    c1 = (.[A1] - 1) * 10 + 3: c2 = c1 + 9: d = d + 1
    For c = c1 To c2
      For i = 6 To d
        v = Cells(i, c): k = 0
        If v <> "" Then
          For p = 0 To m
            If LC(p) = v Then k = p + 2: Exit For
          Next p
          'si k = 0 : le code ne fait pas partie des codes de destination =>
          'on n'en tient pas compte (on l'ignore seulement) ; ainsi, ce qui
          'suit est uniquement pour k > 0 : le code fait partie des codes de
          'destination (colonnes B à K de la feuille "Planning Par Jour").
          If k > 0 Then
            p = (c - c1) * 9 + 11: r = .Cells(p, k).End(3).Row + 1
            'ATTENTION : sur "Planning Par Jour", pour chaque demi-journée,
            'on n'a que 7 lignes => pour ne pas déborder verticalement, le
            'test « r < p » fait qu'on prendra en compte uniquement les 7
            'premiers noms ; donc si y'en a d'autres, ils seront ignorés :
            'ils ne seront pas notés en feuille de résultats.
            If r < p Then .Cells(r, k) = Cells(i + (i Mod 2 = 1), 2)
          End If
        End If
      Next i
    Next c
    .Select
  End With
End Sub

ATTENTION : lis très attentivement TOUS les commentaires
(dans le code VBA du fichier Excel, ils sont en vert).

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

code VBA du module de Feuil2 "Planning Par Jour" (4 lignes) :

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Address = "$A$1" Then PPJ
End Sub

rhodo

@youen

j'ai beaucoup modifié mon post de 15:04, car j'ai mieux décrit la procédure
que tu dois suivre pour mettre du code VBA dans ton fichier Excel réel.  :)

tu peux aussi lire mon post de 15:30, qui montre tout le code VBA du
fichier Excel joint de mon post du 19 octobre à 23:02.

à te lire pour savoir si tu as réussi ; mais si ça ne marche toujours pas,
dis-moi ce qui ne va pas et je t'aiderai de nouveau.

rhodo

Merci beaucoup. J'ai passé pas mal de temps à essayer de comprendre le code BVA. Il va me falloir encore un peu de pratique et de formation, a priori.

Bonjour youen,

autre version du fichier : https://www.cjoint.com/c/MJxl5tvmCP0

j'ai rajouté les couleurs ; voici un extrait des résultats :

lien : https://www.cjoint.com/c/MJxmujsBko0

l'utilisation est la même qu'avant ; à toi d'essayer.  :)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

sur la feuille "Planning Par Jour", si tu as 3 colonnes supplémentaires :

a) ajoute 3 colonnes ➯ colonnes B:N (au lieu de B:K).

b) tu as 3 codes supplémentaires, par exemple "MOB" ; "SCU" ; "VAN" ;
ajoute ces 3 codes dans la liste de LC ➯ tu dois avoir ceci :

LC = Array("B", "M", "EV", "F", "V", "AA", "FL", "NL", "MB", "STR", "MOB", "SCU", "VAN")

c'est tout ; normalement, ça sera OK à l'exécution de la macro.  :)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

code VBA de Module1 (41 lignes) :

Option Explicit

Sub PPJ()
  Dim LC, m As Byte
  'LC = Liste des Codes ; ATTENTION : les codes doivent être dans l'ORDRE des
  'colonnes de DESTINATION : colonnes B à K de la feuille "Planning Par Jour"
  LC = Array("B", "M", "EV", "F", "V", "AA", "FL", "NL", "MB", "STR")
  Dim v$, c1 As Byte, c2 As Byte, c As Byte, k As Byte, d&, p&, r&, i&
  Application.ScreenUpdating = 0: Worksheets("2023-2024").Select: m = UBound(LC)
  With Worksheets("Planning par jour")
    For i = 0 To 9
      With .Cells(9 * i + 4, 2).Resize(7, m + 1)
        .Interior.ColorIndex = -4142: .Value = Empty
      End With
    Next i
    If .[B1] = "" Then .Select: Exit Sub 'sortie, car pas de n° semaine valide.
    d = Cells(Rows.Count, 2).End(3).Row: If d < 6 Then Exit Sub 'aucun item
    c1 = (.[A1] - 1) * 10 + 3: c2 = c1 + 9: d = d + 1
    For c = c1 To c2
      For i = 6 To d
        v = Cells(i, c): k = 0
        If v <> "" Then
          For p = 0 To m
            If LC(p) = v Then k = p + 2: Exit For
          Next p
          If k > 0 Then
            p = (c - c1) * 9 + 11: r = .Cells(p, k).End(3).Row + 1
            'ATTENTION : sur "Planning Par Jour", pour chaque demi-journée,
            'y'a 7 lignes => pour ne pas déborder verticalement, on prend
            'en compte SEULEMENT les 7 premiers noms.
            If r < p Then
              p = i + (i Mod 2 = 1): .Cells(r, k) = Cells(p, 2)
              .Cells(r, k).Interior.Color = Cells(p, 2).Interior.Color
            End If
          End If
        End If
      Next i
    Next c
    .Select
  End With
End Sub

rhodo

Merci infiniment pour tous tes précieux conseils. Ils ont été d'une grande aide et m'ont permis de produire un document satisfaisant, même s'il reste toujours des possibilités d'amélioration. Actuellement en congé, je n'aurai accès à mon document qu'à partir du 6 novembre. Je prévois de simplifier davantage son contenu.

Bonjour youen,

tu as écrit :

« Est-il possible d'appliquer deux couleurs différentes à une même cellule »

oui, c'est possible : fenêtre "Format de cellule", onglet Remplissage, clique
sur le bouton "Motifs et textures..." ➯ fenêtre "Effets de remplissage", onglet
"Dégradé" ; utilise "Bicolore" et choisis 2 couleurs ; dessous, tu peux choisir
le "Type de dégradé".

j'ai dû modifier le code VBA pour prendre en compte des fonds bicolores.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

tu as écrit :

« sans que cela n'affecte les macros et les formules déjà intégrées dans les
différentes feuilles ? »

avec l'ancien code VBA ou avec le nouveau code VBA :

oui, un changement de couleur de remplissage (avec ou sans dégradé) ne
va pas affecter les macros, ni les formules ; dans les 2 fichiers, la macro
copie ce qui est nécessaire.  :)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

fichier version v2 : https://www.cjoint.com/c/MKcac1C5Np0

extrait des résultats (presque identique au précédent) :

lien : https://www.cjoint.com/c/MKbxN3Euer0

l'utilisation est la même qu'avant ; mais attention :

sur la feuille "Planning Par Jour", après avoir saisi 1 en A1,
tu dois attendre au moins une bonne dizaine de secondes
car l'exécution de la macro prend plus de temps.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

dans le code VBA, j'ai remplacé ça :

If r < p Then
  p = i + (i Mod 2 = 1): .Cells(r, k) = Cells(p, 2)
  .Cells(r, k).Interior.Color = Cells(p, 2).Interior.Color
End If

par ça :

If r < p Then
  p = i + (i Mod 2 = 1): Cells(p, 2).Copy
  With .Cells(r, k)
    .PasteSpecial 7: .HorizontalAlignment = 1: Application.CutCopyMode = 0
    With .Font: .Name = "Calibri": .Size = 10: .Bold = 0: End With
  End With
End If

et plus bas, la ligne avant le dernier « End With » est devenue :

.Select: [A1].Select

rhodo