Excel VBA - 在日期范围内使用 Find 方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14448379/
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 VBA - Using Find method on a range of dates
提问by user985779
I am trying to find if a certain date is in a range of dates. This is the range of dates:
我正在尝试查找某个日期是否在某个日期范围内。这是日期范围：
01/01/2013
11/02/2013
29/03/2013
20/05/2013
01/07/2013
05/08/2013
02/09/2013
14/10/2013
11/11/2013
25/12/2013
26/12/2013
Here is the VBA code:
这是VBA代码：
' Format Holiday Rows '
With ConfigData.Range("B8:B18")
Set holidays = .Find(s1.Cells(row_count, 1))
If Not holidays Is Nothing Then
MsgBox s1.Cells(row_count, 1)
End If
End With
In the above code, the first MsgBox that pops up reads "11/01/2013". This makes absolutely no sense, as that value is not in the range.
在上面的代码中，弹出的第一个 MsgBox 显示为“11/01/2013”。这完全没有意义，因为该值不在范围内。
Note: ConfigData.Range("B8:B18") refers to the range of dates shown above.
注意：ConfigData.Range("B8:B18") 指的是上面显示的日期范围。
ALSO: This code is within a for loop that increments the value of s1.Cells(row_count, 1). Starting at 01/01/2013 until 31/12/2013
另外：此代码位于一个 for 循环中，该循环会增加 s1.Cells(row_count, 1) 的值。从 01/01/2013 开始至 31/12/2013
回答by bonCodigo
If you just want to confirm a calendar day in your series is within the holiday list, then you could even use vlookup:
如果您只想确认系列中的日历日在假期列表中，那么您甚至可以使用vlookup：
Dim strFound As String
On Error Resume Next
strFound = Application.Vlookup(s1.Cells(row_count, 1), .Range("B8:B18"), 1, 0)
If IsError(strFound) Then
MsgBox "Not Found"
Else
'-- Found
End If
On Error GoTo 0
回答by user2680041
It is important to note that excel uses american date formatting. ie mm/dd/yyyy and it can therefore be a little tricky to get the .Find() function to work properly. Make sure your variables are formated properly in order for excel to hopefully give you what you're looking for:
需要注意的是，excel 使用美国日期格式。即 mm/dd/yyyy，因此让 .Find() 函数正常工作可能有点棘手。确保您的变量格式正确，以便 excel 为您提供所需的内容：
Dim strdate As String
Dim aCell As Range
strdate = ActiveSheet.Cells(1,1)
strdate = Format(strdate, "Short Date")
On Error Resume Next
Set aCell = Cells.Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlFormulas , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If rCell Is Nothing Then
MsgBox("Date cannot be found. Try Again")
End If
End Sub
Of course there are a lot of annoying things that can happen with the date formatting, but this is assuming the dates you're looking for ar in the "Short Date" format.
当然，日期格式可能会发生很多烦人的事情，但这是假设您正在寻找“短日期”格式的日期。
回答by rich_king
'To find a cell elsewhere in a worksheet with the same specific date as a reference cell: 'First copy all dates to cells immediately to their left. 'Format the copied cells as "General" 'Run this code - then use the dateRow and DateCol variables (eg in vlookup) 'Works in Excel 2013 (The "General" column must not be hidden - Hide by formatting in background colour)
'要在工作表中的其他位置查找与参考单元格具有相同特定日期的单元格： '首先将所有日期复制到紧邻其左侧的单元格。'将复制的单元格格式化为“常规” '运行此代码 - 然后使用 dateRow 和 DateCol 变量（例如在 vlookup 中）'在 Excel 2013 中有效（不得隐藏“常规”列 - 通过背景色格式隐藏）
Dim dateVal
Dim DateRow
Dim DateCol
dateVal = Range("j8").Value 'must be in general format
Cells.Find(What:=dateVal, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
DateRow = ActiveCell.Row
DateCol = ActiveCell.Column
MsgBox (DateRow & " " & DateCol)
End Sub
回答by mkingston
The following code works for me:
以下代码对我有用：
Sub thing()
Dim cell As Range, _
holidays As Range
For Each cell In Range("D1:D365")
With Range("A1:A11")
Set holidays = .Find(cell.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not holidays Is Nothing Then
Debug.Print cell.Value
End If
End With
Next cell
End Sub
If this doesn't work, I'd suggest it's likely you have a cell formatting issue. Select one of your date cells. Go to the immediate window (Alt+F11, then Ctrl+G from Excel), type ? Selection.Value2and press enter. Does that return a numeric value (~41000)?
如果这不起作用，我建议您可能有单元格格式问题。选择您的日期单元格之一。转到即时窗口（Alt+F11，然后在 Excel 中按 Ctrl+G），键入? Selection.Value2并按 Enter。这是否返回一个数值（~41000）？
Alternatively, you could reenter the dates in a completely new sheet (enter the first couple manually and drag down, do not copy and paste as formatting will be copied also) and try again. This should at least remove odd formatting as a potential issue.
或者，您可以在一个全新的工作表中重新输入日期（手动输入第一对并向下拖动，不要复制和粘贴，因为格式也会被复制）并重试。这至少应该消除奇怪的格式作为潜在问题。
