Excel VBA：将硬编码列更改为动态范围以自动填充到最后一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21998456/
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: changing hard coded column to dynamic range to autofill to last row
提问by user3348141
Hello from an unexperienced vba user.. I'm having trouble with the code for autofill to the last row when trying to use named ranges for the column. Everything seems to work fine when I use a hard coding for the column, in this case Column CW, and what I need is to replace this column CW with a named range so that the macro will still work when adding or deleting columns in the worksheet.
来自没有经验的 vba 用户的您好.. 我在尝试使用列的命名范围时遇到自动填充到最后一行的代码的问题。当我对列使用硬编码时，一切似乎都正常工作，在这种情况下是列 CW，我需要的是用命名范围替换 CW 列，以便宏在工作表中添加或删除列时仍然可以工作.
I used the following named ranges:
我使用了以下命名范围：
- First_Date: This is the header cell of one of the columns (in this case AP5)
- Second_Row: This is the range of columns I want to copy the formulas from (AP7:CW7)
- Second_Cell: The cell where I want to start to autofill (AP7)
- Last_Column: This is column CW that I want to use in the code. Autofill would be up to this column and down to the last row.
- First_Date：这是其中一列的标题单元格（在本例中为 AP5）
- Second_Row：这是我要从 (AP7:CW7) 复制公式的列范围
- Second_Cell：我想开始自动填充的单元格（AP7）
- Last_Column：这是我想在代码中使用的列 CW。自动填充将向上到这一列，向下到最后一行。
After searching in different threads, I came up with the following code that seems to work fine. How can I change column CW to a named range? Or do I need to change the code?
在不同的线程中搜索后，我想出了以下似乎工作正常的代码。如何将列 CW 更改为命名范围？还是我需要更改代码？
Dim Lr As Integer
Lr = Range("First_Date").End(xlDown).Row 'Searching last row
Rows(Lr).Insert Shift:=xlDown 'Inserting new row
Range("Second_Row").AutoFill Destination:=Range(Range("Second_Cell"), Range("CW" & Lr))
Can anyone assist me here please?
有人可以在这里帮助我吗？
回答by Tuckshop86
This will get the job done :-)
这将完成工作:-)
Sub RangerFiller()
Sub RangerFiller()
'The Cell that holds the formula B1
'保存公式B1的单元格
OriginalFormula = Cells(1, 2).Formula
原始公式 = Cells(1, 2).公式
'copies formula down to the last column next to Bbut use can use another column as 'a counting column....the column that hold the last value
'将公式复制到B旁边的最后一列，但可以使用另一列作为 '一个计数列......保存最后一个值的列
Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = OriginalFormula
Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = OriginalFormula
End Sub
结束子
回答by user3348141
Someone gave me the solution:
有人给了我解决方案：
Change
改变
Range("CW" & Lr)
To
到
Cells(Lr, Range("Last_Column").Column)
回答by val
I faced a similar problem because I don't want to hard code the cell reference. I found this solution below to be useful, by using "& ______ &" to replace the cell number that can be calculated using input box or formula.
我遇到了类似的问题，因为我不想对单元格引用进行硬编码。我发现下面的这个解决方案很有用，通过使用“& ______ &”来替换可以使用输入框或公式计算的单元格编号。
Eg. cell1 = last row of column A
例如。单元格 1 = A 列的最后一行
Range("CW " & cell1 &" :CW & Lr), where cell1 = any number that can be added via input box/formula.
Range("CW" & cell1 &" :CW & Lr)，其中 cell1 = 可以通过输入框/公式添加的任何数字。
Hope this helps!
希望这可以帮助！
