Modify a line into several lines

Originally Posted by jaslake

Hi julien4266

This code is included in the attached workbook (your sample data).

Option Explicit
Public Sub Test()
    Dim rng As Range
    Dim LR As Long
    Dim i As Integer
    Application.ScreenUpdating = False
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("E2:E" & LR)
    With rng
        For i = LR To 1 Step -1
            If rng(i).Value > 1 Then
                rng(i).Offset(1, 0).Resize(rng(i).Value - 1, 1).EntireRow.Insert
                rng(i).Offset(0, 0).EntireRow.Copy
                rng(i).Offset(1, -4).Resize(rng(i).Value - 1, 1).PasteSpecial
            End If
        Next
    End With
 
    Call Test1
 
    Application.ScreenUpdating = True
End Sub
Sub Test1()
    Dim Arr As Variant
    Dim i As Integer
    Dim rng As Range
    Dim fCell As Range
    Dim LR As Long: LR = Range("K" & Rows.Count).End(xlUp).Row
    Set rng = Range("K2:K" & LR)
    With rng
        For Each fCell In rng
            If fCell.Offset(1, -1) <> fCell.Offset(0, -1) Then GoTo Skip
            Arr = Split(fCell.Value, ",")
            For i = LBound(Arr) To UBound(Arr)
                fCell.Offset(i, 0) = LTrim(Arr(i))
            Next i
Skip:
        Next fCell
    End With
End Sub

It appears to do what you requested. Let me know of issues.

John

Thanks that's very close to what i want to do. Do you think it's possible to change the quantity to 1 for all lines after the process?
The other thing will be to put Spare as a default value if their is no TAG in this way if their is several spare in one line at the beginning, we don't have to write it.
Thanks a lot already for what you have done. It's already very good.

Originally Posted by jaslake

Hi julien4266
With the code in the attached workbook, if you have more units than you have tags, the units without tags are assigned a tag value of "Spare" and all unit quantities are changed to 1.
Let me know of issues.
John

At the first sight it seems perfect to me. Thanks you very much for your help.
Well done.