VBA Excel：将范围值分配到新的范围 [英] VBA Excel: Assigning range values to a new range

问题描述

我无法将值从一个工作簿范围分配到当前工作簿中的范围。当我使用范围（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

这篇关于VBA Excel：将范围值分配到新的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！