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.