Sorx Sorx - 1 year ago 60 Question

FormulaR1C1 doesn't work with SUMME

The FormulaR1C1 Method doesn't work as it's supposed to.

objExcel = CreateObject("Excel.Application")
objWorkbook = objExcel.Workbooks.Add
Dim Formula As String
Formula = "=SUMME(Z1S1;Z2S1)"
For i = 1 To 5 Step 1
objWorkbook.Worksheets("Tabelle1").Cells(i, 1).FormulaR1C1 = 5
objWorkbook.Worksheets("Tabelle1").Cells(i, 5).FormulaR1C1 = "Text"
objWorkbook.Worksheets("Tabelle1").Cells(i, 3).FormulaR1C1 = Formula

This should give me 10 in the third column 5 times, but it doesn't. I even tried it with:

Formula = "=SUMME(Z[0]S[-2];Z[1]S[-2])"

but that doesn't work either. The Loop just breaks when coming to the assigment line :( . If I try it with

Formula = "=SUMME(Z1S1,Z2S1)"

It executes completely but it doesn't work for excel because then it says =SUMME('Z1S1';'Z2S1') in the Excel Field

Answer Source

You have to use the Range.FormulaLocal property or Range.FormulaR1C1Local property if you plan to use a string that contains non-EN-US function. VB.Net expects a EN-US formula and will translate that to the regional language of the worksheet.

Formula = "=SUM(A1, B2)"
'or depending on your requiements,
Formula = "=SUM(A1:B2)"
.Formula = Formula

Formula = "=SUM(R1C1, R2C2)"
'or depending on your requiements,
Formula = "=SUM(R1C1:R2C2)"
.FormulaR1C1 = Formula

Formula = "=SUMME(Z1S1;Z2S1))"
'or depending on your requirements,
Formula = "=SUMME(Z1S1:Z2S1)"
.FormulaLocalR1C1 = Formula

Don't confuse xlA1 cell references with xlR1C1 references. R23 would mean Cells(23, 18) in xlA1 and 23:23 in xlR1C1.

Note that using the Range.Formula property or Range.FormulaR1C1 property enforces the use of a comma (EN-US standard) instead of a semicolon as a system list separator.

Range.Formula property
Range.FormulaR1C1 property
Range.FormulaLocal property
Range.FormulaR1C1Local property