公式自动填充多列，使用 VBA 中的特定单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25621448/
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
Formula Autofill down multiple columns, using specific cells in VBA
提问by Madeleine Thomas
Specific cells: K4 and K8
特定细胞：K4 和 K8
Columns with data needed for formulas: A (values named devm), C (values named slice) and D (values named point)
包含公式所需数据的列：A（名为 devm 的值）、C（名为 slice 的值）和 D（名为 point 的值）
Columns with formulas to go in: E, F and G (want to name these values angle, devmm and height)
带有公式的列：E、F 和 G（想将这些值命名为角度、devmm 和高度）
Formula to go into E1: =(D1 - 1) * $K$8
进入 E1 的公式：=(D1 - 1) * $K$8
Formula to go into F1: =A1 * 1000
进入F1的公式：=A1 * 1000
Formula to go into G1: =(C1 - 1) * $K$4
进入 G1 的公式：=(C1 - 1) * $K$4
^^^These are only with respect to Row 1
^^^这些仅与第 1 行有关
I want my macro to enter these formulas into these Row 1 cells and then autofill down to Last active Row (which I have already sorted the code out for). Relative part(s) of my code is below.
我希望我的宏将这些公式输入到这些第 1 行单元格中，然后自动填充到最后一个活动行（我已经对代码进行了排序）。我的代码的相关部分如下。
K = 1
ender = Tier * increment
last = LastTier * increment
starter = ender - (increment - 1)
If starter = 0 Then
starter = 1
End If
sheetname1 = "Sheet1"
ActiveSheet.Name = sheetname1
ActiveSheet.Range("K2") = TankHeight
ActiveSheet.Range("K3") = LastTier - 1
ActiveSheet.Range("K4").Formula = "=$K/$K"
ActiveSheet.Range("K6").Value = 360
ActiveSheet.Range("K7") = increment
ActiveSheet.Range("K8").Formula = "=$K/$K"
' ********************************************************************
Set Range1 = Range("A1:J65536")
With Range1
Rows(last + 2).Delete
End With
For K = starter To ender
Devm = ActiveSheet.Range("A" & K).Value
Rad = ActiveSheet.Range("B" & K).Value
slice = ActiveSheet.Range("C" & K).Value
point = ActiveSheet.Range("D" & K).Value
' ***Automation settings for Formulas and Autofill down to last***
ActiveSheet.Range("E1").Formula = "=(D1-1)*$K"
ActiveSheet.Range("F1").Formula = "=A1*1000"
ActiveSheet.Range("G1").Formula = "=(C1-1)*$K"
Angle = ActiveSheet.Range("E" & K).Value
Devmm = ActiveSheet.Range("F" & K).Value
height = ActiveSheet.Range("G" & K).Value
K = K + 1
ActiveSheet.Range("C1").Select
Next
回答by Siddharth Rout
No need for a loop. You can enter the formula in all the cells in one go
不需要循环。您可以一次性在所有单元格中输入公式
Range("E1:E" & lastRow).Formula = "=(D1 - 1) * $K"
Range("F1:F" & lastRow).Formula = "=A1 * 1000"
Range("G1:G" & lastRow).Formula = "=(C1 - 1) * $K"
where lastRowis the last row in the column.
lastRow列中的最后一行在哪里。
You can find that using "Error in finding last used cell in VBA".
您可以发现使用“在 VBA 中查找上次使用的单元格时出错”。
回答by Dave
There are a few ways you could approach this, but to do it with minimal changes to your code I would use the R1C1 notation for your formulas and use the reference to k for each row:
有几种方法可以解决这个问题，但要对代码进行最少的更改，我将使用 R1C1 表示法作为公式，并为每一行使用对 k 的引用：
ActiveSheet.Range("E" & k).FormulaR1C1 = "=(RC[-1]-1)*R8C11"
ActiveSheet.Range("F" & k).FormulaR1C1 = "=RC[-5]*1000"
ActiveSheet.Range("G" & k).FormulaR1C1 = "=(RC[-4]-1)*R4C11"
回答by Zig Paul
There is no need to find the last row. Here is a much simpler method:
不需要找到最后一行。这是一个更简单的方法：
Selection.AutoFill Destination:= _
Range(Selection, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1))
This example assumes you are one column to the right.
此示例假定您位于右侧的一列。
Double offset is possible - who knew!!
双偏移是可能的 - 谁知道！！
