Sorx - 1 year ago 84

Vb.net Question

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

Next

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(Z1S1, Z2S1)"
'or depending on your requiements,
Formula = "=SUM(Z1S1:Z2S1)"
.Formula = Formula
Formula = "=SUMME(Z1S1;Z2S1))"
'or depending on your requiements,
Formula = "=SUMME(Z1S1:Z2S1)"
.FormulaLocal = Formula
Formula = "=SUMME(R1C26;R2C26))"
'or depending on your requiements,
Formula = "=SUMME(R1C26;R2C26)"
.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.