Excel 2010 VBA - 运行时错误1004 - 使用匹配将范围从一张纸复制到另一张 [英] Excel 2010 VBA - runtime error 1004 - using match to copy range from one sheet to another

问题描述

我看不到我错了什么

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))

推荐答案

当您从当前未选择/激活的工作表中引用多单元格范围时，VBA不喜欢。 （奇怪的是，这不是单个单元格范围的问题...）无论如何，这是导致您的错误在这种情况下。此语句是指两个不同表格上的多单元格范围：

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:

Worksheets(1).Range(Cells(Sheet1RowNumber, 2), Cells(Sheet1RowNumber, 8)) = _ 
    Worksheets(28).Range(Cells(Sheet28RowNumber, 2), Cells(Sheet28RowNumber, 8))

通过将要复制的范围存储在缓冲区（ temp 下）可以将其拆分成两行。在每个陈述之前，激活相应的工作表。这样做：

You can split it up into two lines by storing the range to be copied in a buffer (temp below). Before each statement, activate the appropriate sheet. This works:

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

这篇关于Excel 2010 VBA - 运行时错误1004 - 使用匹配将范围从一张纸复制到另一张的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！