Using variables in VBA formula functions

[eric_deltour]

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:

Dim Result As Range
         Dim i As Integer
         i = 0
         While i <= 16
             Set Result = Sheets("Report per cost center").Range("E" & i)
             With Result
                .Formula = "=SUMPRODUCT(--('D:\Personal Costs\2013\2. Database\[Personal Costs - 2013-V10.xlsx]Actual 2013'!$A$5:$A$52=A"&i&"),'D:\Personal Costs\2013\2. Database\[Personal Costs - 2013-V10.xlsx]Actual 2013'!$L$5:$L$52)"
                .Value = .Value
             End With
             i = i + 1
         Wend

I hope I am clear enough for your understanding.

Thanks a lot in advance for your support.

Regards,

Eric

[patel45]

a very little mistake, missing spaces

A" & i & "),'D:\Persona......

[alansidman]

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

Posting code between [CODE] [/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.)

[eric_deltour]

Thank you very much Patel45 for your great reactivity, it works perfectly!

@alansidman : Understood, I will next time.