vba 如何在VBA的公式中插入变量

How can I insert variable into formula in VBA

提问by lcc

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

采纳答案by user3598756

your actual goal is unclear

你的实际目标不明确

you may want to start form this code

你可能想从这个代码开始

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:

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

• it currently writes in cells "D1:D10"

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

  so if:

  • you want to reference a different column then just change 4to the proper column index

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

• the formulawritten in those cells is:

  =SUM(E1:E15)in D1,

  =SUM(E2:E15)in D2,

  ....

  =SUM(E10:E15)in D10.

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

• 它当前写入单元格“D1：D10”

  因为cells(i, 4)引用了第 4 列（即：列“D”）4 和i行中的单元格，我们在一个循环中，其中i循环从 1 到 10

  因此，如果：

  • 您想引用不同的列，然后更改4为正确的列索引

  • 您想引用不同的行，然后只需更改i为正确的行索引（i+2如果您需要迭代 1 到 10 但从行开始写入，则可能是一些3）

• 在formula写这些细胞是：

  =SUM(E1:E15)在 D1，

  =SUM(E2:E15)在 D2，

  ....

  =SUM(E10:E15)在 D10。

  所以只要改变"=Sum(E" & i & ":E15)"你的实际需求

回答by Wolfie

You're close, trying to use ampersands (&) to concatenate strings.

您很接近，尝试使用与符号 ( &) 连接字符串。

ActiveCell.Offset(0, 2).Formula = "=Sum(E15," & i & ")"

Use the ampersands between strings to merge them, not inside strings.

使用字符串之间的＆符号来合并它们，而不是在字符串内部。