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 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89
| Sub SearchDroit()
'-------------------------
'Déclaration des variables
'-------------------------
Dim NbLignes As Integer, NbColonnes As Integer
Dim i As Integer, j As Integer, k As Integer, m As Integer, n As Integer, p As Integer, q As Integer, u As Integer, v As Integer, w As Integer, z As Integer
Dim NumUser As String, NumUserInf1 As String, NumUserInf2 As String, NumUserInf3 As String, NumUserInf4 As String, NumUserInf5 As String
Dim NumUserInf6 As String, NumUserInf7 As String, NumUserInf8 As String, NumUserInf9 As String, NumUserInf10 As String
Dim MonTab()
'----------------------------
'Initialisation des variables
'----------------------------
Sheets("Dvp").Select
NbLignes = Range("A65000").End(xlUp).Row
NbColonnes = Range("A1").End(xlToRight).Column
MonTab = Range(Cells(1, 1), Cells(NbLignes, NbColonnes)).Value
Range(Cells(2, 12), Cells(NbLignes, 12)).ClearContents
'--------------------------------------------
'Recherche des dépendances (limité à 10 rangs
'--------------------------------------------
For i = 2 To UBound(MonTab, 1)
If MonTab(i, 7) = "" Then GoTo Suite
NumUser = MonTab(i, 7)
Result = NumUser
For j = 1 To UBound(MonTab, 1)
If MonTab(j, 2) = NumUser Then
NumUserInf1 = MonTab(j, 7)
Result = Result & "," & NumUserInf1
For k = 1 To UBound(MonTab, 1)
If MonTab(k, 2) = NumUserInf1 Then
NumUserInf2 = MonTab(k, 7)
Result = Result & "," & NumUserInf2
For m = 1 To UBound(MonTab, 1)
If MonTab(m, 2) = NumUserInf2 Then
NumUserInf3 = MonTab(m, 7)
Result = Result & "," & NumUserInf3
For n = 1 To UBound(MonTab, 1)
If MonTab(n, 2) = NumUserInf3 Then
NumUserInf4 = MonTab(n, 7)
Result = Result & "," & NumUserInf4
For p = 1 To UBound(MonTab, 1)
If MonTab(p, 2) = NumUserInf4 Then
NumUserInf5 = MonTab(p, 7)
Result = Result & "," & NumUserInf5
For q = 1 To UBound(MonTab, 1)
If MonTab(q, 2) = NumUserInf5 Then
NumUserInf6 = MonTab(q, 7)
Result = Result & "," & NumUserInf6
For u = 1 To UBound(MonTab, 1)
If MonTab(u, 2) = NumUserInf6 Then
NumUserInf7 = MonTab(u, 7)
Result = Result & "," & NumUserInf7
For v = 1 To UBound(MonTab, 1)
If MonTab(v, 2) = NumUserInf7 Then
NumUserInf8 = MonTab(v, 7)
Result = Result & "," & NumUserInf8
For w = 1 To UBound(MonTab, 1)
If MonTab(w, 2) = NumUserInf8 Then
NumUserInf9 = MonTab(w, 7)
Result = Result & "," & NumUserInf9
For z = 1 To UBound(MonTab, 1)
If MonTab(z, 2) = NumUserInf9 Then
NumUserInf10 = MonTab(z, 7)
Result = Result & "," & NumUserInf10
End If
Next z
End If
Next w
End If
Next v
End If
Next u
End If
Next q
End If
Next p
End If
Next n
End If
Next m
End If
Next k
End If
Next j
Cells(i, 12).Value = Result
Suite:
Next i
End Sub |
Partager