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
| Option Explicit
'--- adapté de
'--- https://www.developpez.net/forums/d2122409/logiciels/microsoft-office/excel/macros-vba-excel/compilation-2-tableaux-appartenant-2-feuilles-differentes/#post11789717
Dim TbloFinal As Variant
Private Sub AddTablo(TbloFirst As Variant, TbloScnd As Variant)
'--- Ajoute le tableau TbloScnd en dessous du tableau TbloFirst => TabloFinal
'--- Il est nécessaire que les 2 tableaux aient le même nombre de lignes
If UBound(TbloFirst, 1) <> UBound(TbloScnd, 1) Then
MsgBox "Abandonné: les 2 plages n'ont pas le même nombre de colonnes !", , "Non admis"
TbloFinal = Nothing
Exit Sub
End If
'---
Dim i As Integer, j As Integer, k As Integer
k = 1
ReDim TbloFinal(1 To UBound(TbloFirst, 1), 1 To UBound(TbloFirst, 2) + UBound(TbloScnd, 2))
For i = 1 To UBound(TbloFirst, 1)
For j = 1 To UBound(TbloFirst, 2)
TbloFinal(i, j) = TbloFirst(i, j)
Next j
For j = UBound(TbloFirst, 2) + 1 To UBound(TbloFinal, 2)
TbloFinal(i, j) = TbloScnd(i, k)
k = k + 1
Next j
k = 1
Next i
End Sub
Private Sub MergeTablo(TbloFirst As Variant, TbloScnd As Variant)
'--- Ajoute le tableau TbloScnd à côté du tableau TbloFirst => TbloFinal
'--- Il est nécessaire que les 2 tableaux aient le même nombre de colonnes
If UBound(TbloFirst, 2) <> UBound(TbloScnd, 2) Then
MsgBox "Abandonné: les 2 plages n'ont pas le ême nombre de lignes !", , "Non admis"
TbloFinal = Nothing
Exit Sub
End If
'---
Dim i As Integer, j As Integer, k As Integer
ReDim TbloFinal(1 To UBound(TbloFirst, 1) + UBound(TbloScnd, 1) - 1, 1 To UBound(TbloFirst, 2))
Debug.Print UBound(TbloFinal, 1), UBound(TbloFinal, 2)
For i = 1 To UBound(TbloFirst, 1)
For j = 1 To UBound(TbloFirst, 2)
TbloFinal(i, j) = TbloFirst(i, j)
Next j
Next i
k = UBound(TbloFirst, 1) - 1
For i = 2 To UBound(TbloScnd, 1)
For j = 1 To UBound(TbloScnd, 2)
TbloFinal(k + i, j) = TbloScnd(i, j)
Next j
Next i
End Sub
Sub ConcatTabloStruc()
ReDim Tblo1(0 To 0, 0 To 0) As Variant
ReDim Tblo2(0 To 0, 0 To 0) As Variant
With Application.WorksheetFunction
Tblo1 = .Transpose(Worksheets("Feuil1").Range("rA").Value)
Tblo2 = .Transpose(Worksheets("Feuil2").Range("rB").Value)
End With
Debug.Print UBound(Tblo1, 1), UBound(Tblo1, 2), UBound(Tblo2, 1), UBound(Tblo2, 2)
' AddTablo Tblo1, Tblo2
MergeTablo Tblo1, Tblo2
Erase Tblo2
Erase Tblo1
End Sub |
Partager