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分数'！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.

更新:
我根据罗恩·罗森菲尔德的回答写了以下文章:

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数组并循环遍历该数组，将搜索范围加快十倍.

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屋！