vba Excel 找不到日期（无论格式选项如何）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23897313/
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
Excel cannot find dates (regardless of formatting options)
提问by Jhecht
My boss keeps a small excel file that mimics the desk calendar we use to keep track of random things at our job, like hours and counts of reports sent. It's laid out in a grid of 7x5 (roughly 5, the last row only has a few cells in it since the months aren't generally divisible by 7).
我的老板保留了一个小的 excel 文件，它模仿了我们用来跟踪工作中随机事情的台历，例如发送报告的时间和计数。它以 7x5 的网格布局（大约 5，最后一行只有几个单元格，因为月份通常不能被 7 整除）。
How the dates and such work is the first cell, A1, has the first date of the month. Each subsequent cell was hand-coded by my boss to take the value of the date cell in the tile before it and add 1. However, these cells aren't side-by-side, they are splayed out. I am trying to get it to sum each weeks total hours (and reports), but when I attempt to call the Cells.Findmethod, it doesn't return anything.
日期和此类工作的方式是第一个单元格 A1 具有该月的第一个日期。每个后续单元格都由我的老板手工编码，以获取它之前图块中日期单元格的值并加 1。但是，这些单元格不是并排的，它们是展开的。我试图让它对每周的总小时数（和报告）求和，但是当我尝试调用该Cells.Find方法时，它没有返回任何内容。
I thought that perhaps it was because the cells are formatted in the American fashion of mm/dd/yy, so I tried to search for 05/12/14and it didn't work. So I attempted to search for the default date format that the Date()function returns, m/d/yyyy, and it still doesn't find anything. I've tried to set the .Find()to check both Values and Formulas, and neither works. Even if I use the ctrl+Fto find them by hand, it tells me there is no such value in the work sheet.
我想可能是因为单元格是按照美国方式格式化的mm/dd/yy，所以我尝试搜索05/12/14并没有奏效。所以我试图搜索Date()函数返回的默认日期格式，m/d/yyyy，但它仍然没有找到任何东西。我试图设置.Find()检查值和公式，但都不起作用。即使我使用ctrl+F手动查找它们，它也会告诉我工作表中没有这样的值。
I've searched around, and none of the posts that I found had a solution that worked - the Cells.Findmethod always returns either A1, the first time it is called, or Nothingon every subsequent call.
我四处搜索，我发现的帖子都没有一个有效的解决方案 - 该Cells.Find方法总是A1在第一次被调用时或Nothing在每次后续调用时返回。
My code currently is minimal, but here it is.
我的代码目前是最少的，但在这里。
Code
代码
Sub Worksheet()
Dim r As Range, d As Date, fwkn As Integer, lwkn As Integer, wkn As Integer, col As Collection
'The purpose of this is to get the week totals using the below functions.
d = DateValue([A1].Value)
fwkn = WeekNum(d) 'First week number
d = DateSerial(Year(d), month(d) + 1, 1) - 1 'Gets us the last day of the month
lwkn = WeekNum(d) 'Last Week Number
last_of_month = Day(d)
For i = 1 To last_of_month
d = DateSerial(Year(d), month(d), i)
Set r = Cells.Find(What:=d, LookIn:=xlFormulas, LookAt:=xlWhole, After:=Range("a1"))
If Not r Is Nothing Then
Debug.Print r.Address
End If
Next
End Sub
Function WeekNum(d As Date) As Integer
' You can see examples of this at
' http://www.cpearson.com/excel/weeknum.htm
WeekNum = CInt(Format(d, "ww", vbMonday))
End Function
Does anyone have any idea why Excel wouldn't find the date?
有谁知道为什么 Excel 找不到日期？
回答by Ron Rosenfeld
Here is an example of using looping to find the dates. The dates are in a variety of formats, including text:
这是使用循环查找日期的示例。日期有多种格式，包括文本：
Code:
代码：
Option Explicit
Sub FindDatesLooping()
'Some date in A1
Dim R As Range, C As Range
Dim D As Date
Dim I As Long
Set R = ActiveSheet.UsedRange
D = CDate([A1])
For I = 1 To Day(DateSerial(Year(D), Month(D) + 1, 0))
D = DateSerial(Year(D), Month(D), I)
For Each C In R
If D = C Then
Debug.Print C.Address, D
End If
Next C
Next I
End Sub
Returns in the Immediate Window:
在立即窗口中返回：
$C 5/3/2014
$E 5/15/2014
$D 5/20/2014
$A 5/28/2014
$G 5/31/2014
Dates in the immediate window are in US format m/d/yyyy; but if I change my Regional Settings, the immediate window dates will return in whatever format (and all five dates are still returned).
即时窗口中的日期采用美国格式 m/d/yyyy；但是如果我更改了我的区域设置，即时窗口日期将以任何格式返回（并且仍然返回所有五个日期）。
回答by dcromley
This seems to work:
这似乎有效：
Set r = Cells.Find(what:=DateValue(d), LookIn:=xlFormulas, LookAt:=xlWhole, After:=Range("a1"))
From: Chip Pearson http://www.cpearson.com/excel/DateTimeVBA.htm#Finding
来自：Chip Pearson http://www.cpearson.com/excel/DateTimeVBA.htm#Finding
EDIT: OK, it should have LookIn:=xlValues, sorry
编辑：好的，它应该有 LookIn:=xlValues，抱歉
Option Explicit
Sub Main()
Cells(1, 1) = "5/1/2014"
Range(Cells(2, 1), Cells(31, 1)).Formula = "=a1+1"
Dim i&, fwkn&, lwkn&, wkn&, last_of_month&
Dim r As Range, d As Date
'The purpose of this is to get the week totals using the below functions.
d = DateValue([A1].Value)
fwkn = WeekNum(d) 'First week number
d = DateSerial(Year(d), Month(d) + 1, 1) - 1 'Gets us the last day of the month
lwkn = WeekNum(d) 'Last Week Number
last_of_month = Day(d)
For i = 1 To last_of_month
d = DateSerial(Year(d), Month(d), i)
Set r = Cells.Find(What:=DateValue(d), LookIn:=xlValues, LookAt:=xlWhole, After:=Range("a1"))
If Not r Is Nothing Then
Debug.Print r.Address
End If
Cells(i, 2) = r.Address
Next i
End Sub
Function WeekNum(d As Date) As Integer
' You can see examples of this at
' http://www.cpearson.com/excel/weeknum.htm
WeekNum = CInt(Format(d, "ww", vbMonday))
End Function
回答by SeanC
The range has to be the full area you want to search, not just the initial cell.
范围必须是您要搜索的完整区域，而不仅仅是初始单元格。
set c = Cells.Find(CDate("1/1/2015"), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
This line should set c to the cell you are looking for. Notice there is no range, as I'm simply using Cellsto refer to the entire sheet.
You need to save this address (if it's not Nothing) as vba will keep looping if not checked to stop.
此行应将 c 设置为您要查找的单元格。注意没有范围，因为我只是Cells用来引用整个工作表。
您需要保存此地址（如果不是Nothing），因为如果未选中停止，vba 将继续循环。
firstAddress = c.Address
now you can keep looking if there are more dates to find:
现在您可以继续查看是否还有更多日期可供查找：
Do
'do your stuff here, then look for the next cell
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
