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?

 

[RoryA]

The Formula property expects a US language and format formula string. Use FormulaLocal instead.

 

[Nelson78]

The Formula property expects a US language and format formula string. Use FormulaLocal instead.

Same error 1004.

Sheets("Sheet10").Cells(ro, col).FormulaLocal = calculate

 

[RoryA]

What are the actual (i.e. verified, not assumed) values of ro, col, and calculate?

 

[Nelson78]

What are the actual (i.e. verified, not assumed) values of ro, col, and calculate?

I've to amend: at first, it didn't work because a typo. Now it's ok.

Just a complication: when I use MATCH, say

CONFRONTA(B5;'Sheet1'!A:A;0)

but I need the sum of more values composing a range (say B5:B8): how can I figure out?

CONFRONTA(B5:B8;'Sheet1'!A:A;0)

seems not working.

 

[RoryA]

What do you expect as a result of that? It will return an array of values, and would need to be entered to 4 cells at once using the FormulaArray property.

 

[Nelson78]

What do you expect as a result of that? It will return an array of values, and would need to be entered to 4 cells at once using the FormulaArray property.

Pardon, I'm going to get some clarification.

I've a range A2:A & lr where lr is not known a priori.
If range, for example, is A2:A5, I need the following formula.

=INDICE(Sheet1!A1:CS41;CONFRONTA(A2;Sheet1!B:B;0);CONFRONTA(C1;Sheet1!5:5;0))+INDICE(Sheet1!A1:CS41;CONFRONTA(A3;Sheet1!B:B;0);CONFRONTA(C1;Sheet1!5:5;0))+INDICE(Sheet1!A1:CS41;CONFRONTA(A4;Sheet1!B:B;0);CONFRONTA(C1;Sheet1!5:5;0))+INDICE(Sheet1!A1:CS41;CONFRONTA(A5;Scritte!B:B;0);CONFRONTA(C1;Scritte!5:5;0))

The question is: can I figure this out with a single formula?

The alternative is: using support cells (no problem about, I know how to built it, but I was wondering if a quicker method exists).

Thank you in advance.

 

[RoryA]

It seems like you are using the wrong approach to me. I think what you need is:

=SUMPRODUCT(SUMIF(Sheet1!B1:B41,A2:A5,INDEX(Sheet1!A1:CS41,0,match(C1,Sheet1!5:5,0))))

whatever the equivalent in Italian is.

 

[Nelson78]

It seems like you are using the wrong approach to me. I think what you need is:

=SUMPRODUCT(SUMIF(Sheet1!B1:B41,A2:A5,INDEX(Sheet1!A1:CS41,0,match(C1,Sheet1!5:5,0))))

whatever the equivalent in Italian is.

Absolutely correct your approach.
But this creates another problem: in some cases, I need to sum the values in the adjoining column on the right of the found one.
This is the reason about the use of SCARTO (alias OFFSET), which I used in the original formula at the beginning, say:

not this
=INDICE(Sheet1!A1:CS41;CONFRONTA(A2;SSheet1!B:B;0);CONFRONTA(C1;Sheet1!5:5;0))

but this
=SCARTO(INDICE(Sheet1!A1:CS41;CONFRONTA(A2;Sheet1!B:B;0);CONFRONTA(C1;Sheet1!5:5;0));0;1)

So, how can I apply OFFSET to
=SUMPRODUCT(SUMIF(Sheet1!B1:B41,A2:A5,INDEX(Sheet1!A1:CS41,0,match(C1,Sheet1!5:5,0))))
?

 

[RoryA]

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