Insert Missing Dates & Other Date query

[KyleG]

Two Queries:
I have a column with dates like this 2/18/2005 but i want the actual cell text to be 18/2/2005, not just what is displayed.

My main query is....
I have a column(A) of dates (see below) that i want to insert rows with the missing dates so that i have all the dates in the sequence even though there will be multiple rows of some dates i need to have a complete date list.
18-Feb-05
18-Feb-05
23-Feb-05
23-Feb-05
23-Feb-05
23-Feb-05
25-Feb-05
25-Feb-05
25-Feb-05
1-Mar-05
2-Mar-05
2-Mar-05
2-Mar-05
2-Mar-05
4-Mar-05
4-Mar-05
4-Mar-05


Thanks

 

[rflyr98]

See if this works for you

Sub Macro1()

Dim thisdate As Date
Dim nextdate As Date

thisdate = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
thatdate = ActiveCell.Value
ActiveCell.Offset(-1, 0).Activate

If (thatdate - thisdate) > 1 Then
ActiveCell.Offset(1, 0).Activate

ActiveCell.Insert

ActiveCell.Value = thisdate + 1
ActiveCell.Offset(-1, 0).Activate

End If

ActiveCell.Offset(1, 0).Activate

End Sub

You could make a loop to run this "x" amount of times

 

[rflyr98]

Oh also I started assuming you had the top cell active and then running the macro, it runs, then steps down.

 

[KyleG]

Ok this works but errors at end so its ok.

Sub Macro1()
Dim thisdate As Date
Dim nextdate As Date
Do Until thisdate = 9 / 22 / 6
thisdate = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
thatdate = ActiveCell.Value
ActiveCell.Offset(-1, 0).Activate
If (thatdate - thisdate) > 1 Then
ActiveCell.Offset(1, 0).Activate
ActiveCell.EntireRow.Insert Shift:=xlDown
ActiveCell.Select
ActiveCell.Value = thisdate + 1
ActiveCell.Offset(-1, 0).Activate
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

So now a new question has formed...
I have done this to two sheets of data...I need to make both sheets have the same number of each date.
So if sheet 1 has 3
23-Feb-05
23-Feb-05
23-Feb-05
I need Sheet 2 to also have 3
and if it is sheet 2 with the greater number then i need sheet 1 to increase the number of rows
So that in the end i am left with the same number of rows in each sheet.

 

[KyleG]

help still needed

No other ideas?