Looping

[maju]

Hi Guys. I am new to macro programming. Below is my data. I need to write a
loop that will insert new row with same data whenever the quantity is 2, 3
etc. e.g if the quantity is 1,no new row. if is it 2, 2 additional row with
same datas info, if 3, 3 additional rows with same data. thanks

State Style Quantity
Maryland 100g 1
New york xzy10 1
texas 200g 2
OH 3

 

[JW]

Hi Guys. I am new to macro programming. Below is my data. I need to write a
loop that will insert new row with same data whenever the quantity is 2, 3
etc. e.g if the quantity is 1,no new row. if is it 2, 2 additional row with
same datas info, if 3, 3 additional rows with same data. thanks

State         Style   Quantity
Maryland   100g       1
New york    xzy10     1
texas          200g      2
OH                           3

Here's one way. This is assuming that your quantity is in column C.
It also includes no error handling, so if you have text in column C
other than the header row, this will error out.

Sub oneWay()
Dim r As Long
r = 2
Do Until IsEmpty(Cells(r, "C"))
With Cells(r, "C")
If .Value = "1" Then
r = r + 1
Else
For i = 1 To .Value
.Offset(1, 0).EntireRow.Insert
.EntireRow.Copy _
Destination:=Cells(r + 1, "A")
Next i
r = r + i
End If
End With
Loop
End Sub

 

[JW]

Here's one way.  This is assuming that your quantity is in column C.
It also includes no error handling, so if you have text in column C
other than the header row, this will error out.

Sub oneWay()
    Dim r As Long
    r = 2
    Do Until IsEmpty(Cells(r, "C"))
        With Cells(r, "C")
            If .Value = "1" Then
                r = r + 1
            Else
                For i = 1 To .Value
                    .Offset(1, 0).EntireRow.Insert
                    .EntireRow.Copy _
                        Destination:=Cells(r + 1, "A")
                Next i
                r = r + i
            End If
        End With
    Loop
End Sub

oops. Forgot to declare i.

Sub oneWay()
Dim r As Long, i As Long
r = 2
Do Until IsEmpty(Cells(r, "C"))
With Cells(r, "C")
If .Value = "1" Then
r = r + 1
Else
For i = 1 To .Value
.Offset(1, 0).EntireRow.Insert
.EntireRow.Copy _
Destination:=Cells(r + 1, "A")
Next i
r = r + i
End If
End With
Loop
End Sub

 

[Don Guillett]

Try

Sub insertandcopy()
mc = "c"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
mv = Cells(i, mc)
If mv > 1 Then
Rows(i).Copy
Rows(i).Resize(mv).Insert
End If
Next i
End Sub

 

[maju]

ok. thanks. I ran the code but i want it to give me for example, if the
quantity is 2, then i will get 1 extra row with data inserted, if 3, 2 rows
inserted etc

 

[maju]

why did u resize it?

Try

Sub insertandcopy()
mc = "c"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
mv = Cells(i, mc)
If mv > 1 Then
Rows(i).Copy
Rows(i).Resize(mv).Insert
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[maju]

Don, i keep getting error msg at
Rows(i).Resize(mv).Insert
Why?

 

[Don Guillett]

Your Original Post is what I used. Adjust accordingly.

 

[maju]

Thanks for all ur help. code adjusted but if is # 2, i want just i additional
row & if 3, i want 2 additional row etc. it is not giving me that. it keeps
giving me 2 addiotnal row for 2 and 3 additional row for 3.

 

[Don Guillett]

Send your workbook to the address below, along with all you have done so I
can take a look.