Excel VBA - .在工作簿之间查找方法

Question

问题:
为什么引用不同工作簿时 Range.Find 方法不起作用？

问题:我正在尝试在工作簿之间复制数据，但 Range.Find 方法因“运行时错误 1004”而停止。我在 Windows 7 计算机上使用 Excel 2007。

详细信息:在两个工作簿中，每个工作簿仅引用或使用 Sheet1。我有一个程序(ztest)，其大纲如下:

1. 设置工作表格式
2. 循环遍历工作簿 #1 的 E 列中的所有单元格
3. 使用 Range.Find 方法查找工作簿 #2 的 E 列中的值
4. 找到后，设置工作簿 #1 偏移列 = 工作簿 #2 偏移列

我想使用 .Find 来完成此操作 - 不使用 HLOOKUP 等。

我稍微简化了代码，以缩小到底发生了什么。这没有显示上面的步骤 4，但错误发生在步骤 3 中，在包含 .Find 方法的语句中:

Public Sub ztest2()
'set workbook titles
Const w1 As String = "05AR 20130920.xlsx"
Const w2 As String = "05AR 20130923.xlsx"
Dim cl As Variant

With Workbooks(w2).Worksheets(1)
  'format the sheet
  .Range("A1", "D1").EntireColumn.Hidden = True
  'loop through all cells column E of workbook #1
  For Each cl In .Range("E2", Cells(Rows.Count, "E").End(xlUp))
    'find value of current cell in column E, workbook #2
    Workbooks(w1).Worksheets(1) _
    .Range("E2", Cells(Rows.Count, "E").End(xlUp)) _
    .Find(what:=cl.Value, LookIn:=xlValues).Select
  Next
End With

End Sub

Answer 1

很好地构建代码非常重要，这样理解它就不会有困难。如果需要，请编写额外的代码行，这样即使您在 6 个月后看到代码，您也可以确定代码的用途。还完全限定您的对象。

试试这个(未经测试)。我已经评论了代码。因此，如果您不明白某些内容，请回复

Const w1 As String = "05AR 20130920.xlsx"
Const w2 As String = "05AR 20130923.xlsx"

Sub ztest2()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim cl As Range, ws1Rng As Range, ws2Rng As Range, aCell as Range
    Dim lRowW1 As Long, lRowW2 As Long

    '~~> Define your workbook and worksheets here
    Set wb1 = Workbooks(w1)
    Set ws1 = wb1.Sheets(1)
    Set wb2 = Workbooks(w2)
    Set ws2 = wb2.Sheets(1)

    '~~> Work with First workbook to get last row and define your range
    With ws1
        lRowW1 = .Range("E" & .Rows.Count).End(xlUp).Row
        Set ws1Rng = .Range("E2:E" & lRowW1)
    End With

    '~~> Work with Second workbook to get last row and define your range
    With ws2
        .Range("A1", "D1").EntireColumn.Hidden = True

        lRowW2 = .Range("E" & .Rows.Count).End(xlUp).Row
        Set ws2Rng = .Range("E2:E" & lRowW2)

        For Each cl In ws2Rng
            '~~> Do the find
            Set acell = ws1Rng.Find(what:=cl.Value, LookIn:=xlValues)

            '~~> Check if found or not. This is required else you will
            '~~> get an error if no match found
            If Not acell Is Nothing Then
                '
                '~~> Do what ever you want here
                '
            End If
        Next
    End With
End Sub