使用 vba 偏移和调整大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15881700/
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
Offset and resize with vba
提问by kirk
I am having some problems with what I think is a simple vba command:
我认为一个简单的 vba 命令有一些问题：
Worksheets("Sheet").Range("namedrange_d").Resize(0, 4).Offset(6, 0).Copy _
Destination:=Worksheets("Sheet1").Range("namedrange").Resize(0, 4).Offset(6, 0)
I want to copy a defaults range 5 cells wide, that is 7 cells below a reference cell (namedrange_d). What is the problem with the syntax?
我想复制 5 个单元格宽的默认范围，即参考单元格 ( namedrange_d)下方的 7 个单元格。语法有什么问题？
回答by Dick Kusleika
Zero is not a valid argument for resize. If you want to keep the original size of the range, simply omit the argument. Otherwise you need to specify the number of rows and columns explicitly. Here's how you would keep the original number of rows
零不是调整大小的有效参数。如果您想保持范围的原始大小，只需省略参数即可。否则，您需要明确指定行数和列数。这是保持原始行数的方法
Worksheets("Sheet").Range("namedrange_d").Resize(, 4).Offset(6, 0).Copy _
Worksheets("Sheet1").Range("namedrange").Resize(, 4).Offset(6, 0)
回答by Floris
I would do the following (I am being very explicit - this tends to make for code that is easier to read and debug, with minimal speed impact. It is not "clever".):
我会执行以下操作（我非常明确 - 这往往会使代码更易于阅读和调试，对速度的影响最小。它不是“聪明”。）：
Dim dataSource As Range
Dim firstCellSource, firstCellDest As Range
Set firstCellSource = Worksheets("Sheet1").Range("namedrange_d").Offset(6, 0)
Set firstCellDest = Worksheets("Sheet1").Range("namedrange").Offset(6, 0)
Set dataSource = Range(firstCellSource, firstCellSource.Offset(0, 4))
dataSource.Copy Destination:=firstCellDest
Like this the code is essentially "self documenting" and it's easy to see what you are doing. Note you only need to give the first cell of the destination.
像这样，代码本质上是“自我记录”，很容易看到你在做什么。请注意，您只需要提供目的地的第一个单元格。
回答by John Bustos
First offset, then use Range("A1:E1")to reference the 5-column width area:
先偏移，然后使用Range("A1:E1")引用5列宽的区域：
Worksheets("Sheet").Range("namedrange_d").Offset(7,0).Range("A1:E1").Copy Destination:=Worksheets("Sheet1").Range("namedrange")
... That should be all you need
......这应该就是你所需要的
