vba 创建一个 Excel 宏来跨变量/未知列数复制公式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14326850/
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
Create a Excel Macro to copy formulas down across variable/unknown number of columns?
提问by harvey onethousand
I would like to create a single macro in excel that I can reuse on other sheets to copy the formulas down from from B4:B? down to Row number X.
我想在 excel 中创建一个宏，我可以在其他工作表上重复使用它来从 B4:B 复制公式？直到第 X 行。
I will be pasting various rows of data into Column A of several different worksheets. I will have a button on each sheet that I'd like to label "Copy Formulas". I have written formulas to parse out the text into anywhere from 3 to 250 columns, depending on the sheet. I would like the macro to highlight from B4 to Selection.End(xlToRight) then copy that selection down until the last row of data in column A.
我将把不同的数据行粘贴到几个不同工作表的 A 列中。我将在每张纸上都有一个按钮，我想标记为“复制公式”。我已经编写了公式来将文本解析为 3 到 250 列，具体取决于工作表。我希望宏从 B4 突出显示到 Selection.End(xlToRight) 然后将该选择复制到 A 列中的最后一行数据。
I was thinking of something like this but I keep getting an error on line 6.
我正在考虑这样的事情，但我在第 6 行不断收到错误消息。
Dim strCurrentSheet As String
Dim LastRow As Integer
Dim LastCol As Integer
LastRow = Range("A4").End(xlDown).Row
LastCol = Range("B4").End(xlToRight).Column
Range(Cells(4, 2), Cells(4, LastCol)).AutoFill _
Destination:=Range(Cells(5, 2), Cells(LastRow, LastColumn))
回答by Peter Albert
Instead of a VBA macro to do so, consider using Excel tables (Insert->Table) from the start - it has this functionality more or less build in:
与其使用 VBA 宏，不如从一开始就考虑使用 Excel 表格（插入->表格） - 它或多或少地内置了此功能：
If you enter a formula an empty column in a table (or an adjacent column next to it), Excel will automatically apply the formula to all rows. And if you add/copy data at any point, it will automatically expand the table and therefore also apply the formulas in all columns!
如果您在表格中的空列（或其旁边的相邻列）中输入公式，Excel 将自动将该公式应用于所有行。如果您在任何时候添加/复制数据，它会自动扩展表格，因此也会在所有列中应用公式！
回答by John Bustos
... I didn't test this code too much, but this should at least give you a good starting point and set you on your way:
...我没有过多地测试这段代码，但这至少应该给你一个很好的起点，让你走上正轨：
Sub test()
' Get the last row and column on the sheet - store them in the variables
Dim LastRow As Integer
Dim LastCol As Integer
LastRow = Range("A1").End(xlDown).Row
LastCol = Range("B4").End(xlToRight).Column
' Copy cells B4:B[LastCol] to cells B4:[LastRow][LastCol]
Range(Cells(2, 4), Cells(2, LastCol)).AutoFill _
Destination:=Range(Cells(2, 4), Cells(LastRow, LastCol))
End Sub
Hope it helps
希望能帮助到你
回答by Nat
Dim colABottom As Range
Set colABottom = Range("A1").End(xlDown)
Dim colEnd As Range
Set colEnd = Range("A1").End(xlToRight)
Dim lColStart As Long
lColStart = 2
Dim lColEnd As Long
lColEnd = colEnd.Column
Dim lRowStart As Long
lRowStart = 1
Dim lRowEnd As Long
lRowEnd = colABottom.Row
Dim startRange As Range
Set startRange = Range("B1")
Dim sourceRange As Range
Set sourceRange = Range(startRange, startRange.End(xlToRight))
Dim destinationFillRange As Range
Set destinationFillRange = Range(Cells(lRowStart, lColStart), Cells(lRowEnd, lColEnd))
sourceRange.AutoFill Destination:=destinationFillRange
