VBA Excel：将范围值分配到新的范围

本文介绍了VBA Excel：将范围值分配到新的范围的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我无法将值从一个工作簿范围分配到当前工作簿中的范围。当我使用范围（A1：C1）分配我的范围时，此代码工作正常，但是当我的范围使用范围（单元格（1,1），单元格（1,3））定义时，函数失败：

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:

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

$ b $我知道这必须是一个简单的语法问题，但我无法弄清楚。如何使用单元格获取范围分配？

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?

推荐答案

您必须使用工作表限定单元格调用对象也是：

you've gotta qualify the Cells calls with a worksheet object too:

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