How can I insert variable into formula in VBA

Question

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

you need to take the `i` out of the `"`. But what are you trying to `Sum` ? — Shai Rado, Feb 28 '17 at 08:12

score 10 · Accepted Answer · answered Feb 28 '17 at 08:28

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:

Actually I did try on this "=Sum(E" & i & ":E15)" but I forgot to leave a space between the ampersand,& and "i" That's the reason I couldn't have this code work> — gonzalloe, Feb 28 '17 at 08:48
fine. then you may want to mark answer as accepted by clicking on the check mark beside the answer to toggle it from greyed out to filled in. thank you! — user3598756, Mar 15 '17 at 08:09

score 7 · Answer 2 · answered Feb 28 '17 at 08:11

7

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.

answered Feb 28 '17 at 08:11

Wolfie

1

What is the purpose of the formula `=SUM(E15,1)` ? – Shai Rado Feb 28 '17 at 08:21
1

Thanks, if it's the accepted answer then please mark it so... @ShaiRado, I agree it's unclear what the OP is trying to achieve, but this is the solution to their specific problem :) – Wolfie Feb 28 '17 at 10:29

2 Answers2