Create new record if >0

thart21

Board Regular
Joined
Mar 3, 2005
Messages
159
I have a workbook that contains the following columns - Ordered_Qty and Not_Ordered_Qty. I need to write some VBA that, if both Ordered_Qty and Not_Ordered_Qty" are >0, create a new record on the same worksheet and move that qty into the new record in the same field, replacing the previous value in the original record with 0. I'm using Excel 2007.

I've only been able to find VBA samples for parsing out comma separated values in the same column.

Row ETA_Date Ordered_Qty Not_Ordered_Qty
1 4/15/2011 145 0
2 5/16/2011 25 56
3 4/15/2011 75 114

Need it to be:

Row ETA_Date Ordered_Qty Not_Ordered_Qty
1 4/15/2011 145 0
2 5/16/2011 25 0
3 0/00/0000 56
4 4/15/2011 75 0
5 0/00/0000 114

The idea is that they don't want to see an ETA Date for something that hasn't even been ordered yet.

I will try to modify one of the other modules I found pertaining to parsing out comma separated fields in the meantime, thanks for any help.

Toni
 
Last edited:

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Glory

Well-known Member
Joined
Mar 16, 2011
Messages
640
Split method? Or are values separated by spaces in separate columns?

Edit: The forum scrapped your formatting, didn't it? You can paste excel tables directly across if you want to.
 
Last edited:

thart21

Board Regular
Joined
Mar 3, 2005
Messages
159
Yes, my formatting got messed up. Not sure if "split" is the correct word, I just need an extra record created below the original to move that Not_Ordered_Qty to.

I'll repost in a few. Thanks for the reply
 

thart21

Board Regular
Joined
Mar 3, 2005
Messages
159
Excel Workbook
ABC
1ETA DateOrdered QtyNot Ordered Qty
24/15/20111450
35/16/20112556
44/15/201175114
5
6To go to:
7ETA DateOrdered QtyNot Ordered Qty
84/15/20111450
95/16/2011250
100/00/0000056
114/15/2011750
120/00/00000114
Sheet1
Excel 2007
 

Watch MrExcel Video

Forum statistics

Threads
1,108,492
Messages
5,523,260
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top