Maybe something like this ?
Sub test()
Set Rng = Range("G3:G6")
For Each cell In Rng
cnt = Len(cell) - Len(Application.Substitute(cell, "(", ""))
Set oCopy = Range(cell.Offset(0, -6), cell.Offset(0, 2))
Set oFill = Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
oCopy.Copy Destination:=Range(oFill, oFill.Offset(cnt - 1, 0))
Range(oFill.Offset(0, 6), oFill.Offset(cnt - 1, 6)).ClearContents
Range(oFill.Offset(0, 8), oFill.Offset(cnt - 1, 8)).ClearContents
dtQTY = Split(cell, "(")
For Each el In dtQTY
If InStr(el, ")") Then
spl = Split(el, ")")
Range("i" & Rows.Count).End(xlUp).Offset(1, 0).Value = spl(0)
Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Value = spl(1)
Else
Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Value = el
End If
Next
Next
End Sub
The code assumes that the REMARKS INVOICE PAYMENT to breakdown is always in range from cell G3 to G6, the code loop in this range.
The code also assumes that the value in G3 to G6 will always has the same pattern as seen in the sample workbook, inv#(amount)inv#(amount)inv#(amount)... and so on.
And that if I'm not mistaken to get what you mean .
Bookmarks