Excel VBA - 在一系列日期中使用查找方法 [英] Excel VBA - Using Find method on a range of dates
问题描述
我正在尝试查找某个日期是否在一定范围内。
这是日期的范围:
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
以下是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
在上面的代码中,弹出的第一个MsgBox会显示为11/01/2013。这绝对没有意义,因为这个值不在这个范围内。
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.
注意:ConfigData.Range(B8:B18)是指上面显示的日期范围。
Note: ConfigData.Range("B8:B18") refers to the range of dates shown above.
另请参见:此代码位于for循环中,该值用于增加s1.Cells(row_count,1)的值。从01/01/2013至31/12/2013
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
推荐答案
如果您只想确认系列中的日历日在节假日列表中,您甚至可以使用 vlookup
:
If you just want to confirm a calendar day in your series is within the holiday list, then you could even use 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
这篇关于Excel VBA - 在一系列日期中使用查找方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!