excel - Range.Find 以自定义数字格式查找日期

Question

这是我遇到问题的代码的相关部分。

Sub Find_Target()

Dim DayNum As Long
Dim TargetName As String
Dim TargetDay As Range
Dim found As Variant

DayNum = Cells(1, 9)

Set TargetDay = ActiveWorkbook.Sheets("3-2015").Range("A1:B440")
TargetDay.Activate

Set found = TargetDay.Find(DayNum, LookIn:=xlValues)

If found Is Nothing Then
    MsgBox "Nothing found!"
Else
    TargetDay.Select
End If
End Sub

A 列包含合并和未合并的单元格的混合。 Cells(1, 9) 包含一般格式的日期。定期在 A/B 列中将包含一个包含相同数字的合并单元格，但采用自定义数字格式“dddd”。如果我将数字格式更改为通用，则 find 命令有效，否则发现是 Nothing。

我尝试过使用 FindFormat 选项，但没有任何运气。

Answer 1

这个问题有点不清楚，所以我假设您在单元格 I1 中有一个数字，并且您想在当月的同一天找到第一个单元格。假设是这种情况，您可以循环遍历范围并直接与日期进行比较:

Sub Find_Target()
    Dim DayNum As Long
    Dim TargetDay As Range
    Dim found As Range

    DayNum = Cells(1, 9)

    Set TargetDay = ActiveWorkbook.Sheets("3-2015").Range("A1:B440")

    Dim row As Long, col As Long
    For row = 1 To TargetDay.Rows.Count
        For col = 1 To TargetDay.Columns.Count
            If IsDate(TargetDay.Cells(row, col).Value) Then
                If Day(CDate(TargetDay.Cells(row, col).Value)) = DayNum Then
                    Set found = TargetDay.Cells(row, col)
                    Exit For
                End If
            End If
        Next col
        If Not found Is Nothing Then Exit For
    Next row

    If found Is Nothing Then
        MsgBox "Nothing found!"
    Else
        found.Select
    End If
End Sub

如果 I1 是别的东西，那么修改应该很简单——关键是明确地将单元格值视为 VBA 中的日期。