CODEWITHSUNDEEP
excelvba
Wiktor Orczykowski
Wiktor Orczykowski

Reputation: 17

VBA Date Comparison not working

I've got part of my code:

o = Workbooks(repfile).Worksheets(1).Range("b:b").Cells.SpecialCells(xlCellTypeConstants).Count
For k = o To 2 Step -1
dte = CDate(Replace(Cells(k, 8), ".", "/"))
Cells(k, 8) = dte
    If Cells(k, 8) < udate And Cells(k, 8) > udate2 Then
    Rows(k).EntireRow.Delete
    End If
Next

In previous parts of the code, user specifies dates for which he wants to obtain report (udate and udate2) E.g. I Specify that I want to see only rows with dates between 09/01/2017 and 09/30/2017 (mm/dd/yyyy) and the rest will be deleted. However this comparison doesn't work. When I make a breakpoint in the IF line to check the variables I see:

Cells(k,8)= 11/9/2017 udate= 9/1/2017 udate2= 9/30/2017

So according to my code, this row should be deleted, but it's not. I already tried some other options like:

Cells(k, 8) < DateValue(udate) And Cells(k, 8) > DateValue(udate2)

or

Cells(k, 8).value < DateValue(udate) And Cells(k, 8).value > DateValue(udate2)

or

CDate(Cells(k, 8)) < CDate(udate) And Cells(k, 8) > CDate(udate2)

and a few more, but nothing works.

Upvotes: 1

Views: 1015

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

I think your logic is incorrect:

If Cells(k, 8) < udate And Cells(k, 8) > udate2 Then

if #11/9/2017# < #9/1/2017#  And #11/9/2017# > #9/30/2017#

will evaluate to false.

Perhaps you want:

If Cells(k, 8) < udate Or Cells(k, 8) > udate2 Then

Upvotes: 1

Related Questions