Write a formula in an Excel Cell using VBA

Question

Hello,

I'm trying to use VBA to write a formula into a cell in Excel. My problem is that when I use a semicolon (;) in my formula I get an error 1004.

My macro is the following :

---

Sub Jours_ouvres()
    Dim Feuille_Document As String
    Feuille_Document = "DOCUMENT"

    Application.Worksheets(Feuille_Document).Range("F2").Formula = "=SUM(D2;E2)"


End Sub

---

Thanks in advance,

Matthieu

Answer 1

The correct character to use in this case is a full colon (:), not a semicolon (;).

Answer 2

I don't know why, but if you use

(...)Formula = "=SUM(D2,E2)"

(',' instead of ';'), it works.

If you step through your sub in the VB script editor (F8), you can add Range("F2").Formula to the watch window and see what the formular looks like from a VB point of view. It seems that the formular shown in Excel itself is sometimes different from the formular that VB sees...

Answer 3

Thanks Treb, it's Working.

Answer 4

You can try using FormulaLocal property instead of Formula. Then the semicolon should work.

Answer 5

The correct character (comma or colon) depends on the purpose.

Comma (,) will sum only the two cells in question.

Colon (:) will sum all the cells within the range with corners defined by those two cells.