问题描述
特定细胞:K4 和 K8
Specific cells: K4 and K8
包含公式所需数据的列:A(名为 devm 的值)、C(名为切片的值)和 D(名为点的值)
Columns with data needed for formulas: A (values named devm), C (values named slice) and D (values named point)
要输入公式的列:E、F 和 G(想将这些值命名为角度、devmm 和高度)
Columns with formulas to go in: E, F and G (want to name these values angle, devmm and height)
进入E1的公式:=(D1 - 1) * $K$8
Formula to go into E1: =(D1 - 1) * $K$8
进入F1的公式:=A1 * 1000
Formula to go into F1: =A1 * 1000
进入G1的公式:=(C1 - 1) * $K$4
Formula to go into G1: =(C1 - 1) * $K$4
^^^这些仅针对第 1 行
^^^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.
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$2/$K$3"
ActiveSheet.Range("K6").Value = 360
ActiveSheet.Range("K7") = increment
ActiveSheet.Range("K8").Formula = "=$K$6/$K$7"
' ********************************************************************
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$8"
ActiveSheet.Range("F1").Formula = "=A1*1000"
ActiveSheet.Range("G1").Formula = "=(C1-1)*$K$4"
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
推荐答案
不需要循环.可以一次性在所有单元格中输入公式
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$8"
Range("F1:F" & lastRow).Formula = "=A1 * 1000"
Range("G1:G" & lastRow).Formula = "=(C1 - 1) * $K$4"
其中 lastRow
是列中的最后一行.
where lastRow
is the last row in the column.
您可以发现使用在 VBA 中查找最后使用的单元格时出错".
这篇关于公式自动填充多列,使用 VBA 中的特定单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!