公式自动填充多列，使用 VBA 中的特定单元格 [英] Formula Autofill down multiple columns, using specific cells in VBA

问题描述

特定细胞: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 中的特定单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！