Sumproduct with Sheet as variable

[zechy]

Hi,
I am trying to use a R1C1 Sumproduct formula with a variable sheet, but I got stuck in this problem. I try to use the string "str" as the variable, yet Excel responds with run-time error 1004.
Any hints,please?

Here the code:

Please Login or Register  to view this content.

Thank you very much!

[:) Sixthsense :)]

Try this...

Please Login or Register  to view this content.

[Special-K]

When you refer to str you are talking to VBA so the str reference should be outside double quotes

Try

Please Login or Register  to view this content.

[zechy]

Thank you for quick reply, Sixthsense and Special-K.
However, the proposed solution doesn't work. It stills reports error when it comes to the line with Sumproduct...

[meabrams]

what is the formula suppose to look like when it is in the cell (after the macro would have entered it)

[zechy]

Hi meabrams, thanks for response.

Please Login or Register  to view this content.

420003(BayerischeMotorenwerke) is the name of a sheet and it should vary depending on the string 'str'.

[xlnitwit]

Hi,

You have one too many closing brackets in your formula

Please Login or Register  to view this content.

[zechy]

Hi xlnitwit,
thanks for your help, that was it!