Procédure de détection des chevauchements et constitution d'un tableau autonome des chevauchements (pour ordre et recherches ultérieures).
Function ConverTemps(dh As String) As Double
Dim dhc
dhc = Split(dh)
ConverTemps = CLng(DateValue(dhc(0))) + TimeValue(dhc(1))
End Function
Sub Chevauchements()
Dim dUsa As Object, dAct As Object, k, itm, no%, u%, n%, i%
Dim Tmp(), Tch(), ch%
Set dUsa = CreateObject("Scripting.Dictionary")
Set dAct = CreateObject("Scripting.Dictionary")
'Recueil données: dico Usagers (nb activ. recensées), dico Activités-Usagers (horodatage, activité, encadrants)
With ActiveSheet
n = .Cells(.Rows.Count, 5).End(xlUp).Row
For i = 3 To n
If .Cells(i, 5) <> "" Then
itm = Array(ConverTemps(.Cells(i, 1)), ConverTemps(.Cells(i, 2)), .Cells(i, 4), .Cells(i, 7))
k = Split(.Cells(i, 5), ",")
For u = 0 To UBound(k) - 1
k(u) = Trim(k(u)): no = dUsa(k(u)) + 1
dUsa(k(u)) = no: k(u) = k(u) & "-" & no
dAct(k(u)) = itm
Next u
End If
Next i
End With
'Traitement par Usager
For Each k In dUsa.keys
u = CInt(dUsa(k))
'Tableau Activités (horodatages, activités, encadrants) de l'Usager
ReDim Tmp(3, u)
For no = 1 To u
itm = dAct(k & "-" & no)
For i = 0 To 1
Tmp(i, no) = Val(Replace(itm(i), ",", "."))
Tmp(i + 2, no) = itm(i + 2)
Next i
Next no
'Tri tableau par horodatage début Activité (croissant)
For no = 1 To u - 1
For n = no + 1 To u
If Tmp(0, n) < Tmp(0, no) Then
For i = 0 To 3
Tmp(i, 0) = Tmp(i, n)
Tmp(i, n) = Tmp(i, no)
Tmp(i, no) = Tmp(i, 0)
Next i
End If
Next n
Next no
'Comparaison horodatage début Activité à fin Activité précédente
'Si chevauchement, insertion données (Usager, horodatages, activités, encadrants) dans Tableau Chevauchements
For no = 2 To u
If Tmp(0, no) < Tmp(1, no - 1) Then
ReDim Preserve Tch(8, ch)
Tch(0, ch) = k
For i = 0 To 3
Tch(i + 1, ch) = Tmp(i, no - 1)
Tch(i + 5, ch) = Tmp(i, no)
Next i
ch = ch + 1
End If
Next no
Next k
'Affichage Tableau Chevauchements
With Worksheets.Add(after:=ActiveSheet)
With .Range("A3").Resize(ch, 9)
.Value = WorksheetFunction.Transpose(Tch)
.NumberFormat = "dd/mm/yyyy hh:mm"
.Columns.AutoFit
.Borders.Weight = xlThin
End With
itm = Split("Usager;Début activ.(1);Fin activ.(1);Activité (1);Encadrants (1);Début activ.(2);" _
& "Fin activ.(2);Activité (2);Encadrants (2)", ";")
With .Range("A2:I2")
.Value = itm
.HorizontalAlignment = xlCenter
.Font.Italic = True
End With
With .Range("A1")
.Value = "Chevauchements d'activités détectés"
.HorizontalAlignment = xlCenter
With .Font
.Size = 14: .Bold = True
End With
End With
.Range("A1:I1").Merge
.Activate
End With
End Sub