Discussion :

[Xavier_Glaize]

Bonjour à tous
je viens juste de m'inscrire et je prendrais le temps de compléter mon profil plus tard.
depuis pas mal d'années j'utilise ce forum pour me donner des solutions VBA,ACCESS html etc....
Normalement avec un peu d'attention toutes les réponses dont j'ai besoin son présente sur ce forum et souvent très bien expliquer sur les TUTO.

mais là je sèche j'ai beaucoup lu, fait pas mal d'essais, mais je n'y arrive vraiment pas, j'ai demandé de l'aide autour de moi mais j'ai l'impression que nous atteignons notre limite de compétences, je ne suis même pas sur d'être sur la bonne voie avec la fonction decaler.

je vous explique j'ai découvert au début de ce projet (début du mois) la solution index/equiv pour obtenir le résultat dont j'avais besoin.
au passage merci a http://boisgontierjacques.free.fr/pa...ctionindex.htm



je souhaite récupérer la valeur d'un champs en fonction de 2 critères de recherches dans un classeur "planning" et rapportez ce résultat dans un classeur "affectation" en fonction du jour et du poste je met les 2 fichiers en pieces jointes.

celui ci planningS02 à S04.xlsx et classeur de destination.xlsm


le code que j'utilise et qui fonctionne parfaitement et celui là

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDEX('[planningS02 à S04.xlsx]Report'!$A$9:$A$75;EQUIV($D$5;INDEX('[planningS02 à S04.xlsx]Report'!$B$9:$AU$75;;EQUIV($D$4;'[planningS02 à S04.xlsx]Report'!$B$8:$AU$8;0));0))

seulement voilà cette formule ne donne que le premier résultat obtenue et j'ai besoin des 4 ou 5 résultats présent.
Le "tirage de poignée" n'est pas envisageable et je ne sais vraiment pas où placer la, ou les, fonctions decaler.

j'ai vraiment besoin d'aide pour passer au reste car je dois encore intégrer des données et là je suis bloqué alors si des âmes charitables veulent se pencher sur ma question ce serait géniale.

[Invité]

Bonjour,

Bien que votre message ne soit pas dans le forum Vba Excel, une solution possible serait justement d'utiliser VBA soit à partir d'une fonction, soit à partir d'une procédure Sub.

Le vidage d'écran ci-dessous contient le résultat des deux formules :

• La journée du 08/01 est réalisée avec la fonction AgentPoste. La ligne 5 indique le nombre de lignes pour le poste choisi et la journée en référence.
• La journée du 09/01 est réalisée avec la procédure RerchercherLesAgentsDUnPoste. Celle-ci est activée sur clic droit sur une des cellules G6 à I6.

Pièce jointe 342220

Pour cela, il est nécessaire de modifier le tableau Report dont la copie est intégrée dans le fichier Classeur de destination pour faciliter la mise au point sous le nom de 2018-01. Les dates doivent être incrémentées sous format date dans la ligne de titre comme ci-dessous.

Pièce jointe 342225

La fonction

Dans la fonction, le paramètre IndiceChoisi correspond au numéro d'ordre du poste recherché parmi les N possibles. Exemple D7 correspond à l'indice 1, D8 au 2, etc....

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
Option Explicit
 
Function AgentPoste(ByVal NomFeuilleMois As String, ByVal TitreFeuille As Long, ByVal JourChoisi As Date, ByVal PosteChoisi As String, ByVal IndiceChoisi As Integer) As String
 
Dim FeuilleMois As Worksheet
Dim I As Long
Dim ColonneMois As Long, DerniereColonne As Long, DerniereLigne As Long, ColonneAgent As Long
Dim AireJournee As Range, CellulePoste As Range
Dim IndiceEnCours As Integer
 
     Application.Volatile
     Set FeuilleMois = Sheets(NomFeuilleMois)
     With FeuilleMois
          ColonneAgent = 1
          DerniereLigne = .Cells(.Rows.Count, ColonneAgent).End(xlUp).Row
          DerniereColonne = .Cells(TitreFeuille, .Columns.Count).End(xlToLeft).Column
 
          ColonneMois = 0
          For I = 1 To DerniereColonne
               If .Cells(TitreFeuille, I) = JourChoisi Then ColonneMois = I
          Next I
          If ColonneMois = 0 Then Exit Function
          Set AireJournee = .Range(.Cells(TitreFeuille + 1, ColonneMois), .Cells(DerniereLigne, ColonneMois))
          If WorksheetFunction.CountIf(AireJournee, PosteChoisi) > 0 Then
             IndiceEnCours = 0
             AgentPoste = ""
             For Each CellulePoste In AireJournee
                 If CellulePoste = PosteChoisi Then
                    IndiceEnCours = IndiceEnCours + 1
                    If IndiceEnCours = IndiceChoisi Then
                        AgentPoste = CellulePoste.Offset(0, ColonneAgent - ColonneMois)
                        Exit For
                    End If
                 End If
             Next CellulePoste
          End If
           Set AireJournee = Nothing
 
     End With
     Set FeuilleMois = Nothing
 
 
End Function

La procédure

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
Option Explicit
 
Public MatricePoste As Variant
 
Sub RerchercherLesAgentsDUnPoste(ByVal NomFeuilleMois As String, ByVal TitreFeuille As Long, ByVal JourChoisi As Date, ByVal PosteChoisi As String)
 
Dim FeuilleMois As Worksheet
Dim I As Long
Dim ColonneMois As Long, DerniereColonne As Long, DerniereLigne As Long, ColonneAgent As Long
Dim AireJournee As Range, CellulePoste As Range
 
     Set FeuilleMois = Sheets(NomFeuilleMois)
 
     With FeuilleMois
          ColonneAgent = 1
          DerniereLigne = .Cells(.Rows.Count, ColonneAgent).End(xlUp).Row
          DerniereColonne = .Cells(TitreFeuille, .Columns.Count).End(xlToLeft).Column
 
          ColonneMois = 0
          For I = 1 To DerniereColonne
               If .Cells(TitreFeuille, I) = JourChoisi Then ColonneMois = I
          Next I
          If ColonneMois = 0 Then Exit Sub
          Set AireJournee = .Range(.Cells(TitreFeuille + 1, ColonneMois), .Cells(DerniereLigne, ColonneMois))
          If WorksheetFunction.CountIf(AireJournee, PosteChoisi) > 0 Then
             ReDim MatricePoste(WorksheetFunction.CountIf(AireJournee, PosteChoisi) - 1)
             I = 0
             For Each CellulePoste In AireJournee
                 If CellulePoste = PosteChoisi Then
                    MatricePoste(I) = CellulePoste.Offset(0, ColonneAgent - ColonneMois)
                    I = I + 1
                 End If
             Next CellulePoste
          End If
          Set AireJournee = Nothing
 
     End With
     Set FeuilleMois = Nothing
 
End Sub

La macro événementielle dans le module de l'onglet LIGNE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
Option Explicit
 
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
 
Dim J As Integer
 
 If Target.Count > 1 Then Exit Sub
 
    If Not Intersect(Target, Range("G6:I6")) Is Nothing Then
         With ActiveSheet
              RerchercherLesAgentsDUnPoste .Range("MoisPlanning"), 8, Range("G4"), Target
              For J = LBound(MatricePoste, 1) To UBound(MatricePoste, 1)
                 Target.Offset(J + 1, 0) = MatricePoste(J)
              Next J
         End With
 
    End If
 
End Sub

[Xavier_Glaize]

Bonjour Eric
merci pour cette réponse matinale, la solution VBA n'était pas du tout exclu ni l'intervention sur le fichier où je récupère les données donc merci d'avoir résolu mes problèmes .
je vais essayer de comprendre tout le code installé, où, et pourquoi, afin de maitriser tous les événements et pouvoir debugger a l'utilisation.

encore merci

[Xavier_Glaize]

je vais essayer de comprendre tout le code installé, où, et pourquoi

bon ben là je suis a la rue le code donné dans les 2 solutions marche parfaitement mais pour moi il est trop complexe et je n'arrive pas a le faire vivre, en effet après avoir la liste des gens qui bossent en DP0512 je souhaite que celle en poste DP0513 soit lu et je n'arrive pas ajouter cette fonction au code en formules matriciel j'ai réussi avec une solution SI

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(AgentPoste(MoisPlanning;8;$D$4;D$7;3)<> "";AgentPoste(MoisPlanning;8;$D$4;D$7;3);AgentPoste(MoisPlanning;8;$D$4;D$6;1))

sur la solutions sub là je pige rien j'ai bien essayer de modifier target car je ne vois pas ou on déclarer sa valeur mais je n'arrive pas a faire donner les valeurs a la suite de la première valeur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
 
 
Dim J As Integer
Dim K As Integer
 
 If Target.Count > 1 Then Exit Sub
 
    If Not Intersect(Target, Range("G7:I7")) Is Nothing Then
         With ActiveSheet
              RerchercherLesAgentsDUnPoste .Range("MoisPlanning"), 8, Range("G$4"), Target
              For J = LBound(MatricePoste, 1) To UBound(MatricePoste, 1)
                 Target.Offset(J + 1, 0) = MatricePoste(J)
              Next J
              RerchercherLesAgentsDUnPoste .Range("MoisPlanning"), 8, Range("G$4"), "DP0513"
              For K = LBound(MatricePoste, range("D$5").value) To UBound(MatricePoste, range("D$5").value)
                 Target.Offset(K + 1, 0) = MatricePoste(K)
              Next K
         End With
 
 
    End If
 
End Sub

enfin bon voilà, je sais ce que vous allez me dire il aurait fallu donner tous les éléments dès le début mais j'avance pas à pas et il me reste pleins d'éléments a travailler sur cette matrice, en plus je suis inquiet pour les nombreuses modifications "a la main" à faire sur le planning.
je voulais utiliser un fichier en réseau et ça va demander pas mal de boulot car le planning est modifié toutes les semaines, pas tout, mais quelques lignes c'est donc a refaire a chaque fin de semaine, si je pouvais ne rien toucher ou presque ce serait plus simple.
surtout qu'il me reste encore 2 fichiers a lire avec des données a rapatrier donc si a chaque fois je dois remanier les fichiers sources ça va être galère.

[Invité]

Il n'y a pas lieu de modifier les fonctions. Ce sont les valeurs qu'il faut changer dans les cellules.
Je viens de remplacer le poste DP0512 par DP0513 en D6 et G6. Et J'ai appliqué un clic droit sur G6. Nb : Petite anomalie à partir du clic droit : il faudrait effacer les cellules de la colonne.

Pièce jointe 342315

La correction à apporter dans le macro événementielle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
Option Explicit
 
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
 
Dim J As Integer
 
 If Target.Count > 1 Then Exit Sub
 
    If Not Intersect(Target, Range("G6:I6")) Is Nothing Then
         With ActiveSheet
              RerchercherLesAgentsDUnPoste .Range("MoisPlanning"), 8, Range("G4"), Target
              Range(Target.Offset(1, 0), Target.Offset(12, 0)).ClearContents
              For J = LBound(MatricePoste, 1) To UBound(MatricePoste, 1)
                 Target.Offset(J + 1, 0) = MatricePoste(J)
              Next J
         End With
 
    End If
 
End Sub

[Xavier_Glaize]

je me suis mal expliqué
je souhaite garder les DP0512 et ajouté a la suite DP0513

comme ceci


et ce n'est que le début car je vais surement avoir besoin des autres postes mais le truc génial c'est qu'avec ce code je dissocie bien les différents postes car j'ai la formules NB SI qui me la ramène et j'en aurais besoin pour la suite du développement.

[Invité]

La fonction AgentPoste ne peut plus être utilisée pour faire cela. Il faut en effet sélectionner une aire au lieu d'une valeur comme dans la fonction AgentPosteV2 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
Option Explicit
 
Function AgentPosteV2(ByVal NomFeuilleMois As String, ByVal TitreFeuille As Long, ByVal JourChoisi As Date, ByVal AirePostesChoisis As Range, ByVal IndiceChoisi As Integer) As String
 
Dim FeuilleMois As Worksheet
Dim I As Long
Dim ColonneMois As Long, DerniereColonne As Long, DerniereLigne As Long, ColonneAgent As Long
Dim AireJournee As Range, CellulePoste As Range, CellulePostesChoisis As Range
Dim IndiceEnCours As Integer
 
     Application.Volatile
     Set FeuilleMois = Sheets(NomFeuilleMois)
     With FeuilleMois
          ColonneAgent = 1
          DerniereLigne = .Cells(.Rows.Count, ColonneAgent).End(xlUp).Row
          DerniereColonne = .Cells(TitreFeuille, .Columns.Count).End(xlToLeft).Column
 
          ColonneMois = 0
          For I = 1 To DerniereColonne
               If .Cells(TitreFeuille, I) = JourChoisi Then ColonneMois = I
          Next I
          If ColonneMois = 0 Then Exit Function
          Set AireJournee = .Range(.Cells(TitreFeuille + 1, ColonneMois), .Cells(DerniereLigne, ColonneMois))
 
          For Each CellulePoste In AireJournee
              For Each CellulePostesChoisis In AirePostesChoisis
                 If CellulePoste = CellulePostesChoisis Then
                    IndiceEnCours = IndiceEnCours + 1
                    If IndiceEnCours = IndiceChoisi Then
                        AgentPosteV2 = CellulePoste.Offset(0, ColonneAgent - ColonneMois)
                        Exit Function
                    End If
                 End If
              Next CellulePostesChoisis
          Next CellulePoste
          Set AireJournee = Nothing
     End With
     Set FeuilleMois = Nothing
 
End Function

En ajoutant une colonne pour indiquer l'indice du poste, la fonction est automatiquement paramétrée avec cette valeur et elle peut être mise en place sur les 12 lignes. Nb : On peut aussi ne pas ajouter cette colonne ni la ligne 5, cela marche tout pareil, mais disons que c'est plus clair.

Attention !!! Vos données ne semblent pas structurées selon le même modèle, on retrouve des DP0513 avec un :DP0513, si cette valeur correspond bien à DP0513, il vous faut faire un sérieux nettoyage dans l'onglet de données.


Pièce jointe 342372

[Xavier_Glaize]

Bonjour Eric,
merci de travailler le dimanche pour moi...
pour vous répondre sans avoir encore regarder les modifs:

La fonction AgentPoste ne peut plus être utilisée pour faire cela. Il faut en effet sélectionner une aire au lieu d'une valeur comme dans la fonction AgentPosteV2

effectivement le résultat semble au rendez vous et ça me laisse de la latitude pour ajouter ou échangez des valeurs de cette fonction.

[COLOR="#FF0000"]En ajoutant une colonne pour indiquer l'indice du poste, la fonction est automatiquement paramétrée avec cette valeur et elle peut être mise en place sur les 12 lignes. Nb : On peut aussi ne pas ajouter cette colonne ni la ligne 5, cela marche tout pareil, mais disons que c'est plus clair.

l'ajout de ligne et/ou colonne ne me perturbe pas c'est un fichier destiné a l'impression dans un premier temps et pour l'exploitation des infos sur les onglets suivants nous n'avons plus besoin de cette matrice.

Attention !!! Vos données ne semblent pas structurées selon le même modèle, on retrouve des DP0513 avec un P0513, si cette valeur correspond bien à DP0513, il vous faut faire un sérieux nettoyage dans l'onglet de données.

oui, je m'en suis aperçu lors des contrôles de cohérences et mon intention est bien de rajouter une ligne du type "rechercher et remplacer" sur les postes qui commence par ":"