Range.Find 在 VBA Excel 中一月和十一月(二月和十二月)之间没有区别 [英] Range.Find not making a difference between January and November (February and December) in VBA Excel

问题描述

假设我有以下微不足道的任务:

• 将 2016 年 1 月到 2018 年 6 月的第一个日期写成一行
• 找到 01-January-2016 并将其涂成红色
• 使用Range.Find()

因此，我创建了一个代码，从 1 到 30 循环并写入每个月的第一个日期.然后我使用 Rows(1).Find(CDate("01.01.2016")) 或 Rows(1).Find(DateSerial(2016,1,1))我认为我的任务几乎准备好了.

我运行代码，我在 Excel 2010 和 Excel 2016 中都看到了这一点:

问题:这背后有什么原因吗?或者 Range.Find() 函数是否记录为这样的行为?

公共子TestMe()细胞清除调暗至长对于 cnt = 1 到 30Cells(1, cnt) = DateAdd("M", cnt - 1, DateSerial(2016, 1, 1))Cells(1, cnt).NumberFormat = "MMM-YY"下一个Dim foundRange 作为范围设置 foundRange = Rows(1).Find(CDate("01.01.2016"))'Set foundRange = Rows(1).Find(DateSerial(2016, 1, 1)) '同样的错误结果'Set foundRange = Rows(1).Find("01.01.2016") '没有找到任何东西如果没有找到Range Is Nothing ThenfoundRange.Interior.Color = vbRed万一结束子

一般来说，Range.Find() 有一个可选的After 参数，它是Range 的第一个单元格.在我们的例子中，省略了 After 参数，因此它被认为是 A1 并且最后检查它.如果您在第一个循环后停止代码并从 Excel 中手动删除 Nov 16，那么您继续该代码，它将以红色返回单元格 Jan 16.>

就认为已找到 11 月而言，它会将其归还并且不会再进一步​​.问题更像是 - 1-November-2016 与 1-January-2016 在什么逻辑上相同，甚至部分相同?

解决方案

每当 .Find(LookAt:=xlPart) 用于某个日期范围时，它都会将日期与它们的

<小时>

为了避免这个错误，最好的办法是明确地查看xlWhole.如果没有被引用，Range.Find() 寻找部分字符串.

另一个问题是 Range.Find 开始的地方.根据

Let's say that I have the following trivial task:

• Write the first date of the months from January 2016 to June 2018 on a row
• Find 01-January-2016 and color it in red
• Use Range.Find()

Thus, I create a code, looping from 1 to 30 and writing the first date of each month. Then I use Rows(1).Find(CDate("01.01.2016")) or Rows(1).Find(DateSerial(2016,1,1)) and I consider my task almost ready.

I run the code and I see this in both Excel 2010 and Excel 2016:

Question: Is there any reason behind it? Or is the Range.Find() function documented to act like this?

Public Sub TestMe()

    Cells.Clear

    Dim cnt             As Long
    For cnt = 1 To 30
        Cells(1, cnt) = DateAdd("M", cnt - 1, DateSerial(2016, 1, 1))
        Cells(1, cnt).NumberFormat = "MMM-YY"
    Next cnt

    Dim foundRange      As Range
    Set foundRange = Rows(1).Find(CDate("01.01.2016"))
    'Set foundRange = Rows(1).Find(DateSerial(2016, 1, 1))  'the same false result
    'Set foundRange = Rows(1).Find("01.01.2016")             'does not find anything
    If Not foundRange Is Nothing Then
        foundRange.Interior.Color = vbRed
    End If

End Sub

In general, the Range.Find() has an optional After parameter, which is the first cell of the Range. In our case, the After parameter is omitted, thus it is considered to be A1 and it is checked last. If you stop the code after the first loop and you manually delete Nov 16 from Excel, then you continue the code, it will return the cell Jan 16 in red.

As far as November is considered found, it gives it back and it does not go further. The question is more like - in what logic is 1-November-2016 the same as 1-January-2016, even partially?

解决方案

Whenever .Find(LookAt:=xlPart) is used upon a range of dates, it takes the dates not with their .Value2, but it silently converts them to String following the American date format - MM/DD/YY and looks into this string. The display format of the date in Excel is completely irrelevant, as long as the cell is formatted as a date.

Thus, every day of January can be found in November and every day of February can be found in December as a substring, making possible 58 (or 59 in a leap year) different mistakes within a calendar year:

---

In order to avoid this mistake, the best solution is to look at xlWhole explicitly. If it is not referred, Range.Find() looks for partial string.

Another issue is where Range.Find starts. According to The Documentation It starts AFTER the supplied or default cell and only looks at the start cell after it cycles back.

The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Notice that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell. If you do no specify this argument, the search starts after the cell in the upper-left corner of the range.

So, by not defining XlWhole and a start cell, the first cell that is searched is B1 not A1, and it finds the date partially before finding the correct date when it cycles around.

So setting the start cell at the end of the range will do it:

Set foundRange = Rows(1).Find(DateSerial(2016, 1, 1), [XFD1])

这篇关于Range.Find 在 VBA Excel 中一月和十一月(二月和十二月)之间没有区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！