Excel 2010 VBA - 运行时错误 1004 - 使用匹配将范围从一张工作表复制到另一张工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7484216/
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 2010 VBA - runtime error 1004 - using match to copy range from one sheet to another
提问by user954589
i can't see where i've gone wrong
我看不出我哪里出错了
Dim Sheet1RowNumber As Variant, Sheet28RowNumber As Variant, sStaffName As String
sStaffName = Worksheets(1).Cells(7, 1).Value
Sheet28RowNumber = Application.Match(sStaffName, Worksheets(28).Range("a:a"), 0)
Sheet1RowNumber = Application.Match(sStaffName, Worksheets(1).Range("a:a"), 0)
Worksheets(1).Range(Cells(Sheet1RowNumber, 2), Cells(Sheet1RowNumber, 8)) = Worksheets(28).Range(Cells(Sheet28RowNumber, 2), Cells(Sheet28RowNumber, 8))
回答by Jean-Fran?ois Corbett
VBA doesn't like it when your refer to multi-cell ranges from a sheet that isn't currently selected/activated. (Oddly, this is not a problem for single-cell ranges...) Anyhow, that's what's causing your error in this case. This statement refers to multi-cell ranges on two different sheets:
当您从当前未选择/激活的工作表中引用多单元格范围时，VBA 不喜欢它。（奇怪的是，这对于单单元格范围来说不是问题......）无论如何，这就是在这种情况下导致错误的原因。此语句指的是两个不同工作表上的多单元格范围：
Worksheets(1).Range(Cells(Sheet1RowNumber, 2), Cells(Sheet1RowNumber, 8)) = _
Worksheets(28).Range(Cells(Sheet28RowNumber, 2), Cells(Sheet28RowNumber, 8))
You can split it up into two lines by storing the range to be copied in a buffer (tempbelow). Before each statement, activate the appropriate sheet. This works:
您可以通过将要复制的范围存储在缓冲区（temp如下）中将其分成两行。在每个语句之前，激活相应的工作表。这有效：
Dim Sheet1RowNumber As Variant, Sheet28RowNumber As Variant, sStaffName As String
Dim temp As Variant
sStaffName = Worksheets(1).Cells(7, 1).Value
Sheet28RowNumber = Application.Match(sStaffName, Worksheets(2).Range("a:a"), 0)
Sheet1RowNumber = Application.Match(sStaffName, Worksheets(1).Range("a:a"), 0)
Worksheets(2).Activate
temp = Worksheets(2).Range(Cells(Sheet28RowNumber, 2), Cells(Sheet28RowNumber, 8))
Worksheets(1).Activate
Worksheets(1).Range(Cells(Sheet1RowNumber, 2), Cells(Sheet1RowNumber, 8)) = temp
