Varying a Formula in an Excel Spreadsheet

[Ardvaark the Third via OfficeKB.com]

I have been trying to insert formula into the cells of an Excel spreadsheet
that sums the contents of cells from a number of other Excel spreadsheets,
within the same workbook. I would like to extend this to apply to other
speadsheets were the number of spreadsheets within the formula varies.
i.e. Spreadsheet 'sheet1' might source its information from 2 spreadsheets,
whilst spreadsheet 'sheet2' might want to source its infromation from 3
spreadsheets. I also set up a numebr of for_next loops to control the row
and column of the cells which require to be populated and information
sourced from.

I tried to define a string variable 'eqn' which I hoped could be placed in
the formula in one line of code such that this could be applied to either
of the spreadsheets. The string variable could then be altered to reflect
the number of source spreadsheets required.

I tried the following:

For Each sheet In Array ("sheet1","sheet2")
For Each column In Array ("A","B","C")
For Each row In Array ("1","2","3")

If sheet = "sheet1" then eqn = "'sheet10'!" & column & row &
",'sheet11'!" & column & row &"

If sheet = "sheet2" then eqn = "'sheet10'!" & column & row &
",'sheet11'!" & column & row & "'sheet12'!" & column & row"


sheet.Cells("row","column").Formula = "=SUM(" & eqn &")"

Next
Next
Next

The intention was to insert a formula in the relevant cells of 'sheet1', in
the example, which looked something like this:

"=SUM('sheet10'!" & column & row & ",'sheet11'!" & column & row & ")"

Excel dosen't seem to recognise the use of string variables used in this
way. Could anyone suggest an alternatiive or suggest where I might be
going wrong.

 

[Tom Ogilvy]

You just had some typos and syntax errors. this worked fine for me:

Sub ABC()
For Each sheet In Array("sheet1", "sheet2")
For Each Column In Array("A", "B", "C")
For Each Row In Array("1", "2", "3")

If sheet = "sheet1" Then eqn = _
"'sheet10'!" & Column & Row & ",'sheet11'!" & _
Column & Row & ""

If sheet = "sheet2" Then eqn = _
"'sheet10'!" & Column & Row & ",'sheet11'!" & _
Column & Row & ",'sheet12'!" & Column & Row & ""


Worksheets(sheet).Cells(Row, Column) _
.Formula = "=SUM(" & eqn & ")"

Next
Next
Next

End Sub