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
| Sub Journee()
Dim ligne, colonne, lfin, cfin, cref As Integer
Dim CMax, Max, CMin, Min, Tarr, compare
cfin = InputBox("rentrer le numéro de la dernière colonne de données")
lfin = Cells(65335, 2).End(xlUp).Row
ligne = 9
While Worksheets("journ").Cells(ligne, 1) <> ""
If Worksheets("journ").Cells(ligne, 4) = "" Then
Max = "00:00:00"
Else
Max = Format(Worksheets("journ").Cells(ligne, 4), "hh:nn:ss")
End If
If Worksheets("journ").Cells(ligne, cfin) = "" Then
Min = "24:59:59"
Else
Min = Format(Worksheets("journ").Cells(ligne, cfin), "hh:nn:ss")
End If
CMax = 4
CMin = cfin
For colonne = 5 To cfin
If Worksheets("journ").Cells(ligne, colonne) = "" Then
compare = "00:00:00"
Else
compare = Format(Worksheets("journ").Cells(ligne, colonne), "hh:nn:ss")
If DateDiff("s", Max, compare) > 0 Then
Max = compare
CMax = colonne
End If
End If
Next
For colonne = cfin - 1 To 4 Step -1
If Worksheets("journ").Cells(ligne, colonne) = "" Then
compare = "00:00:00"
Else
compare = Format(Worksheets("journ").Cells(ligne, colonne), "hh:nn:ss")
If DateDiff("s", Min, compare) < 0 Then
Min = compare
CMin = colonne
End If
End If
Next
'Tarr = Max
'cref = CMax
'If Min > Tarr + tret Then
MsgBox Min & "colonne" & CMin
If Max = "00:00:00" Then
Worksheets("journ").Cells(ligne, 21) = "Pas d'horaire"
Else
Worksheets("journ").Cells(ligne, 21) = Max
Worksheets("journ").Cells(ligne, 20) = CMax
End If
ligne = ligne + 1
Wend
End Sub |
Partager