Sub Dran()
[E13:E250].FormulaR1C1 = "=AND(" & ExisteDans(2, 2) & "," & ExisteDans(2, 3) & "," & ExisteDans(2, 4) & ")*1" _
& vbLf & "+AND(" & ExisteDans(3, 2) & "," & ExisteDans(3, 3) & "," & ExisteDans(3, 4) & ")*2" _
& vbLf & "+AND(" & ExisteDans(4, 2) & "," & ExisteDans(4, 3) & "," & ExisteDans(4, 4) & ")*4"
End Sub
Private Function ExisteDans(ByVal ColQuoi As Long, ByVal ColOù As Long) As String
ExisteDans = "ISNUMBER(MATCH(RC" & ColQuoi & ",OFFSET(R" & IIf(ColOù >= ColQuoi, "[-8]C" & ColOù & ":R[-3]C", "C" & ColOù & ":R[5]C") & ColOù & ",-MOD(ROW()-5,8),0),0))"
End Function
[>0]"+ de 24h";
=ET(ESTNUM(EQUIV($B13;DECALER($B5:$B10;-MOD(LIGNE()-5;8);0);0));ESTNUM(EQUIV($B13;DECALER($C5:$C10;-MOD(LIGNE()-5;8);0);0));ESTNUM(EQUIV($B13;DECALER($D5:$D10;-MOD(LIGNE()-5;8);0);0)))*1
+ET(ESTNUM(EQUIV($C13;DECALER($B13:$B18;-MOD(LIGNE()-5;8);0);0));ESTNUM(EQUIV($C13;DECALER($C5:$C10;-MOD(LIGNE()-5;8);0);0));ESTNUM(EQUIV($C13;DECALER($D5:$D10;-MOD(LIGNE()-5;8);0);0)))*2
+ET(ESTNUM(EQUIV($D13;DECALER($B13:$B18;-MOD(LIGNE()-5;8);0);0));ESTNUM(EQUIV($D13;DECALER($C13:$C18;-MOD(LIGNE()-5;8);0);0));ESTNUM(EQUIV($D13;DECALER($D5:$D10;-MOD(LIGNE()-5;8);0);0)))*4
Feuil1.[E13:E250].FormulaR1C1 = _
"=AND(ISNUMBER(MATCH(RC2,OFFSET(R[-8]C2:R[-3]C2,-MOD(ROW()-5,8),0),0)),ISNUMBER(MATCH(RC2,OFFSET(R[-8]C3:R[-3]C3,-MOD(ROW()-5,8),0),0)),ISNUMBER(MATCH(RC2,OFFSET(R[-8]C4:R[-3]C4,-MOD(ROW()-5,8),0),0)))*1
+AND(ISNUMBER(MATCH(RC3,OFFSET(RC2:R[5]C2,-MOD(ROW()-5,8),0),0)),ISNUMBER(MATCH(RC3,OFFSET(R[-8]C3:R[-3]C3,-MOD(ROW()-5,8),0),0)),ISNUMBER(MATCH(RC3,OFFSET(R[-8]C4:R[-3]C4,-MOD(ROW()-5,8),0),0)))*2
+AND(ISNUMBER(MATCH(RC4,OFFSET(RC2:R[5]C2,-MOD(ROW()-5,8),0),0)),ISNUMBER(MATCH(RC4,OFFSET(RC3:R[5]C3,-MOD(ROW()-5,8),0),0)),ISNUMBER(MATCH(RC4,OFFSET(R[-8]C4:R[-3]C4,-MOD(ROW()-5,8),0),0)))*4"