VBA Excel Help

[billyg]

Hi Folks,

Hopefully someone here might be able to help me.

Currently i have a spreadsheet with a number of columns. For example the columns include 'Description', 'Product', 'Quantity' and 'Price'. The quantity column, for instance, can sometimes have a value of over one (two, three, eight or even ten or twenty). And herein lies the problem!

Ideally I would like each quantity to have its unique row in the spreadsheet with the quantity being "1" right the way through the sheet.

I wrote a short script to handle this, however, my big problem is dividing up the 'Price' column, as it represents Total Cost for a quantity. Currently my script copies and pastes the entire row depending on the quantity. For instance if the quantity were 3, the row would be copied twice and display 3 seperate rows. However the 'Price' would still remain as a total cost for each unit.

Would anyone know how I would split the Total Cost cell, in accordance with the quantity? For example if the quantity was 5 and the total cost was 500. How would i split the single row into 5 separate rows while also having a Price of 100 for each?

Does this make sense to anyone? I really hope I explained it correctly as I've been immersed in this for a few days now and its driving me insane!



VBA:

Function Match_Data_Master() 
    Worksheets("Sheet3").Select 
    Sheets("Sheet3").Range("d1").Select 
    I = 1 
    ref = ActiveCell.Value 
    Dim REFBACK As String 
    Dim PRODUCT As Integer 
    While ActiveCell <> "" Or IsNull(ActiveCell) 
        If ActiveCell.Value = 0 Then 
            ActiveCell.Offset(0, 5).Value = 0 
        Else 
            ActiveCell.Offset(0, 5).Value = 1 
            PRODUCT = PRODUCT + 1 
        End If 
        ref = ActiveCell.Value 
        REFBACK = ActiveCell.Value 
        If ref > 1 Then 
            Dim j As Variant 
            j = ref 
            While j <> 1 
                Rows(ActiveCell.Row).Select 
                Selection.Copy 
                Selection.Insert Shift:=xlDown 
                ActiveCell.Offset(1, 0).Select 
                j = j - 1 
                PRODUCT = PRODUCT + 1 
            Wend 
            ActiveCell.Offset(0, 3).Select 
        End If 
        ActiveCell.Offset(1, 0).Select 
        I = I + 1 
    Wend 
     'Range("J1").Select
     'Application.CutCopyMode = False
     'ActiveCell.FormulaR1C1 = "=SUM(J[-1])"
     'Range("J1").Select
    Dim Lr As Long 
    Lr = Cells(Rows.Count, "i").End(xlUp).Row + 1 
    Cells(Lr, "i").Formula = "=SUM(I1:J" & Lr - 1 & ")" 
     'Columns("C:C").Select
     'Selection.Delete Shift:=xlToLeft
    MsgBox "Number of Products:  " & PRODUCT 
End Function

Thanks for anyone who read the post, its pretty long but if anyone has any tips or pointers about where to go i'd be very very grateful!

Cheers

[westconn1]

totalcost / quantity?? for each row