so I am trying to split excel cells into multiple lines based on number of units and Cost. Illustration below
How it Is
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 144"]
[TD="width: 64, bgcolor: transparent"]Fruit[/TD]
[TD="width: 64, bgcolor: transparent"]Units[/TD]
[TD="width: 64, bgcolor: transparent"]Total Cost[/TD]
[TD="bgcolor: transparent"]Apple[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]100[/TD]
[TD="bgcolor: transparent"]Orange [/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]200[/TD]
[TD="bgcolor: transparent"]Guava[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]600[/TD]
[/TABLE]
How I Want it
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 144"]
[TD="width: 64, bgcolor: transparent"]Fruit[/TD]
[TD="width: 64, bgcolor: transparent"]Units[/TD]
[TD="width: 64, bgcolor: transparent"]Cost Per unit[/TD]
[TD="bgcolor: transparent"]Apple[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]50[/TD]
[TD="bgcolor: transparent"]Apple[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]50[/TD]
[TD="bgcolor: transparent"]Orange [/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]66.67[/TD]
[TD="bgcolor: transparent"]Orange [/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]66.67[/TD]
[TD="bgcolor: transparent"]Orange [/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]66.67[/TD]
[TD="bgcolor: transparent"]Guava[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="bgcolor: transparent"]Guava[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="bgcolor: transparent"]Guava[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="bgcolor: transparent"]Guava[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[/TABLE]
I have a VBA which works but I don't know how to apply. Need help with that. VBA that works is as below
Sub test()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Dim howmany As Integer
For y = lastrow To 1 Step -1
If Cells(y, 11) > 1 Then
howmany = Cells(y, 11)
For v = 1 To howmany - 1
Rows(y + 1).Insert (xlShiftDown)
Cells(y, 11) = 1
Cells(y + 1, 1) = Cells(y, 1)
Cells(y + 1, 2) = Cells(y, 2)
Cells(y + 1, 3) = Cells(y, 3)
Cells(y + 1, 4) = Cells(y, 4)
Cells(y + 1, 5) = Cells(y, 5)
Cells(y + 1, 6) = Cells(y, 6)
Cells(y + 1, 7) = Cells(y, 7)
Cells(y + 1, = Cells(y,
Cells(y + 1, 9) = Cells(y, 9)
Cells(y + 1, 10) = Cells(y, 10)
Cells(y + 1, 11) = Cells(y, 11)
Cells(y + 1, 12) = Cells(y, 12)
Cells(y + 1, 13) = Cells(y, 13)
Cells(y + 1, 14) = Cells(y, 14)
Next
End If
Next
End Sub