Looping
 

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

Looping
 

On May 19, 3:38*pm, maju wrote:
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

Looping
 

On May 19, 4:13*pm, JW wrote:
On May 19, 3:38*pm, maju wrote:

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

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

Looping
 

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

"maju" wrote in message
...
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

Looping
 

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

"Don Guillett" wrote:

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

"maju" wrote in message
...
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

Looping
 

why did u resize it?

"Don Guillett" wrote:

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

"maju" wrote in message
...
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

Looping
 

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

"Don Guillett" wrote:

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

"maju" wrote in message
...
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

Looping
 

Your Original Post is what I used. Adjust accordingly.
1,no new row. if is it 2, 2 additional row with
same datas info, if 3, 3 additional rows with same data. thanks

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"maju" wrote in message
...
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

"Don Guillett" wrote:

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

"maju" wrote in message
...
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

Looping
 

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" wrote:

Your Original Post is what I used. Adjust accordingly.
1,no new row. if is it 2, 2 additional row with
same datas info, if 3, 3 additional rows with same data. thanks

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"maju" wrote in message
...
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

"Don Guillett" wrote:

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

"maju" wrote in message
...
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

Looping
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"maju" wrote in message
...
Don, i keep getting error msg at
Rows(i).Resize(mv).Insert
Why?

"Don Guillett" wrote:

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

"maju" wrote in message
...
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