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:

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
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:

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
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
 

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
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
 

ramshakku

New Member
Joined
Sep 26, 2014
Messages
8
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
 

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,161
Messages
5,527,156
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top