Peux-tu expliquer en détail ce que tu voudrais ?Je souhaiterais eclater les données d'une cellule sur plusieurs lignes svp
Sub test()
Dim vArry, i As Long
vArry = Split(Range("C2"), "/")
For i = 0 To UBound(vArry)
Cells(5 + i, "A") = vArry(i)
Cells(5 + i, "A").RowHeight = 12.75
Next
End Sub
Private Sub CommandButton1_Click()
Dim c As Range, L#, t$, i%, e, s, j%, t1$
Application.ScreenUpdating = False
Application.Goto [Z1], True 'pour éloigner les colonnes traitées
Rows("3:" & Rows.Count).Delete 'RAZ
Rows("3:" & Rows.Count).WrapText = False 'pas de renvoi à la ligne
For Each c In [A2:G2] 'plage à adapter
If c <> "" Then
L = c.ColumnWidth
t = "": i = 2
For Each e In Split(c)
s = Split(e, vbLf)
For j = 0 To UBound(s)
t1 = t
t = Trim(t & " " & s(j))
c(i) = t
c(i).Columns.AutoFit 'ajustement largeur
If j Or c(i).ColumnWidth > L + 1 Then c(i) = t1: i = i + 1: t = s(j): c(i) = t
Next j, e
c.ColumnWidth = L
End If
Next c
Application.Goto [A1], True
End Sub
[D:F].Replace ",", ".", xlPart 'conversion en nombres
VbLf = Chr(10) ( Line Feed) : Descendre d'une ligne
vbCr = Chr(13) (Carriage Return) : Retour chariot
vbCrLf = Chr(13) & Chr(10)
For Each c In [A2:B2,D2:G2] 'plage à adapter
s = Split(Replace(Replace(c, vbLf, " "), " %", "%"))
If c <> "" Then c(2).Resize(UBound(s) + 1) = Application.Transpose(s)
Next c
Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxi, c As Range, s, t$, e, i%, t1$
Application.EnableEvents = False 'désactive les évènements
With [M1] 'nombre maximun de caractères d'une ligne en C2, à adapter
.Value = Abs(Int(Val(.Value)))
maxi = .Value
If Target.Address = .Address Then .Select
End With
Application.ScreenUpdating = False
Rows("3:" & Rows.Count).Delete 'RAZ
Rows("3:" & Rows.Count).WrapText = False 'pas de renvoi à la ligne
For Each c In [A2:B2,D2:G2] 'plage à adapter
s = Split(Replace(Replace(c, vbLf, " "), " %", "%"))
If c <> "" Then c(2).Resize(UBound(s) + 1) = Application.Transpose(s)
Next c
Set c = [C2] 'cellule à adapter
If c <> "" Then
t = ""
For Each e In Split(Replace(c, vbCrLf, " "))
s = Split(e, vbLf)
For i = 0 To UBound(s)
t1 = t
t = Trim(t & IIf(i, vbLf, " ") & s(i))
If Len(t) - InStrRev(t, vbLf) > maxi Then t = t1 & IIf(i, vbLf, vbCrLf) & s(i)
Next i, e
c = t 'nouveau texte en C2
s = Split(Replace(t, vbCrLf, vbLf), vbLf)
c(2).Resize(UBound(s) + 1) = Application.Transpose(s)
End If
Columns("C").AutoFit 'ajustement largeur
Rows(2).AutoFit 'ajustement hauteur
Application.EnableEvents = True 'réactive les évènements
End Sub