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 51 52 53
| Option Explicit
Dim j As Long
Dim k As Byte
Sub Test()
Dim LastLig As Long, i As Long
Dim IniComp As String
Dim TBr(), TBb
IniComp = "112461"
j = 0
Erase TBr
With Worksheets("BASE")
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To LastLig
If .Range("A" & i) = IniComp Then
TBb = .Range("A" & i & ":D" & LastLig)
k = 1
FindComp TBb, IniComp, TBr
Exit For
End If
Next i
End With
With Worksheets("RESULTAT")
.UsedRange.ClearContents
.Range("A2").Resize(UBound(TBr, 2), 5) = Application.Transpose(TBr)
End With
MsgBox "terminé"
End Sub
Private Sub FindComp(ByVal Tb, ByVal Cp As String, ByRef Res())
Dim Tmp As String
Dim i As Long
Dim Nxt As Boolean
For i = 1 To UBound(Tb)
If Tb(i, 1) = Cp Then
j = j + 1
Tmp = Tb(i, 4)
ReDim Preserve Res(1 To 5, 1 To j)
Res(k, j) = Tmp
Res(5, j) = Tb(i, 3)
If InStr("MCE", Left(Tmp, 1)) = 0 Then
k = k + 1
FindComp Tb, Tmp, Res
k = k + IIf(Nxt, 0, -1)
End If
End If
Next i
Nxt = i < UBound(Tb)
End Sub |
Partager