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

回答1:

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 i row, and we're inside a loop where i is looping through 1 to 10

  so if:

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

  • 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)

• the formula written 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

回答2:

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.