Write a formula in an Excel Cell using VBA

Question

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

User · Answer

The correct character to use in this case is a full colon      not a semicolon

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The correct character to use in this case is a full colon      not a semicolon

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The correct character to use in this case is a full colon      not a semicolon

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The correct character to use in this case is a full colon      not a semicolon

[vba] Write a formula in an Excel Cell using VBA

Examples related to vba

Examples related to excel