Column inserts may or may not adjust SUM formulas following the insert.

I have noticed that when I manually insert a new column, the following SUM column gets automatically adjusted correctly, which is what I expect. However, the SUM column is not corrected if I insert the column using an Excel macro.


SETUP
1. columns C thru H contain client data and column I cells are set to SUM($Cx:Hx) where x is the row number
2. insert a new client column I to the left of the current I column, which pushes the old SUM column I to column J

OUTCOMES
1. via manually - the SUM column J now reads SUM($Cx:Ix) (correct)
2. via macro - the SUM column still shows SUM($Cx:Hx) (incorrect)


What am I missing here? I had to then write more VBA code using the Find/Replace function to change all column J formulas from :H's to :I's. My macro works but I must be missing some logical flag set or something. Any ideas?

Try this in Ix

=SUM(Cx:INDEX(x:x,COLUMN()-1))

Very clever, but any ideas why the macro insert column works differently than a manual insert column action?

I manually insert a new column in I column, but the formula is also =SUM($C1:H1) , not =SUM($C1:I1).

[SOLVED]Wow, you are correct. My error in testing was inserting in the middle of the columns. That does adjust the SUM formula but inserts just to the left of the SUM column does not. I made a rookie mistake. Thank you so much for your response.

You are welcome.
--------------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.