Breaking out Quantity [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it开发者_StackOverflow社区 can be reopened, visit the help center. Closed 10 years ago.

I've never set up a macro before but I have seen one where it views the "Quantity" column on a spreadsheet and takes anything that isn't a quantity of (1) and duplicates the entry the number of times listed in the "Quantity" column.

Could someone tell me how I can set that up?

---

Here is something that may help you get started. I am assuming you want something like the following:

      Col A    Col B                  Col A    Col B
1     Item     Quantity               Item     Quantity
2     Apple    1                      Apple    1
3     Orange   2                      Orange
4     Banana   1              ----->  Orange   2
5     Pear     4                      Banana   1
6     Grape    2                      Pear
7                                     Pear
8                                     Pear
9                                     Pear     4
10                                    Grape    
11                                    Grape    2

This can be achieved with the following code:

Sub ExpandItem()
    Dim lastRow As Long, rngQuantity As Range, rw As Long

    lastRow = Range("A1").End(xlDown).Row

    For rw = lastRow To 2 Step -1
        If Cells(rw, 1) > 1 Then
            AddItem Cells(rw, 1), Cells(rw, 1).Offset(0, 1)
        End If
    Next rw
End Sub

Sub AddItem(item As Range, quantity As Long)
    Dim i As Long
        For i = 1 To (quantity - 1)
               item.EntireRow.Insert Shift:=xlDown
               item.Offset(-1, 0) = item.Value
        Next i
End Sub

Hope this helps get you moving in the right direction