Sub xx()
Dim arr, brr(1 To 10000, 1 To 6), i, j, n, str1, str2
arr = Sheet1.Range("A4:E" & Sheet1.Range("A65536").End(xlUp).Row)
n = 0
For i = 1 To UBound(arr)
str1 = arr(i, 1) & "-"
str1 = Mid(arr(i, 1), 1, InStr(1, str1, "-") - 1)
str2 = arr(i, 4) & "-"
str2 = Mid(arr(i, 4), 2, InStr(1, str2, "-") - 2)
For j = 1 To arr(i, 2)
n = n + 1
brr(n, 1) = arr(i, 1)
brr(n, 2) = 1
brr(n, 3) = arr(i, 3)
brr(n, 4) = arr(i, 4)
brr(n, 5) = arr(i, 5)
brr(n, 6) = str1 & "L" & Format(str2 + j - 1, "00000")
Next
Next
Sheet1.Range("r4").Resize(1000, 6) = ""
Sheet1.Range("r4").Resize(1000, 6) = brr
End Sub |