Excel VBA 用 .Cells 定义 .Range
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23893062/
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
Excel VBA defining a .Range with .Cells
提问by Neat Machine
I'm running into a problem trying to select/copy certain ranges within my worksheet. I've narrowed it down as best as I can.
我在尝试选择/复制工作表中的某些范围时遇到问题。我已经尽可能地缩小了范围。
This works fine:
这工作正常：
dataSheet.Cells(dRow, dataSheet.Range("IO_MODULES").Column).Select
This does not:
这不会：
dataSheet.Range(Cells(dRow, dataSheet.Range("IO_MODULES").Column), Cells(dRow, dataSheet.Range("IO_MODULES").Column)).Select
This is what I'm trying to do:
这就是我想要做的：
dataSheet.Range(Cells(dRow, dataSheet.Range("IO_MODULES").Column), Cells(dRow, dataSheet.Range("MODULE_END").Column)).Copy Destination:= _
dataSheet.Range(Cells(dataSheet.Range("MODULE_1").Row + i - 1, dataSheet.Range("D_COUNT").Column + 1), Cells(dataSheet.Range("MODULE_1").Row + i - 1, dataSheet.Range("DATA_COL_END").Column))
I seem to be misunderstanding the relationship between Worksheet.Cells and Worksheet.Range. What I need to do is be able to select a range of cells based on the row and column numbers of cells that I've named.
我似乎误解了 Worksheet.Cells 和 Worksheet.Range 之间的关系。我需要做的是能够根据我命名的单元格的行号和列号选择一系列单元格。
回答by Dan Wagner
UNTESTEDtry this to copy from one Rangeto another:
UNTESTED试试这个从一个复制Range到另一个：
'...
Dim Source As Range, Dest As Range
With dataSheet
Set Source = .Range(.Cells(dRow, .Range("IO_MODULES").Column), _
.Cells(dRow, .Range("MODULE_END").Column))
Set Dest = .Range(.Cells(.Range("MODULE_1").Row + i - 1, .Range("D_COUNT").Column + 1), _
.Cells(.Range("MODULE_1").Row + i - 1, .Range("DATA_COL_END").Column))
Source.Copy Dest
End With
- The
With...End Withis in place to allow you to operate many times on a single object (in this case,dataSheet) without calling it explicitly every time. Every line of code you write is a line that must be maintained and potentially debugged, so when you can be more concise without losing readability you should do so. (More aboutWith...End Withstatements here: http://msdn.microsoft.com/en-us/library/office/gg264723(v=office.15).aspx) - By naming the
Rangesyou can use the super-friendlyRange.Copymethod, outlined here: http://msdn.microsoft.com/en-us/library/office/ff837760(v=office.15).aspx. This enables you to bypass a.Selector.Activate, which in my experience is a major source of run-time errors.
- 该
With...End With在的地方，让您在单个对象进行操作很多次（在这种情况下，dataSheet不调用它明确每次）。您编写的每一行代码都是必须维护和潜在调试的行，因此当您可以更简洁而不失去可读性时，您应该这样做。（更多关于With...End With这里的声明：http: //msdn.microsoft.com/en-us/library/office/gg264723(v=office.15) .aspx） - 通过命名
Ranges您可以使用超级友好的Range.Copy方法，概述如下：http: //msdn.microsoft.com/en-us/library/office/ff837760(v=office.15).aspx。这使您可以绕过 a.Selector.Activate，根据我的经验，这是运行时错误的主要来源。
