使用变量定义范围 [英] Using a variable to define a range

问题描述

我正在尝试将8 x 1范围的值存储在相同尺寸的范围内，但在工作簿中的另一个工作表上。这将很容易，除了我的脚本循环遍历这些相同维度的不同范围，我需要将它们全部存储在第二张表上。目前我的代码看起来像这样：

I am attempting to store the value of an 8 x 1 range into a range of identical dimensions, but on another sheet in the workbook. This would be easy except that my script is looping through different ranges of these same dimensions and I need to store them all on the second sheet. Currently my code looks like this:

Sheets("Sheet1").Range(Cells(i, 2), Cells(i + 7, 2)).Value = Sheets("Sheet2").Range("OriginalData").Value

其中i是用作循环中的迭代器的变量。

Where "i" is the variable being used as the iterator in the loop.

此代码引发错误错误1004应用程序定义或对象定义错误。有人可以解释我做错了什么，以及如何以这种方式动态定义范围对象？

This code throws an error "Error 1004 "Application-defined or Object-defined error"". Can someone explain what I'm doing wrong, and how to properly define range objects dynamically in this fashion?

推荐答案

您的问题是单元格里面的 Sheets（Sheet1）。范围不知道他们应该属于表格（Sheet1）。

Your problem is that the Cells inside the Sheets("Sheet1").Range don't know that they are supposed to belong to Sheets("Sheet1").

with Sheets("Sheet1")
    .Range(.Cells(i, 2), .Cells(i + 7, 2)) = _
        Sheets("Sheet2").Range("OriginalData").Value
end with

'alternate
Sheets("Sheet1").Range("B" & i).Resize(8, 1) = _
        Sheets("Sheet2").Range("OriginalData").Value

With ... End With语句允许您将父工作表明确地传递到中。范围和 .Cells 与前缀期间（aka full stop ）。

The With ... End With statement allows you to definitively pass the parent worksheet into both .Range and .Cells with the prefix period (aka full stop).

这篇关于使用变量定义范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！