Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2006
    Posts
    1

    Unanswered: Problem with DATE....

    Hi all,
    Given a list of dates with different times. 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.. ( The date will be repeating)

    EXAMPLE

    BEFORE
    row 1 13/6/2005 12:01 --0.1
    row 2 14/6/2005 14:08 --0.5
    row 3 16/6/2005 08:65 --0.12
    row 4 16/6/2005 11:46 --0.31

    AFTER
    row 1 13/6/2005 12:01 --0.1
    row 2 14/6/2005 14:08 --0.5
    row 3 15/6/2005 00:00 --
    row 4 16/6/2005 08:65 --0.12
    row 5 16/6/2005 11:46 --0.31

    Code:
    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
    This code can only work with NO REPEATING date
    Last edited by ru_1985; 10-11-06 at 05:25.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •