My current project uses 6 tables. 4 tables contain source data and 2 are used to display data combined from the source tables. The four source tables have the same construction. The 2 "combine" tables have the same construction plus addtional columns containing formulae used to order the data. These formulae are entered in the table manually at design time (by adding 1 row and entering the formulae then deleting the row. The main procedure ic Sub Combine in Module 1. It clears all data from the "combine" tables; copies and pastes one of the source tables; compares values in the combined table to values a second source table and decides on a new combined value; then copies and pastes any rows in the 2nd source table which were not involved in the comparison. This last step disrupts the formulae in the sort columns which translate from summing cells in one row of the table to summing the same columns but now includes a block of rows below the insertion point. NOTE if the row is added at the end of the table the formulae above the insertion point are not changed.
I have repeated this action manually Home:Insert:Insert Table Rows Above and get the same formula disruption.
I have tried to alter the syntax of the SUM formulae I'm using [typically SUM(M9:T9) where 9 is the sheet row] so that I use table rows instead of sheet rows but can't find any way of doing that. The nearest I can get is the sample in cell A1 of sheet Eclectic which reproduces the type of result I get in my combine table.


My work around has been to build the tables then overwrite the formulae cells with the correct data. That is currently in a YesNo If in Sub Combine the for test purposes. This works well but is not neat. Any ideas.

Attached Files

Combined Eclectic rev 6 plus ranges.xlsm (362.5 KB, 10 views)

In Y9, try using a formula like:
=SUM((OFFSET(M$8,ROW()-8,,,9)))
Copy across to AC9 and adjust the final '9' to match the 'Back' value in each of the rows above (a more complicated formula could eliminate even that). Then copy down as far as needed.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Yes that works. I presume because your formula uses sheet references rather then table references.
I started using tables because they seemed to provide benefits over ranges such as auto formula change for columns, column sorting and such. I therefore have always tried to make my code use "table cells" as opposed to "sheet cells" which makes the code independent of any sheet position changes.
I've attempted to adapt your formula to reflect this policy but as soon as I try to use a table column reference [column name] the problem I descibed to you comes back.
One of the issues I don't understand is why a Range.Copy...Range.Paste does not cause formula curruption but row.select...listRows.Add...Paste does.
Both forms of data transfer work as predicted but for the formula changes. Do you think this is an Excel bug or am I missing something.

Actually, it doesn't use either.
I'm not sure what you mean when you refer to "table cells", named ranges, perhaps?
Without knowing which particular sub is causing problems, I can't say. I don't propose to go through all your code (you have 10 modules and 8 worksheets!) and test all the code in each one - especially when I don't know what you regard as the correct or corrupt output in each case.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Can't say I blame you for that. Instead I have set out to present the problem in a very simple fashion, which I should have done in the first place. The attached macroless worksheet demonstrates the problem I have encountered. It only occurs when adding the first line to an "empty" table but of course the corrupt formula persists for subsequent line additions. It only occurs with the A1:A2 format.

Attached Files

Problem.xlsx (13.1 KB, 10 views)

You say "Insert Below can't be used because it inserts a line without formulae". Why is that an issue? Why can you not either: (a) copy the formulae from the row above; or (b) insert the formulae programmatically?

As for the changing formula in F2, you could replace that with:
=SUM(OFFSET($A$1,ROW()-1,1,1,4))
Note that this uses neither sheet references nor table references - everything references a single coordinate (in this case A1), which can be anywhere on the worksheet (it could also be a named cell).

The alternative, of course, is to not add a new first line to an "empty" table.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Yes the table is A1:H2. One of the properties of a table is that you can delete all the rows but the table "remembers" any formulae that have been present. When a new first row is entered the formulae are still present. Yes if you choose to delete sheet rows the table is also deleted. I did say delete a table row. So inserting a new row should re-establish the formulae that existed before the deletion. It does for the formula format in colums G & H but it gets the formula in column F wrong. The issue is why does this particular format fail and also that users should be aware that this is going to happen. To answer your unasked question about why I would want to delete the last remaining row in a table and then start to fill it up again; well I have on several occasions found it a useful way of reusing a table and its formulae for a series of different data just as one might do with a table in a database. Database tables can also contain fields (columns) which are based on numerical relationships between other fields in the same table. I guess none of this gets us any closer to understanding why there is this apparent fault but in the course of our debate you have given me some new info which I appreciate.