vba 循环粘贴公式，直到范围内的下一个单元格为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9238125/
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
Loop paste formula until next cell in range is empty
提问by user1203382
I am trying to paste a formula next to range of cells, but only the one's that contains a value, the script must loop until the next cell in the range is empty. For instance Sheet 1 Column Acontains date until row 12, then I would like to paste a formula in column D2:D12Regards
我试图在单元格范围旁边粘贴一个公式，但只有包含值的那个，脚本必须循环直到该范围内的下一个单元格为空。例如，工作表 1 列A包含直到第 12 行的日期，然后我想在列D2:D12问候中粘贴一个公式
回答by Siddharth Rout
Like this?
像这样？
Option Explicit
Sub Sample()
Dim lastRow As Long, i As Long
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
With ws
For i = 1 To lastRow
If Len(Trim(.Range("A" & i).Value)) <> 0 Then _
.Range("D" & i).Formula = "YOUR FORMULA"
Next i
End With
End Sub
回答by brettdj
As you are looking down to the first blank cell then you can avoid a loop and use
当您向下看第一个空白单元格时，您可以避免循环并使用
The code includes a test to make sure that the code doesn't proceed if all of column A is blank - ie if the range from A1 down extends to the bottom of the sheet and A1 is blank
该代码包括一个测试，以确保如果 A 列的所有内容都为空白，则代码不会继续执行 - 即，如果从 A1 向下的范围延伸到工作表的底部并且 A1 为空白
This code adds a sample formula linking each cell in column D to the respective row in column B
此代码添加了一个示例公式，将 D 列中的每个单元格链接到 B 列中的相应行
Sub FillData()
Dim rng1 As Range
Set rng1 = Range([a1], [a1].End(xlDown))
If Not (rng1.Rows.Count = Rows.Count And Len([a1].Value) = 0) Then rng1.Offset(0, 3).FormulaR1C1 = "=RC2"
End Sub
回答by Jerry Beaucaire
I like Sid's beginning, but once you have the range of rows, you can insert the formula into column D all at once, without looping, several ways, here's one:
我喜欢 Sid 的开头，但是一旦你有了行的范围，你就可以一次把公式插入 D 列，不用循环，有几种方法，这里是一种：
Option Explicit
Sub AddFormula()
Dim LR As Long
LR = Range("A" & Row.Count).End(xlUp).Row
Range("D2:D12").Formula = "=A2 + 7" 'just an example of a formula
End Sub
回答by Reafidy
Try this:
尝试这个：
Range("A:A").SpecialCells(2).Areas(1).Offset(, 3).Formula = "MyFormula"
回答by Kyle
This is a simple solution that is built into Excel, as long as you don't want to copy to the first blank, jump over the blank, then continue copying:
这是Excel内置的一个简单的解决方案，只要你不想复制到第一个空白处，跳过空白，然后继续复制：
Enter the formula in the first cell of your range, and as long as it is in the column directly to the right or left of your range of filled cells, simply double-click the black box handler in the bottom right-hand corner of the cell. That will automatically copy your formula down to the last non-empty cell of the range.
在范围的第一个单元格中输入公式，只要它在填充单元格范围右侧或左侧的列中，只需双击位于右下角的黑框处理程序细胞。这将自动将您的公式复制到该范围的最后一个非空单元格。
