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
| Sub remplirSN_PotNtI2()
Dim i&, fin&, col As Variant, aa As Variant, bb As Variant, a&, x&, col1&, col2&
With Feuil1
fin = .Range("A" & Rows.Count).End(xlUp).Row
col1 = .Cells(1, Columns.Count).End(xlToLeft).Column
aa = .Range(.Cells(1, 1), .Cells(fin, col1)) ' VN
End With
With Feuil27
fin = .Range("B" & Rows.Count).End(xlUp).Row
col2 = .Cells(3, Columns.Count).End(xlToLeft).Column + 1
bb = .Range(.Cells(3, 2), .Cells(fin, col2)) ' Modules 1 à 12
End With
x = 3
For Each col In Array(2, 5, 8, 11, 13, 15, 18, 21, 24, 26, 28, 30) '
For i = 3 To UBound(bb)
bb(i, col) = "": bb(i, col + 1) = ""
For a = 2 To UBound(aa)
If bb(i, 1) = aa(a, 1) And bb(1, col) = aa(1, x) And aa(a, x) <> "" Then bb(i, col + 1) = aa(a, x): bb(i, col) = aa(a, 2)
Next a
Next i
x = x + 1
Next col
With Feuil9
.Cells.Clear
.Range("B3").Resize(UBound(bb), UBound(bb, 2)) = bb
.Range("D5:AF20").NumberFormat = "0"
For Each col In Array(3, 6, 9, 12, 14, 16, 19, 22, 25, 27, 29, 31)
.Range(.Cells(5, col), .Cells(18, col + 1)).Copy Feuil27.Cells(5, col)
Next col
End With
Range("C5:AF18").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
Range("A1").Select
End With
End Sub |
Partager