Private Sub ComboBox1_Change()
Dim derlgn As Integer
Dim dercol As Integer
Dim i As Integer
dercol = Feuil2.Range("A5").Value
For i = 1 To dercol
If i = ComboBox1.ListIndex Then
Feuil2.Cells(1, i + 1).FormulaR1C1 = "=DCOUNTA(Feuil1!C,1,R[1]C:R[2]C)"
derlgn = Feuil2.Cells(1, i + 1).Value
ComboBox2.RowSource = Range(Cells(2, i + 1), Cells(derlgn + 1, i + 1)).Address
UserForm1.Repaint
ComboBox2.ListIndex = 0
End If
Next
End Sub
Private Sub UserForm_Initialize()
Dim cel As Range
Dim dercol As Byte
With Worksheets("Feuil1")
dercol = .Range("IV1").End(xlToLeft).Column
For Each cel In .Range(Cells(1, 1), Cells(1, dercol))
Me.ComboBox1.AddItem cel.Value
Next
End With
Feuil2.Range("A5").Value = dercol
End Sub
en passant par des valeurs "temporaires"
un peu compliqué mais ça marche ...