Vba: inserting formula with variables and other sheets in a cell, errr 1004

[Nelson78]

Hello everybody.

I need to write in a cell the following formula.
Consider Excel in italian language:
SCARTO is OFFSET
INDICE is INDEX
CONFRONTA is MATCH

dim calculate as string
calculate = "=SCARTO(INDICE('Sheet1'!" & ccode.Address & ";CONFRONTA('Sheet2'!" & z & ";'Sheet1'!A:A;0);CONFRONTA('Sheet2'!" & ColumnLetter & "4;'Sheet1'!1:1;0));0;1)"
Sheets("Sheet10").Cells(ro, col).Formula = calculate

Error on the last line:

VBA Runtime Error 1004 Application-defined or Object

Any ideas?

 

[Nelson78]

If you’re adding the same column for all rows, just add or subtract from the column match function result as needed.

Fantastic!

Easier then expected:

=MATR.SOMMA.PRODOTTO(SOMMA.SE(Sheet1!B1:B41;A2:A5;INDICE(Sheet1!A1:CS41;0;CONFRONTA(C1;Sheet1!5:5;0)+1)))

Thank's so much.