J'ai pensé à coder une Macro en VBA, mais ne connaissant pas du tout ce langage, je suis relativement perdu.
Pour le moment, j'ai trouvé quelque chose de ce type :
'-----------------------------------------------------------'
Sub test()
Dim Rg As Range, R As Range
Dim Col As Integer, Ligne As Long
On Error Resume Next
With Feuil1
Set Rg = .Range("A2:A" & .Range("A6556").End(xlUp).Row)
End With
Ligne = 1
For Each R In Rg.Rows
Col = R.EntireRow.Cells.Find("*", LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
With Feuil2
.Range("A" & Ligne).Resize(Col - 1) = _
R.Cells(1, 1).Value
.Range("C" & Ligne).Resize(Col - 1) = _
Application.Transpose(R.Offset(, 1). _
Resize(, Col - 1).Value)
Ligne = Ligne + Col - 1
End With
With ActiveSheet DerLig = .[A65536].End(3).Row DerCol = .[IV1].End(1).Column ReDim mesdonnees(1 To (DerLig * DerCol), 1 To 3) For i = 2 To DerLig For j = 2 To DerCol k = k + 1 mesdonnees(k, 1) = .Cells(i, 1) mesdonnees(k, 2) = .Cells(1, j) mesdonnees(k, 3) = .Cells(i, j) Next j Next i End With On Error Resume Next: Sheets("Résultat").Delete: On Error GoTo 0 Sheets.Add(after:¬tiveSheet).Name = "Résultat"
With Sheets("Résultat") .[A1].Resize(UBound(mesdonnees, 1), UBound(mesdonnees, 2)) = mesdonnees .Rows("1:1").Insert .[A1] = "Fourn.": .[B1] = "Rubrique": .[C1] = "Valeur" End With
With ActiveSheet
DerLig = .[A65536].End(3).Row
DerCol = .[IV1].End(1).Column
ReDim mesdonnees(1 To (DerLig * DerCol), 1 To 3)
For i = 2 To DerLig
For j = 2 To DerCol
k = k + 1
mesdonnees(k, 1) = .Cells(i, 1)
mesdonnees(k, 2) = .Cells(1, j)
mesdonnees(k, 3) = .Cells(i, j)
Next j
Next i
End With
On Error Resume Next: Sheets("Résultat").Delete: On Error GoTo 0
Sheets.Add(after:=ActiveSheet).Name = "Résultat"
With Sheets("Résultat")
.[A1].Resize(UBound(mesdonnees, 1), UBound(mesdonnees, 2)) =
mesdonnees
.Rows("1:1").Insert
.[A1] = "Fourn.": .[B1] = "Rubrique": .[C1] = "Valeur"
End With
With ActiveSheet DerLig = .[A65536].End(3).Row DerCol = .[IV1].End(1).Column ReDim mesdonnees(1 To (DerLig * DerCol), 1 To 3) For i = 2 To DerLig For j = 2 To DerCol k = k + 1 mesdonnees(k, 1) = .Cells(i, 1) mesdonnees(k, 2) = .Cells(1, j) mesdonnees(k, 3) = .Cells(i, j) Next j Next i End With On Error Resume Next: Sheets("Résultat").Delete: On Error GoTo 0 Sheets.Add(after:¬tiveSheet).Name = "Résultat"
With Sheets("Résultat") .[A1].Resize(UBound(mesdonnees, 1), UBound(mesdonnees, 2)) = mesdonnees .Rows("1:1").Insert .[A1] = "Fourn.": .[B1] = "Rubrique": .[C1] = "Valeur" End With