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 48 49 50 51 52 53 54 55 56
| Sub t()
derlig = 11 ' A adapter
tt = Array(vbNewLine, vbCrLf, vbLf, vbCr)
SautDeligne = ""
For i = 1 To derlig
a = Cells(i, 5)
For j = 0 To UBound(tt)
If InStr(a, tt(j)) > 0 Then
SautDeligne = tt(j)
ok = True
Exit For
ok = True
End If
Next
If ok = True Then Exit For
Next
If ok = False Then
MsgBox "Aucun saut de ligne détecté"
Exit Sub
End If
NbrSautsDeLigne = 0
For i = 1 To derlig
debut = 1
nbr = 0
b = InStr(debut, Cells(i, 5).Value, SautDeligne)
While b > 0
nbr = nbr + 1
debut = b + Len(SautDeligne)
b = InStr(debut, Cells(i, 5).Value, SautDeligne)
Wend
If nbr > NbrSautsDeLigne Then
NbrSautsDeLigne = nbr
End If
Next
For i = 1 To NbrSautsDeLigne + 1
Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Next
For i = 2 To derlig
a = Cells(i, 5).Value
colonne = 0
b = InStr(a, SautDeligne)
While b > 0
colonne = colonne + 1
Cells(i, 5 + colonne).Value = Mid(a, 1, b - 1)
a = Mid(a, b + Len(b + Len(SautDeligne)))
b = InStr(a, SautDeligne)
Wend
If a <> "" Then
colonne = colonne + 1
Cells(i, 5 + colonne).Value = a
End If
Next
End Sub |
Partager