Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Auto Insert Rows of Data??
I get get a spreadsheet each day that gives me orders by day for the year by
Offer #. If there was no order on a particular day for an offer, it skips that day. I want to insert the missing day in the correct row and auto insert "0" for number of orders and $. Ex. -in the data below, there were no orders beetween 12/23 and 12/27. I want to automatically insert rows with the missing date and "0" for orders and Deamand. Is there function or snippet of code to help??? Thanks, Tom PX05C1A* PX05C2A* OrderDate Orders Demand Orders Demand 12/23/04 1 $39.00 0 $0.00 12/27/04 4 $214.50 0 $0.00 12/28/04 2 $68.00 0 $0.00 12/29/04 1 $118.00 0 $0.00 12/30/04 10 $799.50 0 $0.00 |
#2
|
|||
|
|||
Tom,
Select the cells with the dates, and then run the macro below. HTH, Bernie MS Excel MVP Sub TryNow() Dim mySel As Range Dim Earliest As Variant Dim Latest As Variant Dim Dates As Variant Dim myCount As Integer myCount = 0 Set mySel = Selection Earliest = Application.Min(mySel) Latest = Application.Max(mySel) For Dates = Earliest To Latest If IsError(Application.Match(Dates, mySel, False)) Then myCount = myCount + 1 With mySel(mySel.Cells.Count + myCount) .Value = Dates .NumberFormat = mySel(1).NumberFormat .Offset(0, 1).Value = 0 .Offset(0, 2).Value = 0 End With End If Next Dates End Sub "tojo107" wrote in message ... I get get a spreadsheet each day that gives me orders by day for the year by Offer #. If there was no order on a particular day for an offer, it skips that day. I want to insert the missing day in the correct row and auto insert "0" for number of orders and $. Ex. -in the data below, there were no orders beetween 12/23 and 12/27. I want to automatically insert rows with the missing date and "0" for orders and Deamand. Is there function or snippet of code to help??? Thanks, Tom PX05C1A* PX05C2A* OrderDate Orders Demand Orders Demand 12/23/04 1 $39.00 0 $0.00 12/27/04 4 $214.50 0 $0.00 12/28/04 2 $68.00 0 $0.00 12/29/04 1 $118.00 0 $0.00 12/30/04 10 $799.50 0 $0.00 |
#3
|
|||
|
|||
this worked perfectly thank you -you saved me hours a day!!!!
"Bernie Deitrick" wrote: Tom, Select the cells with the dates, and then run the macro below. HTH, Bernie MS Excel MVP Sub TryNow() Dim mySel As Range Dim Earliest As Variant Dim Latest As Variant Dim Dates As Variant Dim myCount As Integer myCount = 0 Set mySel = Selection Earliest = Application.Min(mySel) Latest = Application.Max(mySel) For Dates = Earliest To Latest If IsError(Application.Match(Dates, mySel, False)) Then myCount = myCount + 1 With mySel(mySel.Cells.Count + myCount) .Value = Dates .NumberFormat = mySel(1).NumberFormat .Offset(0, 1).Value = 0 .Offset(0, 2).Value = 0 End With End If Next Dates End Sub "tojo107" wrote in message ... I get get a spreadsheet each day that gives me orders by day for the year by Offer #. If there was no order on a particular day for an offer, it skips that day. I want to insert the missing day in the correct row and auto insert "0" for number of orders and $. Ex. -in the data below, there were no orders beetween 12/23 and 12/27. I want to automatically insert rows with the missing date and "0" for orders and Deamand. Is there function or snippet of code to help??? Thanks, Tom PX05C1A* PX05C2A* OrderDate Orders Demand Orders Demand 12/23/04 1 $39.00 0 $0.00 12/27/04 4 $214.50 0 $0.00 12/28/04 2 $68.00 0 $0.00 12/29/04 1 $118.00 0 $0.00 12/30/04 10 $799.50 0 $0.00 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pull Current Month's Data Out of List - Repost | Excel Discussion (Misc queries) | |||
can't insert more rows even though not at max rows | Excel Worksheet Functions | |||
INSERT ROWS WITHOUT SCREWING-UP FORMULAS! | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
how do I insert multiple rows in excel after every row of data | Excel Discussion (Misc queries) |