Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Difference Between Dates (VBA Excel 2003)

    Hi,

    I cant seem to work out the syntax to compare two columns of dates, A & B, to see if there are any rows where the date difference is more than 54 days. I then need to move any rows where the difference is over 54 days to an new worksheet and display those results.

    It would be ok if it were just two cells, but I need a loop to go through the two columns to check the difference and move the data if necessary.

    Is anyone able to give some guidance please??

    Many thanks in advance.

    Regards.
    Lee

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Difference Between Dates (VBA Excel 2003)

    Take this macro as starting point, and modify it to suit your needs. You will have to adjust the sheet name, for example.

    Sub CompareDates()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    ' Existing source sheet
    Set ws1 = Worksheets("Sheet1")
    ' New target sheet
    Set ws2 = Worksheets.Add
    Dim r As Long
    Dim n As Long
    Dim t As Long
    n = ws1.Range("A65536").End(xlUp).Row
    For r = n To 1 Step -1
    If Abs(ws1.Range("A" & r) - ws1.Range("B" & r)) > 54 Then
    t = t + 1
    ws1.Rows®.Cut Destination:=ws2.Rows(t)
    ' Optional: delete row
    ws1.Rows®.Delete
    End If
    Next r
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Difference Between Dates (VBA Excel 2003)

    Will do Hans.

    Many thanks for your help, its much appreciated.

    Regards.
    Lee

Posting Permissions

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