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
– Shai Rado, Commented Feb 28, 2017 at 8:12

user3598756 · Accepted Answer · 2017-02-28 08:28:58Z

11

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

answered Feb 28, 2017 at 8:28

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gonzalloe Over a year ago

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

user3598756 Over a year ago

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!

Wolfie · Accepted Answer · 2017-02-28 08:11:39Z

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, 2017 at 8:11

Wolfie

30.7k7 gold badges30 silver badges60 bronze badges

2 Comments

Shai Rado Over a year ago

What is the purpose of the formula =SUM(E15,1) ?

Wolfie Over a year ago

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

Collectives™ on Stack Overflow

How can I insert variable into formula in VBA

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related