CODEWITHSUNDEEP
vbaexcel
Matthieu
Matthieu

Reputation: 205

Write a formula in an Excel Cell using VBA

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:

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

Upvotes: 20

Views: 159203

Answers (5)

DiGiMac
DiGiMac

Reputation: 51

Treb, Matthieu's problem was caused by using Excel in a non-English language. In many language versions ";" is the correct separator. Even functions are translated (SUM can be SOMMA, SUMME or whatever depending on what language you work in). Excel will generally understand these differences and if a French-created workbook is opened by a Brazilian they will normally not have any problem. But VBA speaks only US English so for those of us working in one (or more) foreign langauges, this can be a headache. You and CharlesB both gave answers that would have been OK for a US user but Mikko understod the REAL problem and gave the correct answer (which was also the correct one for me too - I'm a Brit working in Italy for a German-speaking company).

Upvotes: 5

KnomDeGuerre
KnomDeGuerre

Reputation: 357

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.

Upvotes: 5

James Eichele
James Eichele

Reputation: 119106

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

Upvotes: 5

Mikko
Mikko

Reputation:

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

Upvotes: 21

Treb
Treb

Reputation: 20271

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

Upvotes: -1

Related Questions