Range.Find在1月和11月(2月和12月)之间没有区别 [英] Range.Find not making a difference between January and November (February and December) in VBA Excel
问题描述
假设我有以下琐碎的任务:
- 在2016年1月至2018年6月这几个月的第一个日期写上行
- 查找2016年1月1日并用红色上色
- 使用
Range.Find()
因此,我创建了一个代码,从1循环到30,并写出每个月的第一个日期。然后我使用 Rows(1).Find(CDate( 01.01.2016))
或 Rows(1).Find(DateSerial(2016, 1,1))
,我认为我的任务几乎已经准备就绪。
我运行代码,并且在Excel 2010和Excel 2016中都看到了:
问题:
它背后有什么原因吗?还是 Range.Find()
函数被记录为这样?
Public Sub TestMe()
Cells.Clear
Dim cnt as long
for cnt = 1至30
Cells(1,cnt) = DateAdd( M,cnt-1,DateSerial(2016,1,1))
单元格(1,cnt).NumberFormat = MMM-YY
下一个cnt
Dim foundRange As Range
Set foundRange = Rows(1).Find(CDate( 01.01.2016))
'Set foundRange = Rows(1).Find(DateSerial(2016,1, 1))'相同的错误结果
'设置foundRange = Rows(1).Find( 01.01.2016)'找不到任何东西
如果找不到FoundRange则为
foundRange。 Interior.Color = vbRed
End if
End Sub
通常, Range.Find()
具有可选的 After
参数,它是<$的第一个单元格。 c $ c>范围。在我们的例子中,省略了 After
参数,因此它被认为是 A1
并最后检查。如果在第一个循环后停止代码,并从Excel中手动删除 Nov 16
,则继续执行代码,它将返回单元格 1月16日
用红色表示。
据认为找到了11月,它退还了它,而且没有走得更远。问题更像是-在什么逻辑上 2016年11月1日与 2016年1月1日相同,甚至是部分?
每当在一定范围的日期上使用 .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在1月和11月(2月和12月)之间没有区别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!