为什么查找函数在范围内找不到部分日期 - VBA

Question

请参阅下面的代码。find函数找不到范围内的部分日期。我知道我们可以使用for loop在范围内找到部分日期，但是为什么find函数在查找部分日期中不起作用？这个问题很简单，但我在Google中找不到答案...

Sub Demo()
Dim day As Long
Dim monthYear As String
Dim ws As Range

    monthYear = Right(ThisWorkbook.ActiveSheet.Range("C10"), 7) ' monthYear = 08-2017
    Set ws = ThisWorkbook.ActiveSheet.Range("D3:D30")
    Set c = ws.Find(monthYear, Lookat:=xlPart)
End Sub

ws范围

11-01-2016
29-02-2016
28-03-2016
27-04-2016
27-05-2016
27-06-2016
29-08-2016
29-08-2016
27-09-2016
27-10-2016
28-11-2016
27-12-2016
27-01-2017
27-02-2017
27-03-2017
27-04-2017
29-05-2017
27-06-2017
13-08-2017
28-08-2017

Answer 1

尝试这样：

monthYear = Right(CStr(ThisWorkbook.ActiveSheet.Range("C10")), 7)

尝试运行以下操作：

Public Sub TestMe()
    Dim rngCell as Range
    For Each rngCell In Range("D3:D30")
        If Year(rngCell) = 2017 Then
            Debug.Print rngCell.Address
        End If
    Next rngCell
End Sub

然后使用Year()。

重建逻辑

关于Find()，尝试构建像这样的小东西：

Public Sub TestMe()
    Dim rngRange    As Range
    Set rngRange = ActiveSheet.Cells.Find("08.2016")
    Debug.Print rngRange.Address
End Sub

它应该起作用。

Answer 2

此代码将搜索日期转换为在Long中进行搜索以使整个月循环，然后将其转换回Date。因此，它会循环整个月（慢）。

如果您有一个庞大的工作簿，则不建议使用。

Sub Demo()
Dim monthYear As Date
Dim rng As Range, rng2 As Range, cellFound As Range
Dim ws As Worksheet
Dim i As Long, lastrow As Long
    Set ws = ThisWorkbook.Sheets(1)
    lastrow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row

    Set rng = ws.Range(ws.Cells(3, 4), ws.Cells(lastrow, 4))
    monthYear = (Right(ThisWorkbook.ActiveSheet.Range("C10"), 7)) ' monthYear = 08-2017
    intDaysInMonth = day(DateSerial(Year(monthYear), Month(monthYear) + 1, 0))
    For i = 0 To intDaysInMonth - 1
        LookingFor = CLng(monthYear) + i
        LookingForString = CStr(CDate(LookingFor))
        With rng
            Set cellFound = .Find(what:=LookingForString, LookIn:=xlValues, MatchCase:=False)
                If Not cellFound Is Nothing Then
                    FirstAddress = cellFound.Address
                    Do
                        Debug.Print cellFound.Address
                        Set cellFound = .FindNext(cellFound)
                    Loop While Not cellFound Is Nothing And cellFound.Address <> FirstAddress
                 End If
        End With
    Next i
End Sub

这很混乱，有更好的方法来优化它。我认为您正在收到错误13：键入不匹配。

Answer 3

在Lookin参数中使用XLVALUE的查找函数在日期范围内搜索字符串值很复杂。

单元格属性必须是默认日期格式以外的日期格式。

确保列的宽度足够大，以避免被#####替换的日期。

。

您可以使用类似的东西：

ThisWorkbook.ActiveSheet.Range("C10").NumberFormat = "d-m-yyyy"
t = Split(ThisWorkbook.ActiveSheet.Range("C10").Text, "-")
monthYear = t(1) & "-" & t(2)
Set ws = ThisWorkbook.ActiveSheet.Range("D3:D30")
ws.NumberFormat = "d-m-yyyy"
Set c = ws.Find(monthYear, LookIn:=xlValues, Lookat:=xlPart)