1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| Sub Demo()
Const MAX = 99
Dim CPT(1 To MAX, 0), L%, R%, V, VA, W%, WN%
WN = ThisWorkbook.Worksheets.Count
ReDim COL(1 To MAX, 1 To WN)
For W = 2 To WN
VA = ThisWorkbook.Worksheets(W).Cells(1).CurrentRegion.Value
For R = 3 To UBound(VA)
If VA(R, 1) Like "661*" Or VA(R, 1) Like "662*" Or VA(R, 1) Like "668*" Then
V = Application.Match(VA(R, 1), CPT, 0)
If IsError(V) Then L = L + 1: CPT(L, 0) = VA(R, 1): COL(L, 1) = VA(R, 2): V = L
COL(V, W) = VA(R, 7) - VA(R, 8)
End If
Next
Next
Application.ScreenUpdating = False
With Feuil1.[A6].Resize(L, WN + 1)
.Columns(1).Value = CPT
.Columns(2).Resize(, WN).Value = COL
.Sort .Cells(1), xlAscending, Header:=xlNo
End With
Application.ScreenUpdating = True
End Sub |
Partager