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
| Option Explicit
Sub RecupNom()
Dim i As Integer, x As Integer
Dim ColBase As New Collection
Dim TabPlage, TabPlage2, TabPlage3, TabPlage4 As Variant, Container As Variant
Dim Item As Variant
With Sheets("Feuil1")
TabPlage = .Range("premiereplage")
TabPlage2 = .Range("deuxiemeplage")
TabPlage3 = .Range("troisiemeplage")
TabPlage4 = .Range("quatriemeplage")
End With
On Error Resume Next
For i = 1 To UBound(TabPlage)
If Not IsEmpty(TabPlage(i, 1)) Then
ColBase.Add CStr(Trim(TabPlage(i, 1))) & "#" & CStr(Trim(TabPlage(i, 2))), CStr(Trim(TabPlage(i, 1))) & "#" & CStr(Trim(TabPlage(i, 2)))
End If
Next
On Error Resume Next
For i = 1 To UBound(TabPlage2)
If Not IsEmpty(TabPlage2(i, 1)) Then
ColBase.Add CStr(Trim(TabPlage2(i, 1))) & "#" & CStr(Trim(TabPlage2(i, 2))), CStr(Trim(TabPlage2(i, 1))) & "#" & CStr(Trim(TabPlage2(i, 2)))
End If
Next
On Error Resume Next
For i = 1 To UBound(TabPlage3)
If Not IsEmpty(TabPlage3(i, 1)) Then
ColBase.Add CStr(Trim(TabPlage3(i, 1))) & "#" & CStr(Trim(TabPlage3(i, 2))), CStr(Trim(TabPlage3(i, 1))) & "#" & CStr(Trim(TabPlage3(i, 2)))
End If
Next
On Error Resume Next
For i = 1 To UBound(TabPlage4)
If Not IsEmpty(TabPlage4(i, 1)) Then
ColBase.Add CStr(Trim(TabPlage4(i, 1))) & "#" & CStr(Trim(TabPlage4(i, 2))), CStr(Trim(TabPlage4(i, 1))) & "#" & CStr(Trim(TabPlage4(i, 2)))
End If
Next
On Error GoTo 0
For Each Item In ColBase
Container = Split(Item, "#")
x = x + 1
With Sheets("Feuil2")
.Range("A" & 5 + x) = Container(0)
.Range("B" & 5 + x) = Container(1)
End With
Next
End Sub |
Partager