Excel VBA-.工作簿之间的查找方法 [英] Excel VBA - .Find method between workbooks

问题描述

问题:
为什么在引用其他工作簿时Range.Find方法不起作用?

Question:
Why is the Range.Find method not working when referencing a different workbook?

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

Problem: I'm attempting to copy data between workbooks, but the Range.Find method is stopping with a "Run-time Error 1004". I'm using Excel 2007 on a Windows 7 machine.

详细信息: 在两个工作簿上，每个工作簿仅引用或使用Sheet1.我有一个具有以下概述的过程(ztest):

Details: On two workbooks, only Sheet1 is referenced or used for each workbook. I have a procedure (ztest) with the following outline:

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

1. Format the sheet
2. Loop through all cells in column E of workbook #1
3. Using the Range.Find method, find the value in column E of the workbook #2
4. Once found, set workbook #1 offset column = workbook #2 offset column

我想使用HLOOKUP之类的.Find- not 来做到这一点.

I'd like to do this with .Find - not using HLOOKUP or the like.

我已经对代码进行了一些简化，以缩小实际发生的范围.这没有显示上面的步骤4，但是在包含.Find方法的语句中的步骤3中发生了错误:

I've simplified the code somewhat, to narrow down what exactly is going on. This doesn't show step 4 above, but the error occurs in step 3, in the statement containing the .Find method:

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

推荐答案

非常重要的一点是，您必须很好地构造代码，以使理解没有困难.如果需要，请编写额外的代码行，以便即使6个月后看到代码，也可以确定代码的作用.还要完全限定您的对象.

It's very important that you structure your code very well so that there is no difficulty in understanding it. If it is required, write extra lines of code so that even if you see the code after 6 months, you can identify what your code does. Also fully qualify your objects.

尝试一下(未测试).我已经注释了代码.因此，如果您不了解某些内容，请回发

Try this (UNTESTED). I have commented the code. So if you do not understand something then post back

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

这篇关于Excel VBA-.工作簿之间的查找方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！