XL Mangling My FormulaR1C1

[DesertDude]

I am building a string in code that I then assign to the FormulaR1C1 propery of a cell. The string is a formula that sums the values in a cell across spreadsheets and looks something like this:

=SUM('Sheet1'!Z1,'Sheet2'!Z1...)

The problem is that when I assign it to the FormulaR1C1 property of a cell, that cell shows a #NAME? error and when I check its contents I see that XL has mangled the apostrophes.

Here is what the string looks like as it is assigned to the FormulaR1C1 property, which is what it's supposed to look like:

=SUM('SHEET1'!Z1,'SHEET2'!Z1,'SHEET3'!Z1,'SHEET4'!Z1,'SHEET5'!Z1)

Here is what it looks like after XL mangles it. Notice the apostrophe's are all crazy. Sometimes they properly surround the sheet names as they should and other times they don't, and they always seem to surround the address:

=SUM(SHEET1!'Z1',SHEET2!'Z1',SHEET3!'Z1','SHEET4'!'Z1','SHEET5'!'Z1')


To build the formula, I first loop through the cells on Row 1 of a sheet called UAD2. If the cell contains a formula, then that cell will be changed to hold the new formula I am building. The next step is to loop through the collection of WorkSheets to find all the sheet names that need to be included in the formula. Here is the code that creates the formula:

Please Login or Register  to view this content.

Does anyone know what's going on?? Thanks!

[JBeaucaire]

The proper syntax to sum the same cell across a series of adjacent worksheets is:

=SUM(Sheet1:Sheet5!Z1)

Knowing you only need the first and last sheet name, does that help?

[DesertDude]

Thanks, JBeaucaire. Unfortunately, it doesn't help much. Though I am using generic names in my posted example, the sheets often have spaces in their names, so I have to surround the names with apostrophes. When I implement your (better) approach, I still have to put apostrophe's around the names and I am still getting the same mangling by XL.

[JBeaucaire]

Hmm, I don't follow your macro, but I can conceive of a VBA method of inserting a 3D sum into a cell using 2-word sheet names.

First sheet: "Sheet One"
Last sheet: "Sheet Five"

Please Login or Register  to view this content.

End result would be:
=SUM('Sheet One:Sheet Five'!B10)

[royUK]

Take a look at the summary sheet.It contains a method that doesn't need VBA & is dynamic,you can add or remove sheets

http://excel-it.com/workbook_downloads.htm

[DesertDude]

Which is essentially what I'm doing. Have you tried this in Excel 2007? Because when I do it I get apostrophes around the cell address (B10 in your example) which causes a #NAME? error. This has been my problem here all along.

[DesertDude]

Sorry, RoyUK. The Summary sheet to which you directed me in your link is doing pretty much what I'm doing in terms of assembling the formula. Again: my problem is that when I put a perfectly correct formula string into the FormulaR1C1 property, Excel (2007) insists on putting apostrophes around the cell address portion of the formula, which causes a #NAME? error. This is what I am trying to get addressed here. Thanks, again.

[DesertDude]

OK, I finally solved this. Instead of the .FormulaR1C1 property, I'm assigning the string containing the formula to the .Formula property (which I did not know existed) and this works beautifully. Thanks all for attempting to help!

[romperstomper]

Unlike previous versions, Excel 2007 actually requires you to use the R1C1 reference style if you use the FormulaR1C1 property. Since you weren't, it treated your addresses as text.

[DesertDude]

Yes, but the weird thing about that is that I did switch to a R1C1 style and got an Application-defined or object-defined error when I attempted to assign it to the .FormulaR1C1 property! I then found a switch in Excel Options/Formulas called R1C1 reference style. This is a checkbox. I checked it and still got the same error!

[romperstomper]

The most likely cause of that error is that your formula string was incorrect.

[DesertDude]

Here is the formula string:

"=SUM('My First Sheet:My Last Sheet'!R1C26"

There is a sheet called My First Sheet and another called My Last Sheet, there are two sheets between them, and each has a value in Z1 (R1C26).

[romperstomper]

You are missing a closing parenthesis:
"=SUM('My First Sheet:My Last Sheet'!R1C26)"

[DesertDude]

Sorry, that was a typo as I entered it here. The closing parenthesis exists in the code.

[romperstomper]

Then it should work assuming:
1. the sheet names are correct,
2. the sheet is not protected.

[DesertDude]

The sheet names are correct (they are actually arrived at through code (see code above)). The sheets are not protected. And it doesn't work.

[romperstomper]

Without seeing the actual code, I can't really assist. The reasons I gave earlier are the only ones I can think of and it's almost always something wrong with the formula string. (obviously in your original posted version, it's your use of A1 references)

[DesertDude]

Of course. No worries. Sometimes it's just not worth banging your head on a wall. When there is another way out, sometimes it's best to just take it and be done with it. Some day I may come to understand what I am doing wrong with the FormulaR1C1, but right now I'm just happy to have a solution. Thanks, though. I very much appreciate your help.

[romperstomper]

What you can do is enter the formula manually in the cell (In usual format), then compare the cell's FormulaR1C1 property with the formula string you are creating. They're almost certainly different in some way...