Intersection en fonction de deux valeurs (ligne - colonne)

**QuestVba** · 05/02/2013, 08h00

Bonjour le Forum,

Je mets tout mais vous trouverez certainement la réponse plus facilement que moi. J'ai hérité de ces trois fichiers.

Copie de copie de essai congébis 761 : permet d'ouvrir l'écran d'accueil pour demande de congé
Congé761 : fichier permettant de gérer ce qui a été demandé et quand
Classeur des congés 761: est un tableau reprenant
C1:AR1 : prénom du personnel
C2:AR2 : numéro d'agent
Colonne A : numéro de la date se trouvant dans B

Pour le moment, le fichier "Copie de Copie de essai congésbis 761" a un UserForm "periode" qui permet l'encodage et après avoir cliquer sur "envoi de la demande", les données sont introduites dans le tableau. Mais ce n'est pas très facile d'ajouter ou de retirer un agent car cela fonctionne avec le code colonne de chacun. Je m'explique le système sait que Michèle (00000332) est le code "2" ce qui correspond à la colonne C. Mais si un changement intervient, je dois tout changer. Voci le code:

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
Private Sub CommandButton1_Click()
    Sheets("test").Activate
    Range("f11").Activate
    ActiveCell = ComboBox1.Value
    Range("j11").Activate
    ActiveCell = ComboBox2.Value
    Range("b25").Activate
    ActiveCell = TextBox1.Value
 
'   valeur des motifs
    Range("f19").Activate
    ActiveCell = ComboBox3.Value
    Range("i19").Activate
    ActiveCell = ComboBox4.Value
    Range("l19").Activate
    ActiveCell = ComboBox5.Value
 
    période.Hide
 
'   test # nature
    Dim nat1, nat2, nat3
    nat1 = Cells(27, 8).Value
    nat2 = Cells(27, 11).Value
    nat3 = Cells(27, 14).Value
 
    If nat1 = 1 And nat2 = 1 Then MsgBox "Erreur: nombre demi-journées"
    If nat1 = 1 And nat3 = 1 Then MsgBox "Erreur: nombre demi-journées"
    If nat1 = 1 And nat2 = 1 Then période.Show
    If nat1 = 1 And nat3 = 1 Then période.Show
 
    If nat1 = 0 And nat2 = 0 And nat3 = 0 Then MsgBox "Erreur: vous devez introduire la nature du congé"
    If nat1 = 0 And nat2 = 0 And nat3 = 0 Then période.Show
 
'   envoi des données dans BD_Congés
    Dim dat101, dat102, nature1, nature2, nature3, natureX, agentX, comm
    agentX = Cells(2, 1).Value
    dat101 = Cells(17, 1).Value
    dat102 = Cells(17, 2).Value
    nature1 = Cells(19, 6).Value
    nature2 = Cells(19, 9).Value
    nature3 = Cells(19, 12).Value
    comm = Cells(25, 2).Value
    If nature1 <> "" Then natureX = nature1
    If nature1 = "" Then natureX = nature2 + "/" + nature3
    If agentX = "00061141" Or agentX = "00000332" Or agentX = "00061404" And nature1 <> "" Then natureX = natureX + "!"
'    If agentX = "00061141" Or agentX = "00000332" Or agentX = "00061404" And nature1 = "" Then natureX = nature2 + "/" + nature3 + "!"
 
    '   permet d'ouvrir le fichier en lecture seule avec boucle si occupé
Line2:
            Workbooks.Open Filename:= _
                "I:\Bruxelles-Brussel\PFG\Pierre V - 610\CALENDRIER DES CONGES DR\Classeur des congés761.xls"
            If ActiveWorkbook.ReadOnly Then ouv = 1 Else ouv = 0
            If ActiveWorkbook.ReadOnly Then ActiveWorkbook.Close 'Application.Quit
            If ouv = 1 Then GoTo Line2
'   fin boucle si occupé
 
    ActiveSheet.Unprotect Password:="onafts"
 
    For i = dat101 To dat102
 
    Columns("A:A").Select
    Range("a1").Activate
    Dim cellagent
 
        If agentX = "00000332" Then cellagent = 2
        If agentX = "00061404" Then cellagent = 3
        If agentX = "00000831" Then cellagent = 4
        If agentX = "00000262" Then cellagent = 5
        If agentX = "00060046" Then cellagent = 6
        If agentX = "00000395" Then cellagent = 7
        If agentX = "00000386" Then cellagent = 8
        If agentX = "00060106" Then cellagent = 9
        If agentX = "00060787" Then cellagent = 10
        If agentX = "00061227" Then cellagent = 11
        If agentX = "00061427" Then cellagent = 12
        If agentX = "00062130" Then cellagent = 13
        If agentX = "00000397" Then cellagent = 14
        If agentX = "00000854" Then cellagent = 15
        If agentX = "00062171" Then cellagent = 16
        If agentX = "00062083" Then cellagent = 17
        If agentX = "00062114" Then cellagent = 18
        If agentX = "00061595" Then cellagent = 19
        If agentX = "00061581" Then cellagent = 20
        If agentX = "00102361" Then cellagent = 21
        If agentX = "00000388" Then cellagent = 22
        If agentX = "00061697" Then cellagent = 23
        If agentX = "00061949" Then cellagent = 24
        If agentX = "00000385" Then cellagent = 25
        If agentX = "00062095" Then cellagent = 26
        If agentX = "00000975" Then cellagent = 27
        If agentX = "00062068" Then cellagent = 28
        If agentX = "00062084" Then cellagent = 29
        If agentX = "00061978" Then cellagent = 30
        If agentX = "00000357" Then cellagent = 31
        If agentX = "00062085" Then cellagent = 32
        If agentX = "00062137" Then cellagent = 33
        If agentX = "00000392" Then cellagent = 34
        If agentX = "00000699" Then cellagent = 35
        If agentX = "00061951" Then cellagent = 36
        If agentX = "00000381" Then cellagent = 37
        If agentX = "00000394" Then cellagent = 38
        If agentX = "00061580" Then cellagent = 39
        If agentX = "00000608" Then cellagent = 40
        If agentX = "00061855" Then cellagent = 41
        If agentX = "00061979" Then cellagent = 42
        If agentX = "00060739" Then cellagent = 43
 
 
        Selection.Find(What:=i, After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
 
        ActiveCell.Offset(rowoffset:=0, columnoffset:=cellagent).Activate
        If ActiveCell <> "" Then GoTo line3
        ActiveCell = natureX
        If comm = "" Then GoTo line3
        ActiveCell.AddComment comm
        ActiveCell.comment.Visible = False
line3:
 
    Next i
    ActiveSheet.Protect Password:="onafts", DrawingObjects:=True, contents:=True, Scenarios:=True
    ActiveWorkbook.Save
    ActiveWindow.Close
 
line123:
 
    Dmd_congés_Vérif.Show
 
End Sub

Ma question: quelle commande faut-il utiliser pour trouver l'intersection entre le nom de la personne (username) en ligne 1 et la date en colonne 1 ?

Je ne sais pas si c'est suffisamment compréhensible. Posez vos questions si un doute ? Merci.

**keygen08** · 05/02/2013, 14h05

Dans copie de copie de essai
Userform periode
Je propose de remplacer ceci

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
45
            'Columns("A:A").Range("a1").Activate
    Range("a1").Activate
    Dim cellagent
        If agentX = "00000332" Then cellagent = 2
        If agentX = "00061404" Then cellagent = 3
        If agentX = "00000831" Then cellagent = 4
        If agentX = "00000262" Then cellagent = 5
        If agentX = "00060046" Then cellagent = 6
        If agentX = "00000395" Then cellagent = 7
        If agentX = "00000386" Then cellagent = 8
        If agentX = "00060106" Then cellagent = 9
        If agentX = "00060787" Then cellagent = 10
        If agentX = "00061227" Then cellagent = 11
        If agentX = "00061427" Then cellagent = 12
        If agentX = "00062130" Then cellagent = 13
        If agentX = "00000397" Then cellagent = 14
        If agentX = "00000854" Then cellagent = 15
        If agentX = "00062171" Then cellagent = 16
        If agentX = "00062083" Then cellagent = 17
        If agentX = "00062114" Then cellagent = 18
        If agentX = "00061595" Then cellagent = 19
        If agentX = "00061581" Then cellagent = 20
        If agentX = "00102361" Then cellagent = 21
        If agentX = "00000388" Then cellagent = 22
        If agentX = "00061697" Then cellagent = 23
        If agentX = "00061949" Then cellagent = 24
        If agentX = "00000385" Then cellagent = 25
        If agentX = "00062095" Then cellagent = 26
        If agentX = "00000975" Then cellagent = 27
        If agentX = "00062068" Then cellagent = 28
        If agentX = "00062084" Then cellagent = 29
        If agentX = "00061978" Then cellagent = 30
        If agentX = "00000357" Then cellagent = 31
        If agentX = "00062085" Then cellagent = 32
        If agentX = "00062137" Then cellagent = 33
        If agentX = "00000392" Then cellagent = 34
        If agentX = "00000699" Then cellagent = 35
        If agentX = "00061951" Then cellagent = 36
        If agentX = "00000381" Then cellagent = 37
        If agentX = "00000394" Then cellagent = 38
        If agentX = "00061580" Then cellagent = 39
        If agentX = "00000608" Then cellagent = 40
        If agentX = "00061855" Then cellagent = 41
        If agentX = "00061979" Then cellagent = 42
        If agentX = "00060739" Then cellagent = 43

Par cela

Code :

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

1
2
3
4
5
6
7
8
9
'permet de rechercher la valeur de "agentX" dans la ligne2
'et de renvoyer le numero de la colonne, celui ci est deduit de un puisque
'la valeur d'agentx 000332 est 2 mais se trouve en colonne 3
 
    Dim col As Integer
        With Feuil1.Rows("2")
        Set n = .Find(agentX, LookIn:=xlValues, LookAt:=xlWhole)
        If Not n Is Nothing Then cellagent = n.Column - 1
    End With

Pour info dans tous le reste, de trés,trés nombreuses lignes pourrait etre supprimer,
afin de faciliter la lecture et la compréhension du code.
ex :

Code :

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

1
2
3
4
5
6
7
    Sheets("test").Activate
    Range("f11").Activate
    ActiveCell = ComboBox1.Value
    Range("j11").Activate
    ActiveCell = ComboBox2.Value
    Range("b25").Activate
    ActiveCell = TextBox1.Value

pourrait avantageusement etre remplacer par

Code :

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

1
2
3
    Sheets("test").Range("f11") = ComboBox1.Value
    Sheets("test").Range("j11") = ComboBox2.Value
    Sheets("test").Range("b25") = TextBox1.Value

Ma question: quelle commande faut-il utiliser pour trouver l'intersection entre le nom de la personne (username) en ligne 1 et la date en colonne 1 ?

Pour repondre a la question

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
 Sub devellopez_net()
 Dim Ligne As Integer
 Dim Colonne As Integer
 Dim Noms As String
 Dim Jour As Date
 
 'recherche de la valeur de colonne
 Noms = "00000532" 'ou range("?") ou Cbox.value
    With Sheets("feuil1").Rows(2)
        Set c = .Find(Noms, LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then Colonne = c.Column
    End With
 
Jour = "41311" 'ou range("?") ou Cbox.value
    With Sheets("feuil1").Range("a:a") 'ou column(1)
        Set c = .Find(Jour, LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then Ligne = c.Row
    End With
 
'intersection de colonne et ligne =
Sheets("feuil1").Cells(Ligne, Colonne).Select
 
 End Sub

J'effectue la recherche plutot sur le code agent que sur le prénom à cause des doublons eventuels.

**QuestVba** · 06/02/2013, 15h32

Bonjour, keygen08,

Excellent

, en plus cela accélère le traitement !!

'permet de rechercher la valeur de "agentX" dans la ligne2
'et de renvoyer le numero de la colonne, celui ci est deduit de un puisque
'la valeur d'agentx 000332 est 2 mais se trouve en colonne 3

Code :

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

1
2
3
4
5
    Dim col As Integer
        With Feuil1.Rows("2")
        Set n = .Find(agentX, LookIn:=xlValues, LookAt:=xlWhole)
        If Not n Is Nothing Then cellagent = n.Column - 1
    End With

L'intersection fonctionne bien

. Merci beaucoup.

Je cherchais bêtement une commande toute faite du style "Intersection(a,b).select" ou autre ....

**Philippe Tulliez** · 06/02/2013, 17h48

Bonjour,
Pour trouver une valeur qui se trouve à l'intersection d'une ligne et colonne, il y a également la combinaison des fonctions INDEX et EQUIV (MATCH pour la version anglaise et VBA)
Imaginons une plage de cellules nommée Points contenant les points, une plage en colonne contenant les matières nommées Matiere et une plage en ligne contenant les Trimestres nommée Trimestre.
Pour chercher les points au Trimestre 2 (Trim 2) pour le cours de Français
Avec Excel

Code :

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

=INDEX(Points;EQUIV("Français";Matiere;0);EQUIV("Trim 2";Trimestre;0))

En VBA sous forme de fonction, utilisable depuis Excel

Code :

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

1
2
3
4
5
6
7
8
9
10
Function CalculPoints(QuelleMatiere As String, QuelTrimestre As String) As Double
 ' =INDEX(Points;EQUIV("Français";Matiere;0);EQUIV("Trim 2";Trimestre;0))
 Dim TablePoints As Range, vMatiere As Range, vTrimestre As Range
 With ThisWorkbook.Worksheets("Index")
  Set TablePoints = .Range("Points"): Set vMatiere = .Range("Matiere"): Set vTrimestre = .Range("Trimestre")
 End With
 With Application.WorksheetFunction
  CalculPoints = .Index(TablePoints, .Match(QuelleMatiere, vMatiere, 0), .Match(QuelTrimestre, vTrimestre, 0))
 End With
End Function

Comment invoquer la fonction
Avec VBA

Code :

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

1
2
3
Sub Calcul()
 MsgBox CalculPoints("Français", "Trim 2")
End Sub

Depuis Excel

Code :

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

=CalculPoints("Français";"Trim 2")

[EDIT]
Tu peux aussi écrire une fonction qui renvoie un objet Range

Code :

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

1
2
3
4
5
6
7
8
9
10
Function CelluleIntersect(QuelleMatiere, QuelTrimestre) As Range
 Dim TablePoints As Range, vMatiere As Range, vTrimestre As Range
 With ThisWorkbook.Worksheets("Index")
  Set TablePoints = .Range("Points"): Set vMatiere = .Range("Matiere"): Set vTrimestre = .Range("Trimestre")
 End With
 With Application.WorksheetFunction
  '.Match(QuelleMatiere, vMatiere, 0)
  Set CelluleIntersect = TablePoints.Cells(.Match(QuelleMatiere, vMatiere, 0), .Match(QuelTrimestre, vTrimestre, 0))
 End With
End Function

Invoquer la fonction

Code :

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

1
2
3
4
5
6
7
8
Sub SelectRange()
 Dim txt As String
 With CelluleIntersect("Français", "Trim 2")
  txt = "Trouvé " & .Value & " à l'adresse " & .Address
  MsgBox txt
 End With
 CelluleIntersect("Français", "Trim 2").Select ' Sélectionner la cellule trouvée
End Sub

**QuestVba** · 07/02/2013, 07h32

Bonjour, Philippe,

Merci pour cet exemple qui répond également à ma question. Il n'y a plus qu'à adapter ....

Et moi qui cherchait je ne sais quoi

.
Il suffit de savoir ....

**Philippe Tulliez** · 07/02/2013, 08h16

Bonjour Lionel,
Merci pour ton appréciation et pour répondre au rêve de Christophe,

je cherchais bêtement une commande toute faite du style "Intersection(a,b).select" ou autre ....

voici une autre approche.
Pour l'exemple
La plage C4:G8 contient en D4:G4 Trim 1 à Trim 4, en cellule C5:C8 les matières Français, Math, Histoire, Géographie et D5:G8 les points
La fonction

Code :

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

1
2
3
4
5
6
Function Intersect(rng As Range, ValeurV, ValeurH)
 Dim f As WorksheetFunction: Set f = Application.WorksheetFunction
 With rng
  Intersect = .Cells(f.Match(ValeurV, .Resize(, 1), 0), f.Match(ValeurH, .Resize(1), 0))
 End With
End Function

Qu'on invoque par

Code :

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

1
2
3
4
Sub TestIntersect()
 Dim rng As Range: With ThisWorkbook.Worksheets("Index"): Set rng = .Range("C4:G8"): End With
 MsgBox Intersect(rng, "Français", "Trim 2")
End Sub