Using variables in VBA formula functions

Dear all,

As a beginner in VBA, I'm pleased to write my first post.
Here is my problem:
I want to use excell functions in VBA to retrieve data from a closed workbook.
My formula uses SumProduct function (because I understood that SumIf works only with an open workbook) and I want to create a loop to fulfill directly all the cells (from 0 to 16 of the column A) with the result of this same formula.
But when I use the variable "i" (indicating the row) into the SumProduct function using the quote (A"&i&"), I have a compile error "Syntax error".
Sorry it's probably basic, but as a beginner, I already spent hours on it without finding any solution...

This formula works without any problem when used in a cell of Excell, so I used perhaps the formula in the wrong way.

Please find my code below:

Please Login or Register  to view this content.

I hope I am clear enough for your understanding.

Thanks a lot in advance for your support.

Regards,

Eric

a very little mistake, missing spaces

Please Login or Register  to view this content.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(I have added them today. Please adhere to our rules in the future.)