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

问题描述

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

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

（团队成员的用户ID

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

但是，该日期值实际上是通过公式从另一张具有相同布局的工作表中提取的。其中一个范围的公式为： ='QA分数'！K2：M2

我正在尝试使用Range.Find以查找给定月份的第一天和该月的结束日期（基于用户输入）-例如6月1日至6月15日-但无济于事。看起来像这样：

 设置rngMin = .Find（What：= DateValue（minDate），_ 
 LookIn ：= xlFormulas，_ 
 LookAt：= xlWhole）
  

在其他用途​​中，我已经找到了以这种方式确定日期，但是问题的价值来自公式，这似乎是这里的问题。我看到了建议。真的希望这里的人知道一些事情。

更新：

我已经基于罗恩·罗森菲尔德的回答编写了以下代码：

  Dim UsedArr作为变体：UsedArr = SrcWS.UsedRange 
 blFound = False 
对于i = LBound（UsedArr，1）到UBound（ UsedArr，1）
对于j = LBound（UsedArr，2）到UBound（UsedArr，2）
如果UsedArr（i，j）= MinDate然后
 blFound = True 
退出对于
结束如果
接下来的
如果blFound = True然后退出对于
接下来的
  

解决方案

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

如果可以确定工作表上日期的格式，一种解决方案是搜索等效的字符串。以您的示例为例，这样的方法将起作用：

  Option Explicit 
 Sub GetDates（）
 Const findDate作为日期=＃5/11/2017＃
 Dim findStr作为字符串
 
 Dim R作为范围，WS作为工作表
设置WS = Worksheets（ Sheet1）
 
 findStr = Format（findDate， dd-mmm）
 
与WS 
设置R = .Cells.Find（what：= findStr，LookIn：= xlValues，lookat ：= xlWhole）
如果Not R无效，则MsgBox findDate&在& R.Address 
结尾为
 
 End Sub 
  

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

另一种更健壮的方法是遍历现有单元格，寻找日期的数字表示形式（使用 Value2 属性）：

  Sub GetDates2（）
 Const findDate As Date =＃5/11/2017＃
 Dim R作为范围，C作为范围，WS作为工作表
 
 Set WS = Worksheets（ sheet1）
设置R = WS.UsedRange 
 
每个R中的C 
如果C.Value2 = CDbl（findDate）然后MsgBox findDate&在& C.地址
下一个C 
结束子
  

如果范围较大要进行搜索，可以通过将范围读取到VBA数组并循环遍历该数组来将其加速十倍。

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)

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"

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

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:

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:

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

解决方案

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.

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

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.

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