Insert new row based on condition ?

ramshakku

New Member
Joined
Sep 26, 2014
Messages
8
Before :-
Item NumberQty to ProduceQuanity ProducedRemainng QuantityDate
A100040060028/09/2014
B52003400180028/09/2014
C120007500450028/09/2014
D50027522528/09/2014
E80008000028/09/2014
F120009000300028/09/2014

<tbody>
</tbody>


After :-

Item NumberQty to ProduceQuanity ProducedRemainng QuantityDate
A100040060028/09/2014
A60029/09/2014
B52003400180028/09/2014
B180029/09/2014
C120007500450028/09/2014
C450029/08/2014
D50027522528/09/2014
D22529/08/2014
E80008000028/09/2014
F120009000300028/09/2014
F300029/08/2014

<tbody>
</tbody>

If remaining quantity > 0, new line has to be created in the Excel along with Remaining Quantity.
How to achieve to my desired outcome ? Please do the needful.

Regards,
RAM
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Code:
firstRow = 2
lastRow = Range("A" & Rows.Count).End(xlup).Row
i = firstRow
Do Until i > lastRow
     If Range("D" & i).value > 0 And _
          Range("D" & i).value <> "" Then
          i = i + 1
          Range("A" & i).EntireRow.Insert
          Range("A" & i).value = Range("A" & i -1).value
          Range("B" & i).value = Range("D" & i - 1).value
          Range("E" & i).value = Range("E" & i -1).value
          lastRow = Range("A" & Rows.Count).End(xlup).Row
     End If
     i = i + 1
Loop
 
Last edited:
Upvote 0
This works too.
Code:
firstRow = 2
lastRow = Range("A" & Rows.Count).End(xlUp).Row
i = firstRow
Do Until i > lastRow
     If Range("D" & i).Value > 0 And _
          Range("D" & i).Value <> "" Then
          i = i + 1
          [COLOR=#ff0000]Rows(i).Insert[/COLOR]
          Range("A" & i).Value = Range("A" & i - 1).Value
          Range("B" & i).Value = Range("D" & i - 1).Value
          Range("E" & i).Value = Range("E" & i - 1).Value
          lastRow = Range("A" & Rows.Count).End(xlUp).Row
     End If
     i = i + 1
Loop
 
Upvote 0
You should use this code instead and here's why. If you run any of the two other codes 1 time, it will work. But if you run it again, it will create duplicates. Go ahead and give it a try and see so you know what I'm talking about. Use this code instead so that it makes sure you aren't creating a duplicate.
Code:
firstRow = 2
 lastRow = Range("A" & Rows.Count).End(xlUp).Row
 i = firstRow
 Do Until i > lastRow
      If Range("D" & i).Value > 0 And _
           Range("D" & i).Value <> "" And_
               [COLOR=#ff0000]Range("B" & i + 1).Value <> Range("D" & i).Value[/COLOR] Then
                     i = i + 1
                     [COLOR=#ff0000]Rows(i).Insert
[/COLOR]                     Range("A" & i).Value = Range("A" & i - 1).Value
                     Range("B" & i).Value = Range("D" & i - 1).Value
                     Range("E" & i).Value = Range("E" & i - 1).Value
                     lastRow = Range("A" & Rows.Count).End(xlUp).Row
      End If
      i = i + 1
 Loop
 
Upvote 0
Hi,

I am getting an following compilation error. (Invalid Outside Procedure)

-- removed inline image ---



This is my current sheet :-
Item NumberQty to ProduceQuanity Produced
Remainng Quantity
Date
A10001000
026/09/2014
A20001000
100027/09/2014
A100080020028/09/2014
B52005200027/09/2014
B52003400180028/09/2014
C1200012000027/09/2014
C120006000600028/09/2014
D500500027/09/2014
D100027572528/09/2014
E80008000027/09/2014
E120008000400028/09/2014
F90009000026/09/2014
F80006000200027/09/2014
F120009000300028/09/2014

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

My expected Outcome :-
Item NumberQty to ProduceQuanity ProducedRemainng Quantity Date
A100010000 26/09/2014
A200010001000 27/09/2014
A1000800200 28/09/2014
A200 29/09/2014
B520052000 27/09/2014
B520034001800 28/09/2014
B1800 29/09/2014
C12000120000 27/09/2014
C1200060006000 28/09/2014
C6000 29/09/2014
D5005000 27/09/2014
D1000275725 28/09/2014
D725 29/09/2014
E800080000 27/09/2014
E1200080004000 28/09/2014
E4000 29/09/2014
F900090000 26/09/2014
F800060002000 27/09/2014
F1200090003000 28/09/2014
F3000 29/09/2014

<colgroup><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

Please do the needful.

Regards,
RAM
 
Upvote 0
Please run the code using the F8 button. Do not run the code using the Run button. Then you can tell me exactly which line of code brings back the error. Even better, you can google ScreanOMatic and record your screen while you do this. It will upload the video to YouTube and you can post the link here so I can see what you did. What you asked for is extremely easy and so I am assuming it was a user error or you forgot to mention something. It would be helpful to figure out the source of the problem if you record what you did using ScreanOMatic or some other recording software.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top