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
| Private Sub CommandButton1_Click()
Dim Plage As Range
Dim Cel As Range
Dim PlageTitre As Range
Dim CelluleTitre As Range
Dim DerniereColonne As Long
Dim ColDate As Long
Dim ColAgent As Long
With Worksheets("Feuil1")
DerniereColonne = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set PlageTitre = .Range(.Cells(1, 1), .Cells(1, DerniereColonne))
' Recherche des colonnes
'-----------------------
ColDate = 0
ColAgent = 0
For Each CelluleTitre In PlageTitre
If CelluleTitre = "Dates" Then ColDate = CelluleTitre.Column
If ComboBox2.Text = CelluleTitre Then ColAgent = CelluleTitre.Column
Next CelluleTitre
' Mise à jour du tableau
'-----------------------
If ColDate > 0 And ColAgent > 0 Then
Set Plage = .Range(.Cells(3, ColDate), .Cells(.Rows.Count, ColDate).End(xlUp))
For Each Cel In Plage
If CDate(Cel.Value) = CDate(ComboBox3.Text) Then
Cel.Offset(0, ColAgent - ColDate) = ComboBox1.Text
Cel.Offset(0, ColAgent - ColDate + 1) = TextBox1.Text
Exit For
End If
Next Cel
Set Plage = Nothing
End If
Set PlageTitre = Nothing
End With
End Sub |