vba 向下填充公式直到列中的最后一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25788750/
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
Fill formula down till last row in column
提问by MatTtT
I'm trying to draw down the formula that's in cell M3 to the end of the data set.
我正在尝试将单元格 M3 中的公式绘制到数据集的末尾。
I'm using column L as my base to determine the last cell with data. My formula is a concatenation of two cells with a text comma in-between them.
我使用 L 列作为我的基础来确定带有数据的最后一个单元格。我的公式是两个单元格的串联，它们之间有一个文本逗号。
My formula is =G3&","&L3
我的公式是 =G3&","&L3
I want Excel to draw down this formula so
我希望 Excel 绘制出这个公式
Cell M4 would be =G4&","&L4
Cell M5 would be =G5&","&L5 and so on.
单元格 M4 将是 =G4&"、"&L4
单元格 M5 将是 =G5&"、"&L5，依此类推。
My code:
我的代码：
Range("$M").Formula = Range("G3") & (",") & Range("L3")
Dim Lastrow As Long
Application.ScreenUpdating = False
Lastrow = Range("L" & Rows.Count).End(xlUp).Row
Range("M4").FormulaR1C1 = Range("G4") & (",") & Range("L4")
Range("M4").AutoFill Destination:=Range("M4:M" & Lastrow)
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
My output is pulling down the text values from cell M3 all the way down to the end of the data set. I've searched around for several hours trying to look for a fix, but can't find one that is trying to accomplish what I'm going for.
我的输出是将单元格 M3 中的文本值一直下拉到数据集的末尾。我已经搜索了几个小时试图寻找修复，但找不到一个试图完成我想要的。
回答by Siddharth Rout
It's a one liner actually. No need to use .Autofill
它实际上是一个班轮。无需使用.Autofill
Range("M3:M" & LastRow).Formula = "=G3&"",""&L3"
回答by Monthy
For people with a similar question and find this post (like I did); you can do this even without lastrow if your dataset is formatted as a table.
对于有类似问题并找到这篇文章的人（就像我一样）；如果您的数据集格式为表格，则即使没有 lastrow 也可以执行此操作。
Range("tablename[columnname]").Formula = "=G3&"",""&L3"
Making it a true one liner. Hope it helps someone!
使其成为真正的单衬。希望它可以帮助某人！
回答by u3397819
Alternatively, you may use FillDown
或者，您可以使用FillDown
Range("M3") = "=G3&"",""&L3": Range("M3:M" & LastRow).FillDown
回答by SteveG
Wonderful answer! I needed to fill in the empty cells in a column where there were titles in cells that applied to the empty cells below until the next title cell.
精彩的回答！我需要填充一列中的空单元格，其中单元格中的标题适用于下面的空单元格，直到下一个标题单元格。
I used your code above to develop the code that is below my example sheet here. I applied this code as a macro ctl/shft/D to rapidly run down the column copying the titles.
我使用上面的代码开发了位于此处示例表下方的代码。我将此代码用作宏 ctl/shft/D 以快速运行复制标题的列。
--- Example Spreadsheet ------------ Title1 is copied to rows 2 and 3; Title2 is copied to cells below it in rows 5 and 6. After the second run of the Macro the active cell is the Title3 cell.
--- Example Spreadsheet ------------ Title1 被复制到第 2 行和第 3 行；Title2 被复制到它下面的第 5 行和第 6 行的单元格中。在第二次运行宏之后，活动单元格是 Title3 单元格。
' **row** **Column1** **Column2**
' 1 Title1 Data 1 for title 1
' 2 Data 2 for title 1
' 3 Data 3 for title 1
' 4 Title2 Data 1 for title 2
' 5 Data 2 for title 2
' 6 Data 3 for title 2
' 7 Title 3 Data 1 for title 3
----- CopyDown code ----------
----- 复制代码 ----------
Sub CopyDown()
Dim Lastrow As String, FirstRow As String, strtCell As Range
'
' CopyDown Macro
' Copies the current cell to any empty cells below it.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Set strtCell = ActiveCell
FirstRow = strtCell.Address
' Lastrow is address of the *list* of empty cells
Lastrow = Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Address
' MsgBox Lastrow
Range(Lastrow).Formula = strtCell.Formula
Range(Lastrow).End(xlDown).Select
End Sub
