Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$3" Then Call GetNomPersonnel
End Sub
Private Sub GetNomPersonnel()
Dim S As Worksheet
Dim R As Range
Dim rG As Range
Dim rJ As Range
Dim rN As Range
Dim DateCible As Date
Dim var
Dim i&
Dim j&
Dim A$
'---
If Not IsDate(Range("a3")) Then Exit Sub
DateCible = CDate(Range("a3"))
'### SPP ###
Range("I6:I17,I19:I24,I26:I28").ClearContents
Set rG = Range("I6")
Set rJ = Range("I19")
Set rN = Range("I26")
'---
Set S = Sheets("SPP")
Set R = S.[a1].CurrentRegion
var = R
'---
For i& = 1 To UBound(var, 1)
If IsDate(var(i&, 5)) Then
If DateCible = CDate(var(i&, 5)) Then
For j& = 11 To UBound(var, 2)
'--- G ---
If var(i&, j&) = "G" Then
A$ = var(3, j&)
A$ = Replace(A$, Chr(10), Space(1))
rG = A$
Set rG = rG.Offset(1, 0)
End If
'--- J ---
If var(i&, j&) = "J" Then
A$ = var(3, j&)
A$ = Replace(A$, Chr(10), Space(1))
rJ = A$
Set rJ = rJ.Offset(1, 0)
End If
'--- N ---
If var(i&, j&) = "N" Then
A$ = var(3, j&)
A$ = Replace(A$, Chr(10), Space(1))
rN = A$
Set rN = rJ.Offset(1, 0)
End If
Next j&
End If
End If
Next i&
'### SPV ###
Range("K6:K12,K14:K20,K22:K28").ClearContents
Set rG = Range("K6")
Set rJ = Range("K14")
Set rN = Range("K22")
'---
Set S = Sheets("SPV")
Set R = S.UsedRange
var = R
'---
For i& = 1 To UBound(var, 1)
If IsDate(var(i&, 2)) Then
If DateCible = CDate(var(i&, 2)) Then
For j& = 14 To UBound(var, 2)
'--- G ---
If var(i&, j&) = "G" Then
A$ = var(3, j&) & Space(1) & var(4, j&)
A$ = Replace(A$, Chr(10), Space(1))
rG = A$
Set rG = rG.Offset(1, 0)
End If
'--- J (JS ou JW)---
If var(i&, j&) = "JS" Or var(i&, j&) = "JW" Then
A$ = var(3, j&) & Space(1) & var(4, j&)
A$ = Replace(A$, Chr(10), Space(1))
rJ = A$
Set rJ = rJ.Offset(1, 0)
End If
'--- N ---
If var(i&, j&) = "N" Then
A$ = var(3, j&) & Space(1) & var(4, j&)
A$ = Replace(A$, Chr(10), Space(1))
rN = A$
Set rN = rN.Offset(1, 0)
End If
Next j&
End If
End If
Next i&
End Sub