Boucle sur Textbox
Bonjour à tous,
J'ai une erreur sur la boucle ci-dessous mais je n'arrive pas à l'identifier.
Merci d'avance la communauté.
A simplifier :
Range("A" & Rows.Count).End(xlUp).Select
TextBox36.Value = Format(ActiveCell.Value, "dd/mm/yy")
TextBox37.Value = ActiveCell.Offset(0, 1).Value
TextBox38.Value = ActiveCell.Offset(0, 2).Value
TextBox39.Value = ActiveCell.Offset(0, 3).Value
TextBox40.Value = Format(ActiveCell.Offset(0, 9).Value, "dd/mm/yy hh:mm")
TextBox41.Value = Format(ActiveCell.Offset(0, 10).Value, "dd/mm/yy hh:mm")
TextBox42.Value = Format(ActiveCell.Offset(0, 11).Value, "hh:mm")
Range("A" & Rows.Count).End(xlUp).Offset(-1).Select
TextBox43.Value = Format(ActiveCell.Value, "dd/mm/yy")
TextBox44.Value = ActiveCell.Offset(0, 1).Value
TextBox45.Value = ActiveCell.Offset(0, 2).Value
TextBox46.Value = ActiveCell.Offset(0, 3).Value
TextBox47.Value = Format(ActiveCell.Offset(0, 9).Value, "dd/mm/yy hh:mm")
TextBox48.Value = Format(ActiveCell.Offset(0, 10).Value, "dd/mm/yy hh:mm")
TextBox49.Value = Format(ActiveCell.Offset(0, 11).Value, "hh:mm")
Boucle qui ne fonctionne pas :
For i = 1 To 4 Step 1
For j = 36 To 64 Step 7
Me.Controls("TextBox" & j).Value = Format(ActiveCell.Value, "dd/mm/yy")
Me.Controls("TextBox" & j + 1).Value = ActiveCell.Offset(0, 1).Value
Me.Controls("TextBox" & j + 2).Value = ActiveCell.Offset(0, 2).Value
Me.Controls("TextBox" & j + 3).Value = ActiveCell.Offset(0, 3).Value
Me.Controls("TextBox" & j + 4).Value = Format(ActiveCell.Offset(0, 9).Value, "dd/mm/yy hh:mm")
Me.Controls("TextBox" & j + 5).Value = Format(ActiveCell.Offset(0, 10).Value, "dd/mm/yy hh:mm")
Me.Controls("TextBox" & j + 6).Value = Format(ActiveCell.Offset(0, 11).Value, "hh:mm")
Next j
Range("A" & Rows.Count).End(xlUp).Offset(-i).Select
Next i
Je viens de m'apercevoir qu'il en manque une partie.
Voici donc tout le code à remplacer :
Range("A" & Rows.Count).End(xlUp).Select
TextBox36.Value = Format(ActiveCell.Value, "dd/mm/yy")
TextBox37.Value = ActiveCell.Offset(0, 1).Value
TextBox38.Value = ActiveCell.Offset(0, 2).Value
TextBox39.Value = ActiveCell.Offset(0, 3).Value
TextBox40.Value = Format(ActiveCell.Offset(0, 9).Value, "dd/mm/yy hh:mm")
TextBox41.Value = Format(ActiveCell.Offset(0, 10).Value, "dd/mm/yy hh:mm")
TextBox42.Value = Format(ActiveCell.Offset(0, 11).Value, "hh:mm")
Range("A" & Rows.Count).End(xlUp).Offset(-1).Select
TextBox43.Value = Format(ActiveCell.Value, "dd/mm/yy")
TextBox44.Value = ActiveCell.Offset(0, 1).Value
TextBox45.Value = ActiveCell.Offset(0, 2).Value
TextBox46.Value = ActiveCell.Offset(0, 3).Value
TextBox47.Value = Format(ActiveCell.Offset(0, 9).Value, "dd/mm/yy hh:mm")
TextBox48.Value = Format(ActiveCell.Offset(0, 10).Value, "dd/mm/yy hh:mm")
TextBox49.Value = Format(ActiveCell.Offset(0, 11).Value, "hh:mm")
Range("A" & Rows.Count).End(xlUp).Offset(-2).Select
TextBox50.Value = Format(ActiveCell.Value, "dd/mm/yy")
TextBox51.Value = ActiveCell.Offset(0, 1).Value
TextBox52.Value = ActiveCell.Offset(0, 2).Value
TextBox53.Value = ActiveCell.Offset(0, 3).Value
TextBox54.Value = Format(ActiveCell.Offset(0, 9).Value, "dd/mm/yy hh:mm")
TextBox55.Value = Format(ActiveCell.Offset(0, 10).Value, "dd/mm/yy hh:mm")
TextBox56.Value = Format(ActiveCell.Offset(0, 11).Value, "hh:mm")
Range("A" & Rows.Count).End(xlUp).Offset(-3).Select
TextBox57.Value = Format(ActiveCell.Value, "dd/mm/yy")
TextBox58.Value = ActiveCell.Offset(0, 1).Value
TextBox59.Value = ActiveCell.Offset(0, 2).Value
TextBox60.Value = ActiveCell.Offset(0, 3).Value
TextBox61.Value = Format(ActiveCell.Offset(0, 9).Value, "dd/mm/yy hh:mm")
TextBox62.Value = Format(ActiveCell.Offset(0, 10).Value, "dd/mm/yy hh:mm")
TextBox63.Value = Format(ActiveCell.Offset(0, 11).Value, "hh:mm")
Range("A" & Rows.Count).End(xlUp).Offset(-4).Select
TextBox64.Value = Format(ActiveCell.Value, "dd/mm/yy")
TextBox65.Value = ActiveCell.Offset(0, 1).Value
TextBox66.Value = ActiveCell.Offset(0, 2).Value
TextBox67.Value = ActiveCell.Offset(0, 3).Value
TextBox68.Value = Format(ActiveCell.Offset(0, 9).Value, "dd/mm/yy hh:mm")
TextBox69.Value = Format(ActiveCell.Offset(0, 10).Value, "dd/mm/yy hh:mm")
TextBox70.Value = Format(ActiveCell.Offset(0, 11).Value, "hh:mm")
Encore merci pour votre aide.
Bonjour youbaa,
Je te propose ce code VBA (non testé) :
Private Sub CommandButton1_Click()
Dim celX As Range, lig&, k As Byte, i As Byte
lig = Cells(Rows.Count, 1).End(xlUp).Row
For i = 0 To 4
k = 7 * i + 36
With Cells(lig - i, 1)
Me.Controls("TextBox" & k) = Format(.Value, "dd/mm/yy")
Me.Controls("TextBox" & k + 1) = .Offset(, 1)
Me.Controls("TextBox" & k + 2) = .Offset(, 2)
Me.Controls("TextBox" & k + 3) = .Offset(, 3)
Me.Controls("TextBox" & k + 4) = Format(.Offset(, 9), "dd/mm/yy hh:mm")
Me.Controls("TextBox" & k + 5) = Format(.Offset(, 10), "dd/mm/yy hh:mm")
Me.Controls("TextBox" & k + 6) = Format(.Offset(, 11), "hh:mm")
End With
Next i
End Sub
Merci de me donner ton avis.
dhany
Super, ca fonctionne. Encore merci.