如何在VBA中将变量插入公式 [英] How can I insert variable into formula in VBA

问题描述

任何人都可以解决这个问题吗?

Can anyone solve this?

Sub test

Dim i as integer

For I = 1 to 10
   ActiveCell.Offset(0, 2).Formula = "=Sum(E15,&i&)"
Next I

End Sub

推荐答案

您的实际目标尚不清楚

您可能想从此代码开始

Sub test()
    Dim i As Integer

    For i = 1 To 10
       cells(i, 4).Formula = "=Sum(E" & i & ":E15)"
    Next
End Sub

并根据您的需要进行调整，知道:

and adjust it to your needs, knowing that:

• 它当前写入单元格"D1:D10"

• it currently writes in cells "D1:D10"

因为cells(i, 4)引用了第4列(即"D"列)第4列和i行中的单元格，所以我们处于一个循环中，其中i循环通过1到10

since cells(i, 4) references a cell in 4th column (i.e.: column "D") 4 and i row, and we're inside a loop where i is looping through 1 to 10

所以，如果:

• 您要引用其他列，然后只需将4更改为适当的列索引

您要引用其他行，然后只需将i更改为适当的行索引(如果您需要迭代1到10但从第3行开始写入，则可能为i+2)

you want to reference a different row then just change i to the proper row index (may be some i+2 if you need to iterate through 1 to 10 but start writing from row 3)

formula是:

=SUM(E1:E15)，

=SUM(E2:E15)，

....

=SUM(E10:E15).

所以只需将"=Sum(E" & i & ":E15)"更改为您的实际需求

so just change "=Sum(E" & i & ":E15)" to your actual needs

这篇关于如何在VBA中将变量插入公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！