Problem with Date....

[ru_1985]

--------------------------------------------------------------------------------

Given a list of dates. Would it be able to check the list of dates, sort it in ascending order and check whether there is any missing date, if there is missing date can it be able to insert the date rather than a blank row. The example is shown below..

EXAMPLE

BEFORE
row 1 13/6/2005 0.1
row 2 14/6/2005 0.5
row 3 16/6/2005 0.12
row 4 17/6/2005 0.31

AFTER
row 1 13/6/2005 0.1
row 2 14/6/2005 0.5
row 3 15/6/2005
row 4 16/6/2005 0.12
row 5 17/6/2005 0.31

Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row).Sort key1:=Range("B2"), order1:=xlAscending, header:=xlNo
    Columns("B:B").TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1))
    Range("E3").Select
    While Not IsEmpty(ActiveCell)
    If ActiveCell <> ActiveCell.Offset(-1, 0) + 1 And ActiveCell <> ActiveCell.Offset(-1, 0) Then
      t = ActiveCell - ActiveCell.Offset(-1, 0)
      ActiveCell.Resize(t - 1).EntireRow.Insert
      ActiveCell.Offset(t, 0).Select
    Else
      ActiveCell.Offset(1, 0).Select
    End If
    Wend
    Columns("E:G").Delete
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

 

[pgc01]

Hi ru_1985

Your code makes reference to a lot of columns.

I assumed that the data is like in your post. In column A starting in A1 you have dates. In column B you have the corresponding values.

For that configuration, please try:

Sub SortCompleteDate()
Dim lRow As Long, lRowMax As Long

Application.ScreenUpdating = False
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Sort key1:=Range("A1"), order1:=xlAscending, header:=xlNo
lRowMax = Cells(Rows.Count, 1).End(xlUp).Row
For lRow = lRowMax To 2 Step -1
    Do While Cells(lRow, "A") <> Cells(lRow, "A").Offset(-1) + 1
        Cells(lRow, "A").Resize(, 2).Insert
        Cells(lRow, "A") = Cells(lRow, "A").Offset(1) - 1
    Loop
Next
Application.ScreenUpdating = True
End Sub

Hope this helps
PGC

 

[ru_1985]

The code is great, but there is a problem in it. wat if my date contain time in it?

Example:
Before
Row 1 2006/09/01 15:15 0.1
Row 2 2006/09/03 13:10 0.4


After
Row 1 2006/09/01 15:15 0.1
Row 2 2006/09/02 23:00
Row 3 2006/09/03 13:10 0.4

 

[pgc01]

Hi again

If your date has time we'll work just with the date.

I'm creating the days at 00:00:00 because you did not explain how you found the 23:00 in the inserted line.

Please explain the algorithm for the time in a newly created day.

Sub SortCompleteDate()
Dim lRow As Long, lRowMax As Long

Application.ScreenUpdating = False
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Sort key1:=Range("A1"), order1:=xlAscending, header:=xlNo
lRowMax = Cells(Rows.Count, 1).End(xlUp).Row
For lRow = lRowMax To 2 Step -1
    Do While Int(Cells(lRow, "A")) <> Int(Cells(lRow, "A").Offset(-1)) + 1
        Cells(lRow, "A").Resize(, 2).Insert
        Cells(lRow, "A") = Int(Cells(lRow, "A").Offset(1)) - 1
    Loop
Next
Application.ScreenUpdating = True
End Sub

Hope this helps
PGC

 

[ru_1985]

It's a mistake... Don need to set at 23:59... why did the code hang whenever i go it when my source code???