Function HeureService(Rng As Range, Debut As Boolean)
Dim prenom As String, Journee As String, Lieu As String, Col As Integer, Lig As Long, Pos As Byte
Dim Horaire As Date, PourYParvenir As Date, HService As Date, c As Range
Application.Volatile ' Ne pas oublier de mettre cette instruction, pour un recalcul systématique
prenom = "Pascal" 'ComboChauffeurs.Value
Lig = Rng.Row
' Récupère le numéro de colonne selon critère
If Debut = True Then
Col = Range("B" & Lig).End(xlToRight).Column
Else
Col = Range("N" & Lig).End(xlToLeft).Column
End If
'
If Col >= 13 Or Col <= 2 Then 'colonne M (17:00)
HService = "00:00"
Else
Journee = Cells(Lig, Col)
Horaire = CDate(Left(Journee, 5))
Pos = InStr(1, Journee, Chr(10))
Lieu = Mid(Journee, 7, Pos - 7)
With Sheets("Delai").Range("A2:A" & Sheets("Delai").Range("A65536").End(xlUp).Row)
Set c = .Find(Lieu, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then PourYParvenir = CDate(c.Offset(0, 1))
If Debut = True Then
HService = Horaire - PourYParvenir
Else
HService = Horaire + PourYParvenir
End If
End With
End If
HeureService = HService
End Function
Function LieuEtHeure(Rng As Range, Debut As Boolean)
Dim Col As Integer, Lig As Long, Pos As Integer
Application.Volatile ' Ne pas oublier de mettre cette instruction, pour un recalcul systématique
Lig = Rng.Row
' Récupère le numéro de colonne selon critère
If Debut = True Then
Col = Range("B" & Lig).End(xlToRight).Column
Else
Col = Range("N" & Lig).End(xlToLeft).Column
End If
Col = Range("B" & Lig).End(xlToRight).Column
If Col >= 13 Or Col <= 2 Then
LieuEtHeure = "-"
Else
Pos = InStr(1, Cells(Lig, Col), Chr(10))
LieuEtHeure = Left(Cells(Lig, Col), Pos - 1)
End If
End Function