Range.Find 是一个公式的日期 [英] Range.Find on a Date That is a Formula

问题描述

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

I receive a workbook 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.

一张纸包含有关处理错误的信息.

(已编辑团队成员的用户 ID)

One sheet contains information about processing errors.

(team members' user IDs redacted)

每个日期由合并的 1x3 范围表示，所讨论的日期格式为dd-mmm"；例如《01-Jun》

Each date is represented by a merged 1x3 range with the date in question formatted as "dd-mmm" e.g. "01-Jun"

该日期是通过公式从另一个具有相同布局的工作表中提取的.一个这样的范围的公式是:='QA Scores'!K2:M2

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

我尝试使用 Range.Find 来定位给定月份的第一天和该月的结束日期(基于用户输入) - 例如6 月 1 日至 6 月 15 日.

I attempted 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.

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

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

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

更新:
我根据 Ron Rosenfeld 的回答写了以下内容:

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

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

推荐答案

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

Dates are tricky to find with the Range.Find method. One of the issues is that in VBA, dates are of the Date data 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.

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

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.

另一种更强大的方法是遍历现有单元格，寻找日期的数字表示(使用 Value2 属性):

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

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

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

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.

这篇关于Range.Find 是一个公式的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！