How to avoid @ character in Excel VBA when pasting a formula to a cell

Question

When I try to make equal a cell with a formula containing a named range MyRange, the result will contain an unnecessary @ character.

VBA code that I try to apply:

Sub InsertFormula()

Range("D1") = "=SUM(SUMIFS(A:A,B:B,MyRange))"

End Sub

The result in the D1 cell will be =SUM(SUMIFS(A:A,B:B,@MyRange)) and the @ character ruins all the formula that works fine by writing it by hand without the @ in the cell.

How Could I avoid character @ in my result? Can you somebody help me in this matter?

Side note: try to fully qualify your range references, e.g. via `Sheet1.Range("D1") ...`. — T.M., Nov 07 '20 at 18:01

score 3 · Answer 1 · answered Nov 07 '20 at 17:59

3

You can avoid insertion of the so called implicit intersection operator using .Formula2:

Range("D1").Formula2 = "=SUM(SUMIFS(A:A,B:B,MyRange))"

Related links

answered Nov 07 '20 at 17:59

T.M.

9,436
3
33
57

1

That's right! Nice link too =). Though I think it's justified to have marked it as a duplicate for now. – JvdV Nov 07 '20 at 18:10
Agree with comment; btw nice link indeed. – T.M. Nov 07 '20 at 18:13

score 1 · Accepted Answer · answered Nov 07 '20 at 17:59

1

Replace:

Range("D1") = "=SUM(SUMIFS(A:A,B:B,MyRange))"

with:

Range("D1").Formula2 = "=SUM(SUMIFS(A:A,B:B,MyRange))"

answered Nov 07 '20 at 17:59

Gary's Student

95,722
10
59
99

How to avoid @ character in Excel VBA when pasting a formula to a cell

2 Answers2