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
| Sub Concatest()
USF1.Show 0
USF1.Repaint
Dim NewSheet As Worksheet
Set NewSheet = Sheets.Add(After:=Sheets(Sheets.Count))
NewSheet.Name = "ListeNette"
Dim Plage As Range, C As Range, Ligne As Variant, Ctr As Integer
With Sheets("Liste_Alarmes")
.Range("A4", .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 8).Copy Sheets("ListeNette").[A4]
Set Plage = .Range("B5", .Cells(.Rows.Count, 2).End(xlUp))
End With
With Sheets("ListeNette")
.Range("A5", .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 8).RemoveDuplicates Columns:=2
.Range("A5", .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 4).Resize(, 4).ClearContents
For Each C In Plage
Ligne = Application.Match(C, .[B:B], 0)
If IsNumeric(Ligne) Then
For I = 3 To 7
If InStr(1, .Cells(Ligne, I + 2), C.Offset(, I)) = 0 Then
.Cells(Ligne, I + 2) = .Cells(Ligne, I + 2) & Chr(10) & C.Offset(, I)
End If
Next I
End If
Next C
For Each C In .Range("A5", .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 4).Resize(, 4)
If Len(C.Value) > 0 Then C.Value = Mid(C.Value, 2, Len(C.Value) - 1)
If C.Column = 5 Then
Ctr = Ctr + 1
.Cells(C.Row, 1) = Ctr
End If
Next C
End With
With Sheets("Liste_Alarmes")
.Range("A5:H5", .Cells(.Rows.Count, 1).End(xlUp)).Clear
End With
With Sheets("ListeNette")
.Range("A5:H5", .Cells(.Rows.Count, 1).End(xlUp)).Copy Sheets("Liste_Alarmes").[A5]
End With
Application.DisplayAlerts = False
Sheets("ListeNette").Delete
Application.DisplayAlerts = True
Unload USF1
End Sub |
Partager