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

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

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