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
| Sub testBB()
Application.ScreenUpdating = False
Dim F1 As Worksheet
Dim F2 As Worksheet
Set F1 = Sheets("Feuil1")
Set F2 = Sheets("RESULTAT")
F2.Cells.ClearContents
Dim i As Long
On Error Resume Next
Set d = CreateObject("Scripting.Dictionary")
F2.Cells(1, 1).Resize(1, 10) = Array("N° Rapport", "Date :", "Site", "Secteur", "Constats 1", "Constats 2", "Constats 3", "Constats 4", "Constats 5", "Constats 6")
TblBD = F1.Range("A2:E" & F1.Range("A" & Rows.Count).End(xlUp).Row)
For i = 1 To UBound(TblBD)
c = TblBD(i, 1) & "|" & TblBD(i, 2) & "|" & TblBD(i, 3) & "|" & TblBD(i, 4)
d(c) = d(c) + 1
Next i
F2.Range("A2").Resize(d.Count) = Application.Transpose(d.keys)
F2.Range("K2").Resize(d.Count) = Application.Transpose(d.items)
Application.DisplayAlerts = False
F2.Range("A2").Resize(d.Count).TextToColumns Other:=1, OtherChar:="|"
Dim derlig As Long
Dim J As Long
derlig = F2.Cells(Rows.Count, 1).End(xlUp).Row
For J = 2 To derlig
X = (F2.Cells(J, "K") + 4)
For col = 5 To X
F2.Cells(J, col) = "OK"
Next col
Next J
F2.Columns("K:K").Delete Shift:=xlToLeft
Set d = Nothing
Application.ScreenUpdating = True
F2.Select
End Sub |
Partager