Different languages issue when inserting formula from VBA

Solution 1:

VBA is very EN-US-centric. VBA's .Formula and .FormulaR1C1 expect the ROW function. To use regional language function 'flavors' like СТРОКА then the Range.FormulaLocal property or Range.FormulaR1C1Local property should be employed instead.

The same holds true for list separator characters. Use a comma (e.g. ,) to separate the arguments in a function when using .Formula or .FormulaR1C1 regardless of system regional settings. If your system uses a semi-colon (e.g. ;) as the list separator character, this should only be used with .FormulaLocal or .FormulaR1C1Local.

The result on the worksheet will properly reflect the language settings of the Office installation.

myRange.Formula = "=ROW(mySheet!$12:$12)"
myRange.FormulaR1C1 = "=ROW(mySheet!R12)"
myRange.FormulaLocal  = "=СТРОКА(mySheet!$12:$12)"
myRange.FormulaR1C1Local= "=СТРОКА(mySheet!R12)"