Search code examples
excelvbaruntime-error

Inserting simply SUM in a cell formula results in Run-time error 1004

While trying to set some data from one spreadsheet to another in the same excel file it gives a Run-time error 1004: Application-defined or object-defined error. By debugging the code stops to work below when I'm trying to assign to that cell s.

Worksheets("Lista Sc").Select
    With Sheets("Lista Sc")
    
    .Range(Cells(riga, 3), Cells(riga + n, 3)).MergeCells = True
    .Range(Cells(riga, 5), Cells(riga + n, 5)).MergeCells = True
    .Range(Cells(riga, 21), Cells(riga + n, 21)).MergeCells = True
    s = "=SOMMA(S" + CStr(riga) + ";T" + CStr(riga + 1) + ":T" + CStr(riga + n) + ")-L" + CStr(riga)
    .Cells(riga, 21).FormulaLocal = s

It's the first time I'm trying to face problems with excel macro, so I have no idea on what's going on.

Solution

  • I suggest you use English with the Formula property, which will work regardless of the user's language settings:

    .Cells(riga, 21).Formula = "=SUM(S" + CStr(riga) + ",T" + CStr(riga + 1) + ":T" + CStr(riga + n) + ")-L" + CStr(riga)