VBA Excel：将范围值分配给新范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20637835/
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: Assigning range values to a new range
提问by teepee
I am having trouble assigning values from one workbook range to a range in my current workbook. When I assign my range using Range("A1:C1") this code works fine, however when my range is defined using Range(Cells(1,1),Cells(1,3)) the function fails:
我无法将一个工作簿范围内的值分配到当前工作簿中的某个范围。当我使用 Range("A1:C1") 分配我的范围时，此代码工作正常，但是当我使用 Range(Cells(1,1),Cells(1,3)) 定义我的范围时，函数失败：
Sub CopyRange()
Dim inputExcel As Excel.Application, BookA As Workbook
Path_A = ThisWorkbook.Path & "\Book_A.xlsx"
Set inputExcel = New Excel.Application
Set BookA = inputExcel.Workbooks.Open(Path_A, ReadOnly:=True)
'THIS WORKS:
ThisWorkbook.Sheets(1).Range("A1:C1").Value = _
BookA.Sheets(1).Range("A1:C1").Value
'THIS DOESN'T WORK:
ThisWorkbook.Sheets(1).Range(Cells(1, 1), Cells(1, 3)).Value = _
BookA.Sheets(1).Range(Cells(1, 1), Cells(1, 3)).Value
End Sub
I know this must be a simple syntax issue but I haven't been able to figure it out. How can I get the Range assignments using "Cells" to work?
我知道这一定是一个简单的语法问题，但我一直无法弄清楚。如何使用“单元格”进行范围分配？
回答by JosieP
you've gotta qualify the Cells calls with a worksheet object too:
您还必须使用工作表对象限定 Cells 调用：
ThisWorkbook.Sheets(1).Range(ThisWorkbook.Sheets(1).Cells(1, 1), ThisWorkbook.Sheets(1).Cells(1, 3)).Value = _
BookA.Sheets(1).Range(BookA.Sheets(1).Cells(1, 1), BookA.Sheets(1).Cells(1, 3)).Value
for contiguous ranges like that you can also use Resize:
对于这样的连续范围，您还可以使用调整大小：
ThisWorkbook.Sheets(1).Cells(1, 1).Resize(, 3).Value = _
BookA.Sheets(1).Cells(1, 1).Resize(, 3).Value
回答by David Zemens
I think ultimately you are going about this in not the best way to solve the problem. You have object.object.object.objectnotation in your code, which is cumbersome and hard to interpret and fix.
我认为最终你会以不是解决问题的最佳方式来解决这个问题。您object.object.object.object的代码中有符号，这很麻烦且难以解释和修复。
If you define some more variables, the code will be easier to troubleshoot and solve your problem:
如果您定义更多变量，代码将更容易排除故障和解决您的问题：
Dim myAddr as String 'A string to represent the Address of the range
myAddr = Cells(1,1).Address & ":" & Cells(1,3).Address
ThisWorkbook.Sheets(1).Range(myAddr).Value = BookA.Sheets(1).Range(myAddr).Value
