Excel VBA - .Find 工作簿之间的方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18964935/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - .Find method between workbooks
提问by Aaron Thomas
Question:
Why is the Range.Findmethod not working when referencing a different workbook?
问题：
为什么Range.Find在引用不同的工作簿时该方法不起作用？
Problem:I'm attempting to copy data between workbooks, but the Range.Findmethod is stopping with a "Run-time Error 1004". I'm using Excel 2007 on a Windows 7 machine.
问题：我试图在工作簿之间复制数据，但该Range.Find方法因“运行时错误 1004”而停止。我在 Windows 7 机器上使用 Excel 2007。
Details:On two workbooks, only Sheet1 is referenced or used for each workbook. I have a procedure (ztest) with the following outline:
详细信息：在两个工作簿上，每个工作簿仅引用或使用 Sheet1。我有一个程序（ztest），其大纲如下：
- Format the sheet
- Loop through all cells in column E of workbook #1
- Using the
Range.Findmethod, find the value in column E of the workbook #2 - Once found, set workbook #1 offset column = workbook #2 offset column
- 格式化工作表
- 循环遍历工作簿 #1 的 E 列中的所有单元格
- 使用该
Range.Find方法，在工作簿 #2 的 E 列中找到值 - 找到后，设置工作簿 #1 偏移列 = 工作簿 #2 偏移列
I'd like to do this with .Find- notusing HLOOKUP or the like.
我想这样做.Find-不使用 HLOOKUP 等。
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 .Findmethod:
我已经稍微简化了代码，以缩小到底发生了什么。这没有显示上面的第 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
采纳答案by Siddharth Rout
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.
很好地构建代码非常重要，这样就不会很难理解它。如果需要，请编写额外的代码行，以便即使您在 6 个月后看到代码，也可以识别代码的作用。也完全限定您的对象。
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
