Search code examples
excelvbaexcel-formulanamed-ranges

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

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?

Solution

  • Replace:

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

    with:

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