Excel VBA Range.Find Date That is a formula

Excel VBA Range.Find Date That is a Formula

提问by KOstvoll

I receive a workbook twice a month that contains information about the processing volumes of a call center team. I have no way of modifying the format or layout of the workbook upstream.

我每月收到两次工作簿，其中包含有关呼叫中心团队处理量的信息。我无法修改上游工作簿的格式或布局。

Within this workbook, one sheet contains information about processing errors. It looks like this:

在此工作簿中，一张纸包含有关处理错误的信息。它看起来像这样：

(team members' user IDs redacted)

（已编辑团队成员的用户 ID）

As you can see, each date is represented by a merged 1x3 range with the date in question formatted as "dd-mmm"e.g. "01-Jun"

如您所见，每个日期都由合并的 1x3 范围表示，相关日期的格式为"dd-mmm"例如“01-Jun”

However, that date value is actually pulled via formula from another sheet with the same layout. The formula for one such range reads: ='QA Scores'!K2:M2

但是，该日期值实际上是通过公式从具有相同布局的另一个工作表中提取的。一个这样的范围的公式如下：='QA Scores'!K2:M2

I am attempting to use Range.Find to locate the first day of a given month and an end date in that same month (based on user input) - e.g. June 1 through June 15 - but to no avail. Here's what that looks like:

我试图使用 Range.Find 来定位给定月份的第一天和同月的结束日期（基于用户输入）——例如 6 月 1 日到 6 月 15 日——但无济于事。这是它的样子：

Set rngMin = .Find(What:=DateValue(minDate), _
                   LookIn:=xlFormulas, _
                   LookAt:=xlWhole)

In other uses, I have located a date in this manner, but the added complexity of the value in question coming from a formula seems to be the issue here.

在其他用途​​中，我以这种方式定位了日期，但是来自公式的相关值的增加的复杂性似乎是这里的问题。

I have scoured the usual resources and tried everything I have seen suggested. Really hoping someone here knows something.

我已经搜索了常用资源并尝试了我所看到的所有建议。真的希望这里有人知道一些事情。

UPDATE:
I have written the following code based on Ron Rosenfeld's answer:

更新：
我根据 Ron Rosenfeld 的回答编写了以下代码：

Dim UsedArr As Variant: UsedArr = SrcWS.UsedRange
blFound = False
For i = LBound(UsedArr, 1) To UBound(UsedArr, 1)
    For j = LBound(UsedArr, 2) To UBound(UsedArr, 2)
        If UsedArr(i, j) = MinDate Then
            blFound = True
            Exit For
        End If
    Next
    If blFound = True Then Exit For
Next

回答by Ron Rosenfeld

Dates are tricky to find with the Range.Findmethod. One of the issues is that in VBA, dates are of the Datedata type, but the worksheet does not have that data type. Rather the data type is a number that is formatted to look like a date.

使用该Range.Find方法很难找到日期。问题之一是在 VBA 中，日期属于Date数据类型，但工作表没有该数据类型。而是数据类型是格式化为看起来像日期的数字。

One solution, if you can be certain of the format of the date on the worksheet, is to search for the string equivalent. Given your example, something like this will work:

如果您可以确定工作表上日期的格式，一种解决方案是搜索等效字符串。鉴于你的例子，这样的事情会起作用：

Option Explicit
Sub GetDates()
    Const findDate As Date = #5/11/2017#
    Dim findStr As String

Dim R As Range, WS As Worksheet
Set WS = Worksheets("Sheet1")

findStr = Format(findDate, "dd-mmm")

With WS
    Set R = .Cells.Find(what:=findStr, LookIn:=xlValues, lookat:=xlWhole)
    If Not R Is Nothing Then MsgBox findDate & " found in " & R.Address
End With

End Sub

but it is not very robust since, in many cases, the user can change the format.

但它不是很健壮，因为在许多情况下，用户可以更改格式。

Another method that is more robust, would be to loop through the existing cells, looking for the numeric representation of the date (using the Value2property):

另一种更强大的方法是遍历现有单元格，查找日期的数字表示（使用Value2属性）：

Sub GetDates2()
    Const findDate As Date = #5/11/2017#
    Dim R As Range, C As Range, WS As Worksheet

Set WS = Worksheets("sheet1")
Set R = WS.UsedRange

For Each C In R
    If C.Value2 = CDbl(findDate) Then MsgBox findDate & " found in " & C.Address
Next C
End Sub

If you have a large range to search, this can be sped up by a factor of ten by reading the range into a VBA array and looping through the array.

如果您要搜索的范围很大，则可以通过将范围读入 VBA 数组并循环遍历该数组，将搜索速度提高 10 倍。

回答by Rajesh S

@KOstvall,, I would like to suggest this simple find method, since you are trying to get the Date.

@KOstvall，我想推荐这个简单的查找方法，因为您正在尝试获取日期。

Set rng = Sheet1.Range("A:A").Find("1/1/2017", LookIn:=xlValue, LookAt:=xlWhole)

Set rng = Sheet1.Range("A:A").Find("1/1/2017", LookIn:=xlValue, LookAt:=xlWhole)

回答by Tony M

Update: I've modified the code below to set the active cell before the find. The second animated gif show the code running

更新：我修改了下面的代码以在查找之前设置活动单元格。第二个动画 gif 显示代码运行

One approach is to do a find based upon the specific date format, which in your case appears to be "[$-en-US]dd-mmm;@". The following is a simple example, which you could then adjust to your need. To make this example work, place 3 dates into "A1:A3", with just one of them having the desired format, and then run the macro. The animated gif shows how to set the format.

一种方法是根据特定日期格式进行查找，在您的情况下似乎是"[$-en-US]dd-mmm;@". 以下是一个简单的示例，您可以根据需要对其进行调整。要使此示例工作，请将 3 个日期放入“A1:A3”中，其中只有一个具有所需格式，然后运行宏。动画 gif 显示了如何设置格式。

Sub dateFormatFind()
Dim sh As Worksheet, searchR As Range
Dim cell As Range, resultR As Range
Set sh = Worksheets("Sheet5")
Set searchR = sh.Range("A1:A3")
Set resultR = sh.Range("C1")
sh.Range("A1").Activate
Application.FindFormat.NumberFormat = "[$-en-US]dd-mmm;@"
resultR = searchR.Find(What:="", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True)
End Sub