Splitting a line with a $ Amt into 2 Lines

[kb1dqh]

Hi,

I am trying to use VBA to split about 5,000 lines into 10,000. Each line has a dollar amount, say $10 and two columns, say A and B, each with a %, as well as a unique identified (name). A+B=100%. I now want a line for just 'A' and a line for just 'B'. Example:

INPUT:
NAME A B $
CAR 20% 80% 10

OUTPUT:
NAME TYPE $
CAR A 2
CAR B 8

Any thoughts on how to do this for 5000+ lines? Thanks!

 

[CWatts]

What is supposed to happen to the dollar armount? Should it be omitted, added to only Line A, or shoulld the dollar amount be split by the respective percentage?

 

[kb1dqh]

Sorry, was trying to get that across in the table.

The $ amount should be split by the associated %. So, the new line "A" should have 20%, or 2, and "B" 80% or 8.

 

[CWatts]

I tackled this since i'm trying to teach myself VBA, and it works for me. Give it a go and see if it works for you.

Make sure you have like three backups!.

Also, any feedback from pro coders would be appreciated:

Sub Shift()
Dim i As Integer
Dim Length As Integer

Length = 0
i = 1

Do While Cells(i, 1) <> ""
    i = i + 1
Loop

Length = (i - 1) * 2
i = 3

Do While i <= Length
    Rows(i).Select
    Selection.Insert
    
    Cells(i, 1).Value = Cells(i - 1, 1).Value
    Cells(i, 2).Value = Cells(i - 1, 3).Value

    Cells(i - 1, 3).Value = Cells(i - 1, 2).Value * Cells(i - 1, 4).Value
    Cells(i, 3).Value = Cells(i, 2).Value * Cells(i - 1, 4).Value
    
    i = i + 2
Loop

    Columns("C:C").NumberFormat = "$#,##0"
    Columns("D:D").Clear

End Sub