vba - 对象变量或带有 block 变量 未使用 find 函数在循环中设置

Question

Sub Main()
Dim FName As Variant, R As Long, DirLoc As String, i As Integer
R = 1
i = 1
DirLoc = ThisWorkbook.Path & "\" 'location of files
FName = Dir(DirLoc & "*.csv")
Do While FName <> ""
    ImportCsvFile DirLoc & FName, ActiveSheet.Cells(R, 1)
    R = ActiveSheet.UsedRange.Rows.Count + 1
    FName = Dir
    For i = 1 To 100
        Worksheets("RAW").Range("B1:B6").Copy
        Worksheets("filtered").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial , Transpose:=True
        Cells.Find(What:="Run:", After:=Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlPart, _
            SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Select
        Worksheets("filtered").Cells(10, 10).Value = i
        If ActiveCell <> "Run:" & i Then
            Exit For
        End If
    Next i
    DeleteFiltered
Loop
End Sub

我遇到了麻烦，因为我收到了一个错误:

           `Cells.Find(What:="Run:" & i, After:=Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlPart, _
            SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Select`

当我删除“& i”时，错误不会发生。我用它来导入一个数据文件，然后找到一个特定的“Run:1”。然后它应该复制数据并找到下一个 Run 以及之后的一个。这就是为什么我需要 & i。我怎样才能使这项工作？

还有一些代码部分肯定没问题，所以我把它们省略了。

Answer 1

如果 Cells.Find(What:="Run:" & i,...找不到匹配项，Select语句的一部分将导致错误。您应该始终存储 Find 的结果在一个范围变量中，然后测试 Nothing .

将此声明添加到您的代码中:

Dim cellsFound As Range

并替换这个:

    Cells.Find(What:="Run:", After:=Cells(1, 1), _
        LookIn:=xlValues, LookAt:=xlPart, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Select
    Worksheets("filtered").Cells(10, 10).Value = i
    If ActiveCell <> "Run:" & i Then
        Exit For
    End If

和:

Set cellsFound = Worksheet("sheet_name").Cells.Find(What:="Run:" & i, After:=Worksheet("sheet_name").Cells(1, 1), _
        LookIn:=xlValues, LookAt:=xlPart, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
If Not(cellsFound Is Nothing) Then
    Worksheets("filtered").Cells(10, 10).Value = i
Else
    ' not found
    Exit For
End If