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
|
Private Sub CommandButton1_Click()
Dim i&, J&, K&
Dim DicoA As Object, DicoD As Object
Dim TReport As Variant, X As Variant
Dim Y As Variant
Set DicoA = CreateObject("Scripting.dictionary")
Set DicoD = CreateObject("Scripting.dictionary")
With Sheets("Requête5")
TReport = .Range(.Cells(2, 1), .Cells(.Rows.Count, 12).End(3))
ReDim Preserve TReport(1 To UBound(TReport, 1), 1 To UBound(TReport, 2) * 2)
For i = LBound(TReport, 1) To UBound(TReport, 1)
X = Int(TReport(i, 2)) & (TReport(i, 3))
Select Case TReport(i, 1)
Case "OP"
DicoA(X) = i
Case "OC"
DicoD(Y) = i
End Select
Next i
For i = LBound(TReport, 1) To UBound(TReport, 1)
X = Int(TReport(i, 2)) & (TReport(i, 3))
If TReport(i, 1) = "OP" And DicoA.exists(X) And DicoD.exists(X) Then
L = 0
J = J + 1
For K = 1 To 16
TReport(J, K) = TReport(i, K)
Next K
For K = K To 32
L = L + 1
TReport(J, K) = TReport(DicoD(X), L)
Next K
End If
Next i
Sheets("Feuil1").Select
.Cells(2, 1).Resize(J, UBound(TReport, 2)).FormulaLocal = TReport
End With
End Sub |
Partager