Excel VBA 偏移函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45597356/
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 offset function
提问by Tox
I have an Excel file with information in column A and column B. Since these columns could vary in the number of rows I would like to use the function offsetso that I could print the formula in one time as an array rather than looping over the formula per cell (the dataset contains almost 1 million datapoints).
我有一个 Excel 文件，其中包含 A 列和 B 列中的信息。由于这些列的行数可能会有所不同，因此我想使用函数偏移量，以便我可以一次性将公式打印为数组，而不是循环遍历每个单元格的公式（数据集包含近 100 万个数据点）。
My code is actually working the way I want it to be I only can't figure out how to print the code in Range(D1:D5). The outcome is now printed in Range(D1:H1). Anybody familiar how to use this offset within a for statement?
我的代码实际上按照我想要的方式工作，我只是不知道如何在 Range(D1:D5) 中打印代码。结果现在打印在 Range(D1:H1) 中。有人熟悉如何在 for 语句中使用此偏移量吗？
Sub checkOffset()
Dim example As Range
Dim sht As Worksheet
Dim LastRow As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Set example = Range("A1:A1")
For i = 1 To LastRow
example.Offset(0, i + 2).Formula = "=SUM(A" & i & ":B" & i & ")"
Next i
End Sub
回答by Shai Rado
Using the Offset(Row, Column), you want to offset with the increment of row (i -1), and 3 columns to the right (from column "A" to column "D")
使用Offset(Row, Column)，您希望以行 ( i -1)的增量和向右 3 列（从“A”列到“D”列）的增量进行偏移
Try the modified code below:
试试下面修改后的代码：
Set example = Range("A1")
For i = 1 To LastRow
example.Offset(i - 1, 3).Formula = "=SUM(A" & i & ":B" & i & ")"
Next i
回答by Ron Rosenfeld
One way of outputting the formula in one step, without looping, to the entire range, is to use the R1C1 notation:
将公式一步输出到整个范围而不循环的一种方法是使用 R1C1 表示法：
Edit: Code modified to properly qualify worksheet references
编辑：修改代码以正确限定工作表引用
Option Explicit
Sub checkOffset()
Dim example As Range
Dim sht As Worksheet
Dim LastRow As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
With sht
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set example = .Range(.Cells(1, 1), .Cells(LastRow, 1))
End With
example.Offset(columnoffset:=3).FormulaR1C1 = "=sum(rc[-3],rc[-2])"
End Sub
回答by ale10ander
You don't need to use VBA for this. Simply type =sum(A1:B1) in cell D1 and then fill it down.
If you're going to use VBA anyway, use this:
Sub checkOffset() Dim example As Range Dim sht As Worksheet Dim LastRow As Long Set sht = ThisWorkbook.Worksheets("Sheet1") LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row Set example = Range("A1:A1") For i = 1 To LastRow example.Offset(i - 1, 3).Formula = "=SUM(A" & i & ":B" & i & ")" Next i End Sub
您不需要为此使用 VBA。只需在单元格 D1 中键入 =sum(A1:B1) 然后向下填充即可。
如果您无论如何都要使用 VBA，请使用以下命令：
Sub checkOffset() Dim example As Range Dim sht As Worksheet Dim LastRow As Long Set sht = ThisWorkbook.Worksheets("Sheet1") LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row Set example = Range("A1:A1") For i = 1 To LastRow example.Offset(i - 1, 3).Formula = "=SUM(A" & i & ":B" & i & ")" Next i End Sub
The way offsetworks is with row offset, column offset. You want the column to always be fixed at 3 to the right.
offset工作方式是行偏移，列偏移。您希望该列始终固定在右侧 3 处。
