Compare Dates on VBA

Question

I'm trying to take items which have an earlier date.

When I run this code the output is the same as input. It tries to find the earlier items but it couldn't compare so all items are copied.

Private Sub Macro1()
a = Worksheets("SVS").Cells(Rows.Count, 1).End(xlUp).Row

For i = 3 To a

    If Worksheets("SVS").Cells(i, 22).Value < CDate("28/02/2023") Then
        Worksheets("SVS").Rows(i).Copy
        Worksheets("Summary").Activate
        b = Worksheets("Summary").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("Summary").Cells(b + 1, 1).Select
        ActiveSheet.Paste
        Worksheets("SVS").Activate
    End If

Next i

Application.CutCopyMode = False

ThisWorkbook.Worksheets("SVS").Cells(1, 1).Select

End Sub

How do I get the earlier dates?

Please install Rubberduck, the free and fantastic, opensource, Addin for VBA. Then look at the results of the code inspections. If you can't do that please take the time to work through a VBA tutorial. — freeflow, Jan 11 '23 at 10:43
Are you sure that there are `Date` values in the range you try evaluating and not strings looking as `Date`? Please, try inserting `Debug.Print isDate(Worksheets("SVS").Cells(i, 22).Value): stop` immediately after `For i = 3 To a`. What does it return in `Immediate Window` when code stops there? — FaneDuru, Jan 11 '23 at 10:43
Without seeing some sample data it is hard to judge (use [Markdown Table Generator](https://www.tablesgenerator.com/markdown_tables) to insert them). Reading [How to avoid using select](https://stackoverflow.com/a/23913882/16578424) and [How to avoid copy/paste](https://stackoverflow.com/a/64611707/16578424) already helps you. Also Reading [ask] and [repro] will help you to improve your question. — Ike, Jan 11 '23 at 10:45
In Excel, dates are simple numbers and the value 1 stands for one entire day. So by simply subtracting 1 or 2 or any other number, you can find the difference in days between two dates. — Dominique, Aug 29 '23 at 12:27

score 2 · Answer 1 · answered Jan 11 '23 at 12:10

2

Check you have a valid date to compare with.

Option Explicit
Private Sub Macro1()

    Dim wb As Workbook, ws As Worksheet, v
    Dim lastrow As Long, i As Long, b As Long, n As Long
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Summary")
    b = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    With wb.Sheets("SVS")
        lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
    
        For i = 3 To lastrow
            v = .Cells(i, 22) ' col V
            If IsDate(v) Then
                If CDbl(v) < DateSerial(2023, 2, 28) Then
                    b = b + 1
                    .Rows(i).Copy ws.Cells(b, 1)
                    n = n + 1
                End If
            End If
        Next i
    End With
    MsgBox n & " rows copied to Summary", vbInformation, lastrow - 2 & " rows checked"
    
End Sub

answered Jan 11 '23 at 12:10

CDP1802

13,871
2
7
17

I tried just now. It shows that 0 copied cells when your code is run – Semih Jan 11 '23 at 12:16
01/02/2023. Also I checked the type of this. It is Short Date type and all other dates are the same type. – Semih Jan 11 '23 at 12:26
Under Date category and *14/03/2012 type. – Semih Jan 11 '23 at 13:27
@semith I meant what value do you see on spreadsheet if you format the cell as General. – CDP1802 Jan 11 '23 at 13:32
For an example, I see 44958 when I format the cell that includes 01/02/2023 – Semih Jan 11 '23 at 13:37
@semith What row is that on ? How many rows does the message box say was scanned ? Are you sure that date is in column V ? – CDP1802 Jan 11 '23 at 13:47
Huge thanks bro, I'm sure that I'm stupid I didn't notice that there is 1 more hidden column so that's why my code didn't work. Now it's running correct. So you are the geneous one :D – Semih Jan 11 '23 at 14:16
Nice. The thing that confuses me is the `CDbl` i.e. you have already determined that it is a date (`IsDate`). Is it really necessary i.e. doesn't it also 'make' the `DateSerial(...)` to convert to a *Double*, or worse, will it not be converted back to a date before being compared? – VBasic2008 Jan 11 '23 at 14:40
1

@vbasic Yes probably `Cdbl` is unnecessary, just being over cautious with data I don't have visibility of. – CDP1802 Jan 11 '23 at 15:13

score 0 · Answer 2 · answered Jan 11 '23 at 14:15

Append If Earlier Date

Option Explicit

Sub AppendEarlierDate()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim sws As Worksheet: Set sws = wb.Sheets("SVS")
    Dim srg As Range
    Set srg = sws.Range("V3", sws.Cells(sws.Rows.Count, "V").End(xlUp))
    
    Dim surg As Range, sCell As Range, sValue
    
    For Each sCell In srg.Cells
        sValue = sCell.Value
        If IsDate(sValue) Then
            If sValue < DateSerial(2023, 2, 28) Then
                If surg Is Nothing Then
                    Set surg = sCell
                Else
                    Set surg = Union(surg, sCell)
                End If
            End If
        End If
    Next sCell
    
    If surg Is Nothing Then Exit Sub
    
    Dim dws As Worksheet: Set dws = wb.Sheets("Summary")
    If dws.FilterMode Then dws.ShowAllData 
    
    Dim dlCell As Range, dfCell As Range
    
    Set dlCell = dws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious)
    If dlCell Is Nothing Then
        Set dfCell = dws.Range("A1")
    Else
        Set dfCell = dws.Cells(dlCell.Row + 1, "A")
    End If
    
    surg.EntireRow.Copy dfCell
    
End Sub

Compare Dates on VBA

2 Answers2

Append If Earlier Date