Sum dynamic ranges

[RobbertH]

Gurus,

I'm trying to put a sum formula is some cells by running a macro. The formula that has to be put into the cells sums a range that is located in another workbook. The name of this workbook will vary. The workbook is open at the time the code should put the sum formula into the required cells.
How do i put together the vba code?
an example of the formula that has to be writen into the cell is:

"=-SUM([Omzet2011tot_02.xls]Maand!R33C2:R33C25)"

Thanks for any help!

 

[stilesbn]

Here's how I would go about it. I don't know what your code looks like, but hopefully a general idea will help you.

Since the workbook varies the only thing I can think of is that you have the varying workbook open and active when you run the macro and then set it as a variable at the start like this.

set varBook = ActiveWorkbook
set varSheet = activesheet

Now you can reference the workbook and worksheet as needed. Then to get it into the formula you can do something like this

Cells(1,1).FormulaR1C1 = "=-SUM([" & varBook.name & "]" & varSheet.name & "!R33C2:R33C25)"

I hope this is the direction you were looking for.