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
| Sub Marquage()
Dim DerLig_T1 As Long, DerLig_T2 As Long, i As Long, Cpt As Long
Dim o As Object, n As Object
Application.ScreenUpdating = False
Columns(11).ClearContents
'Contrôle de la présence de 5 lignes identiques du tableau 2
DerLig_T1 = Columns("A").Find(What:="*", SearchDirection:=xlPrevious).Row
DerLig_T2 = Columns("F").Find(What:="*", SearchDirection:=xlPrevious).Row
Set T2 = Range("F2:I" & DerLig_T2)
Range("J2:J" & DerLig_T2).FormulaR1C1 = "=RC[-4]&"" ""&RC[-3]&"" ""&RC[-2]&"" ""&RC[-1]"
Range("J2:J" & DerLig_T2).Value = Range("J2:J" & DerLig_T2).Value
For i = 2 To DerLig_T2
If Cells(i, "J") = Cells(i + 1, "J") And Cells(i, "J") = Cells(i + 2, "J") And _
Cells(i, "J") = Cells(i + 3, "J") And Cells(i, "J") = Cells(i + 4, "J") Then
End If
Next i
'Recherche dans le tableau 1
With Columns(10)
Set o = .Find(" ")
If Not o Is Nothing Then
o_Adr = o.Address
Do
Cells(o.Row, "K").FormulaArray = "=IFERROR(IF(RC[-1]<>"""",MATCH(RC10,R1C1:R" & DerLig_T1 & "C1&"" ""&R1C2:R" & DerLig_T1 & "C2&"" ""&R1C3:R" & DerLig_T1 & "C3&"" ""&R1C4:R" & DerLig_T1 & "C4,0),""""),"""")"
If Cells(o.Row, "K").Value = "" Then
Cells(o.Row, "K").Value = "non"
Else
Cells(o.Row, "K").Value = "oui"
End If
Set o = .FindNext(o)
Loop While Not o Is Nothing And o_Adr <> o.Address
End If
End With
'Suppression des "oui" consécutifs
Cpt = 1
For i = DerLig_T2 To 2 Step -1
If Cpt < 5 Then
If Cells(i, "K") = "oui" And Cells(i - 1, "K") = "oui" Then
Cells(i, "K") = ""
Cpt = Cpt + 1
Else
Cpt = 1
End If
Else
Cpt = 1
End If
Next i
'Suppression des "non" consécutifs
For i = DerLig_T2 To 2 Step -1
If Cells(i, "K") = "non" And Cells(i - 1, "K") = "non" Then Cells(i, "K") = ""
Next i
Columns(10).ClearContents
Set o = Nothing
End Sub |
Partager