CODEWITHSUNDEEP
vbadateif-statement
Jen
Jen

Reputation: 21

Use IF Statement to compare variable to stored date

I am trying to run a loop of my data that checks a number of different aspects, and if any fail, the macro deletes the row. I have everything working except for the check of the date. If a date in a given cell is equal to the date input initially, I want to keep the row, otherwise I want to delete it. However I cant seem to get my date compare to work. Any thoughts?

Dim myValue As Variant
myValue = InputBox("Enter current as of date (MM/DD/YY)")

Dim dDate As Date
Dim NumberofRows As Long
Dim x As Long

With Worksheets("Transactions")
    NumberofRows = .Cells(.Rows.Count, "C").End(xlUp).Row

    For x = 1 To NumberofRows
        If (.Range("Q" & x).Value) = myValue.Date Then
            ActiveCell.Offset(1, 0).Select
        Else
            ActiveCell.EntireRow.Select
            Selection.Delete
            Range("A" & (ActiveCell.Row)).Select
        End If
    Next x

End With

Upvotes: 2

Views: 50

Answers (3)

braX
braX

Reputation: 11755

Here's a version that compares using Date variables instead, and checks for Cancel being clicked. This way you know you are comparing apples to apples.

Sub Test()
Dim myValue As Variant
Dim dtValue As Date
Dim dtSheet As Date
myValue = InputBox("Enter current as of date (MM/DD/YY)", "Enter Date", Date)

' user cancelled
If myValue = "" Then Exit Sub

If Not IsDate(myValue) Then 
    MsgBox "That is not a date.", vbCritical
    Exit Sub
End If

' user entered a valid date, so continue
dtValue = CDate(myValue)

Dim NumberofRows As Long
Dim x As Long

With Worksheets("Transactions")
    NumberofRows = .Cells(.Rows.Count, "C").End(xlUp).Row

    For x = NumberofRows To 1 Step -1
        If IsDate(.Range("Q" & x).Value) Then
            dtSheet = CDate(.Range("Q" & x).Value)
            If Not dtSheet = dtValue Then
                .Range("Q" & x).EntireRow.Delete
            End If
        Else
            MsgBox "'" & .Range("Q" & x).Value & "' is not a date.", vbCritical
        End If
    Next x

End With
End Sub

Upvotes: 0

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

You can delete all at once, instead of one at a time. This will be a little faster as well, and you do not have to step backwards.

Simply add all the rows you want to delete to a range, then delete the entire range.

Dim myValue As Date, retVal As String
retVal = InputBox("Enter current as of date (MM/DD/YY)")

If retVal = vbNullString Then
    Exit Sub
Else
    myValue = CDate(retVal)
End If

Dim dDate As Date
Dim NumberofRows As Long
Dim x As Long

With Worksheets("Transactions")
    NumberofRows = .Cells(.Rows.Count, "C").End(xlUp).row
    For x = 1 To NumberofRows
        If Not .Range("Q" & x) = myValue Then
            If delRng Is Nothing Then
                Set delRng = .Range("Q" & x)
            Else
                Set delRng = Union(delRng, .Range("Q" & x))
            End If
        End If
    Next x
    If Not delRng Is Nothing Then delRng.EntireRow.Delete
End With

Upvotes: 0

dwirony
dwirony

Reputation: 5450

Try this:

Sub Test()
Dim myValue As Variant
myValue = InputBox("Enter current as of date (MM/DD/YY)")

If Not IsDate(myValue) Then Exit Sub

Dim dDate As Date
Dim NumberofRows As Long
Dim x As Long

With Worksheets("Transactions")
    NumberofRows = .Cells(.Rows.Count, "C").End(xlUp).Row

    For x = NumberofRows To 1 Step -1
        If Not .Range("Q" & x).Value = myValue Then
            .Range("Q" & x).EntireRow.Delete
        End If
    Next x

End With
End Sub

Upvotes: 2

Related Questions