CODEWITHSUNDEEP
vbadatetimecompare
BeginnedCSharp
BeginnedCSharp

Reputation: 39

VBA comparing two dates

I know it sounds like a simple task but I've been trying various approach and still didn't get success yet.

  1. I have a big data set with many variables and two of them are of use. First is called "symbol" (aaa,bbb,ccc,etc) and second is "date" (e.g. 2012-05-15 20:00); One symbol corresponds to multiple dates;

  2. I wanted to delete some symbols with specific date (e.g., bbb, 2012-05-15) and I have the list of deleting symbols;

  3. I was trying to compare the date in dataset and the deleting date in a for loop and to delete the entire row if there equal to each other for specific symbol and that's where the problem is;

  4. The code is below:

    Dim d1 As Date
d1 = DateSerial(2012, 5, 15) 'This is the deleting date  
MsgBox (IsDate(d1)) 'This returns true   
MsgBox (IsDate(DateValue(Cells(4, 9)))) 'This returns true

For j = lastRow To 2 Step -1  
   If Cells(j,2).Value = "bbb" And (CDate(d1) = DateValue(Cells(j, 9))) Then Rows(j).EntireRow.Delete  
Next

  5. VBA gave me "type mismatch" error. I also tried If d1 = DateValue(Cells(j,9)) Then Rows(j).EntireRow.Delete but this didn't work either. The screenshot has the value in the cell, when it raises the error.

    The screenshot has the value is in the cell, when it raises the error

Upvotes: 1

Views: 15185

Answers (2)

Rrgg
Rrgg

Reputation: 69

I actually tried this and it works. The only thing I changed is that I put actual dates into the date column, column I (which other commenters noted is perhaps your problem) and I made lastrow a number.

Just ensure that you have dates in column I and last row is a number and you shouldn't get that error

Upvotes: 0

David Zemens
David Zemens

Reputation: 53623

Looks like you're encountering an empty cell which can't be converted to a DateValue.

In the immediate window, try:

?DateValue("") and you'll get the same error.

You just need to extend your logic a bit:

For j = lastRow To 2 Step -1  
   If Cells(j,2).Value = "bbb" And Not Cells(j,9) = vbNullString And IsDate(Cells(j,9) Then
      If (CDate(d1) = DateValue(Cells(j, 9))) Then Rows(j).EntireRow.Delete  
   End If
Next

Upvotes: 2

Related Questions