vba - 使用带有时间序列的 excel 查找

Question

我对 .find 有疑问命令。我想创建一个宏来复制一个范围并将其粘贴到使用 find 找到的特定单元格之后命令(将事件单元格移动到数据值的偏移量):

Sub value()
Dim today As String
Dim lookfor As Range

Sheets(1).Range("C3:C19").Copy
today = "11.nov"

Set lookfor = Cells.Find(What:=today, _
            After:=ActiveCell, _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False).Activate

lookfor.Offset(rowOffset:=1, columnOffset:=3).Paste
End Sub

Answer 1

事实上，您必须检查是否有 Find 的结果。使用 If Not LookFor Is Nothing Then 的方法在使用它之前。

所以我的猜测是 Find方法没有找到您要查找的值的任何内容。

这是您修改后的代码:

Sub test_Veiko_Aunapuu()
Dim FirstAddress As String, _
    ToDay As String, _
    LookFor As Range

ToDay = "11.nov"

Sheets(1).Activate
Sheets(1).Range("C3:C19").Copy

With Sheets(1).Cells
    '----First, define properly the Find method
    Set LookFor = .Find(What:=ToDay, _
                After:=ActiveCell, _
                LookIn:=xlValues, _
                LookAt:=xlPart, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlNext, _
                MatchCase:=False, _
                SearchFormat:=False)

    If Not LookFor Is Nothing Then
    '----If there is a result,
        FirstAddress = LookFor.Address
        'LookFor.Activate
        MsgBox "The row containing " & ToDay & " is : " & LookFor.Row
        'Keep looking with FindNext method : Not usefull for your example
        Do
            '-------------------------------------------------------------
            '----Place instructions to execute on the matched cell/row/...
            LookFor.Offset(rowOffset:=1, columnOffset:=3).Paste

            '-------------------------------------------------------------
            Set LookFor = .FindNext(LookFor)
        'Loop and keep looking until you find again the first result
        Loop While Not LookFor Is Nothing And LookFor.Address <> FirstAddress
    Else
    '----If there is no results, say it
        MsgBox "No matches were found for : " & ToDay, vbCritical + vbOKOnly, "No results"
    End If
End With

End Sub