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
| Sub Convertir()
Dim WsSource As Worksheet, WsCible As Worksheet
Dim DerLigS As Long, DerLigC As Long
Dim MaPlage As Range, Cel As Range
Dim Recollages() As String, Texte() As String
Dim Ligne As Byte
Set WsSource = Worksheets("depart")
Set WsCible = Worksheets("arrive")
DerLigS = WsSource.Range("A" & WsSource.Rows.Count).End(xlUp).Row
Set MaPlage = WsSource.Range("A2:A" & DerLigS)
For Each Cel In MaPlage
DerLigC = WsCible.Range("E" & WsCible.Rows.Count).End(xlUp).Row
'Sépare les recollages
Recollages = Split(Cel.Offset(0, 7), "/")
For i = 0 To UBound(Recollages, 1)
Texte = Split(Recollages(i), "_")
Recollages(i) = Texte(0) & Texte(1)
If UBound(Texte, 1) > 1 Then _
Recollages(i) = Recollages(i) & "_" & Texte(2)
Next
With WsCible
.Range("A" & DerLigC + 1) = Cel.Value 'recipient
.Range("B" & DerLigC + 1) = Cel.Offset(0, 1) 'couche
.Range("C" & DerLigC + 1) = Cel.Offset(0, 2) 'm_2
.Range("D" & DerLigC + 1) = Cel.Offset(0, 3) 'tesson
For Ligne = 0 To UBound(Recollages, 1)
.Range("E" & DerLigC + 1 + Ligne) = Replace(Cel.Offset(0, 2), "_", "") & "_" & Cel.Offset(0, 3) 'IS dep
.Range("F" & DerLigC + 1 + Ligne) = Cel.Offset(0, 4)
.Range("G" & DerLigC + 1 + Ligne) = Cel.Offset(0, 5)
.Range("H" & DerLigC + 1 + Ligne) = Cel.Offset(0, 6)
.Range("I" & DerLigC + 1 + Ligne) = Recollages(Ligne)
Next Ligne
End With
Next Cel
End Sub |
Partager