vba VBA比较两个日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19281013/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA comparing two dates
提问by BeginnedCSharp
I know it sounds like a simple task but I've been trying various approach and still didn't get success yet.
我知道这听起来像是一项简单的任务，但我一直在尝试各种方法，但仍然没有成功。
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;
I wanted to delete some symbols with specific date (e.g., bbb, 2012-05-15) and I have the list of deleting symbols;
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;
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 NextVBA 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.
我有一个包含许多变量的大数据集，其中两个有用。第一个称为“符号”（aaa、bbb、ccc 等），第二个称为“日期”（例如 2012-05-15 20:00）；一个符号对应多个日期；
我想删除一些具有特定日期的符号（例如，bbb，2012-05-15）并且我有删除符号的列表；
我试图比较数据集中的日期和 for 循环中的删除日期，如果特定符号彼此相等，则删除整行，这就是问题所在；
代码如下：
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 NextVBA 给了我“类型不匹配”错误。我也试过 If d1 = DateValue(Cells(j,9)) Then Rows(j).EntireRow.Delete 但这也不起作用。屏幕截图在单元格中具有值，当它引发错误时。
回答by David Zemens
Looks like you're encountering an empty cell which can't be converted to a DateValue.
看起来您遇到了一个无法转换为 DateValue 的空单元格。
In the immediate window, try:
在立即窗口中，尝试：
?DateValue("")and you'll get the same error.
?DateValue("")你会得到同样的错误。
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
回答by Rrgg
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.
我实际上尝试过这个并且它有效。我改变的唯一一件事是我将实际日期放入日期列，第一列（其他评论者指出的可能是您的问题），并且我将 lastrow 设为一个数字。
Just ensure that you have dates in column I and last row is a number and you shouldn't get that error
只需确保您在第一列中有日期并且最后一行是一个数字，您不应该收到该错误
