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
| Option Explicit
Sub test()
Dim a, w(), i As Long, col As Byte
Dim Sl As Object, ws As Worksheet
Set Sl = CreateObject("System.Collections.SortedList")
col = 0
For Each ws In Worksheets([{"10cm","20cm","50cm","100cm","Tmax, Tmin, Tgmin"}])
a = ws.Range("A1").CurrentRegion.Value2
col = col + 2
For i = 2 To UBound(a, 1)
If Not Sl.Contains(a(i, 1)) Then
ReDim w(1 To 13)
w(1) = a(i, 1)
Else
w = Sl(a(i, 1))
End If
w(col) = a(i, 2): w(col + 1) = a(i, 3)
If ws.Name = "Tmax, Tmin, Tgmin" Then
w(col + 2) = a(i, 4): w(col + 3) = a(i, 5)
End If
Sl(a(i, 1)) = w
Next
Next
With Sheets("Bilan")
With .Range("a4").CurrentRegion.Offset(2)
.ClearContents
With .Resize(Sl.Count)
.Columns(1).NumberFormat = "dd/mm/yyyy"
For col = 2 To 10 Step 2
.Columns(col).NumberFormat = "h:mm"
Next
End With
End With
For i = 0 To Sl.Count - 1
.Rows(i + 6).Value = Sl.GetByIndex(i)
Next
End With
Set Sl = Nothing
End Sub |
Partager