VBA-Excel -- Cells.Find 中的变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22234378/
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
VBA-Excel -- Variable in Cells.Find
提问by user3388888
I have two spreadsheets (wb1 and wb2). The goal is to select each value in column D of wb1, find the value in column C of wb2, then copy a range of cells (same row as the search value) back to wb1.
我有两个电子表格（wb1 和 wb2）。目标是选择 wb1 的 D 列中的每个值，找到 wb2 的 C 列中的值，然后将一系列单元格（与搜索值相同的行）复制回 wb1。
Here's the code I've managed to pull together thus far:
到目前为止，这是我设法汇总的代码：
Dim rng1 As Range, rng2 As Range
Dim cell as Variant
Dim cell_val as String
Dim wb1 as Workbook, wb2 as Workbook
Dim sh1 as Worksheet, sh2 as Worksheet
Sub Find_Copy_Paste()
set wb1 = Workbooks.Open("c:\explicit\path\to\wb1.xlsm") <---This fails
set wb2 = Workbooks.Open("c:\explicit\path\to\wb2.xlsm") <---This fails
Set sh1 = wb1.Open("Inventory")
set sh2 = wb2.Open ("Sheet1")
set rng1 = wb1.sh1.Range("D6:D1702")
set rng2 = wb2.sh2.Range("C2:C3132")
For Each cell In rng1
ActiveCell.Select
cell_val = Selection.Copy
Windows(wb2).Activate
Cells.Find(What:=(cell_val), After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset (0,1).Range("A1:AH1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(wb1).Activate
ActiveCell.Offset(0,1).Range("A1").Select
ActiveSheet.Paste
cell_val=""
Next
End Sub
Unfortunately, I'm hitting a challenge, and I suspect it has to do with two things: 1) wb1 and wb2 variables and how I've assigned them, and 2) the variable in the Cells.Find part of my code (but I'm still fairly new to VBA, so my suspicions might be way off).
不幸的是，我遇到了一个挑战，我怀疑它与两件事有关：1）wb1 和 wb2 变量以及我如何分配它们，以及 2）我的代码的 Cells.Find 部分中的变量（但是我对 VBA 还是很陌生，所以我的怀疑可能还很遥远）。
回答by PatricK
Try this below, I have simulated your goal with only 1 workbook. If the macro & path is not trusted, you may have issue opening the xlsm files. Here I only have one of them in ReadOnly mode (Workbook 2).
在下面试试这个，我只用 1 个工作簿模拟了你的目标。如果宏和路径不受信任，则您可能无法打开 xlsm 文件。在这里，我只有其中一个处于只读模式（工作簿 2）。
Sub Find_Copy_Paste()
Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim rng1 As Range, rng2 As Range
Dim cell As Range, FoundCells As Range
Set wb1 = Workbooks.Open(Filename:="c:\explicit\path\to\wb1.xlsm",ReadOnly:=False)
Set wb2 = Workbooks.Open(Filename:="c:\explicit\path\to\wb2.xlsm",ReadOnly:=True)
Set sh1 = wb1.Worksheets("Inventory")
Set sh2 = wb2.Worksheets("Sheet1")
Set rng1 = sh1.Range("D6:D1702")
Set rng2 = sh2.Range("C2:C3132")
For Each cell In rng1
If Not IsEmpty(cell) Then
Set FoundCells = rng2.Find(cell.Value)
If Not FoundCells Is Nothing Then
Debug.Print """" & cell.Value & """ found at " & FoundCell.Worksheet.Name & "!" & FoundCell.Address
' Copy Found cell to one column on right of cell being searched for
FoundCells.Copy Destination:=cell.Offset(0, 1)
End If
End If
Next
Set rng1 = Nothing
Set rng2 = Nothing
Set sh1 = Nothing
Set sh2 = Nothing
Set wb1 = Nothing
Set wb2 = Nothing
End Sub
There are many good place to start learning VBA, for Excel 2010, check out the Excel Developer Reference.
有许多开始学习 VBA 的好地方，对于 Excel 2010，请查看Excel 开发人员参考。
