Tu cherches la difficulté...
J'ai pris une autre voie.
Sub EtatsVoyants()
Dim d As Object, dh, itm$, v$, w$, e%, ev%, k%, j%, ii%, n&, i&
Dim LE()
Set d = CreateObject("Scripting.Dictionary")
With Worksheets("F392")
n = .Cells(.Rows.Count, 1).End(xlUp).Row
k = .Cells(1, .Columns.Count).End(xlToLeft).Column
ReDim LE(k - 1, 7)
For j = 2 To k
v = Split(.Cells(1, j), ".")(1)
LE(j - 1, 0) = v
For i = 3 To n
If .Cells(i, j) <> "" And IsNumeric(.Cells(i, j)) Then
e = .Cells(i, j): w = v & "Etat" & e: dh = Split(.Cells(i, 1))
itm = Format(DateValue(dh(0)), "dd/mm/yy") & " " _
& Format(TimeValue(dh(1)), "hh""h""mm") & " à "
Do While .Cells(i + ii + 1, j) = e And i + ii < n
ii = ii + 1
Loop
dh = Split(.Cells(i + ii, 1))
itm = itm & Format(DateValue(dh(0)), "dd/mm/yy") & " " _
& Format(TimeValue(dh(1)), "hh""h""mm")
If d.exists(w) Then ev = CInt(d(w)) + 1 Else ev = 1
d(w) = ev: d(w & "-" & ev) = itm
i = i + ii: ii = 0
End If
Next i
Next j
End With
For i = 1 To 7
LE(0, i) = "Etat " & i - 1
Next i
For j = 1 To UBound(LE, 1)
v = LE(j, 0) & "Etat"
For i = 1 To 7
w = v & i - 1
If d.exists(w) Then
ev = CInt(d(w)): w = w & "-": itm = ""
For e = 1 To ev
itm = itm & Chr(10) & d(w & e)
Next e
LE(j, i) = Replace(itm, Chr(10), "", 1, 1)
Else
LE(j, i) = "néant"
End If
Next i
Next j
With Worksheets.Add(after:=Worksheets("F392")).Range("A1").Resize(UBound(LE, 1), 8)
.Value = LE
.Columns.ColumnWidth = 28.29
.Columns(1).ColumnWidth = 8
.Rows.AutoFit
.VerticalAlignment = xlCenter
.Rows(1).HorizontalAlignment = xlCenter
.Borders.Weight = xlThin
End With
End Sub
Je voulais déboucher sur quelque chose d'un peu différent mais j'ai eu la flemme de faire le complément nécessaire...
Cordialement.