Formula disappears

[Med4040]

Hi,

I am not sure if this is simple or not but I have a table that looks something like this

cells A7:F7 contain formulas. the table need to be updated once in a while by inserting a new column, but when I do this, the formula disappears from the corresponding cell in row 7.

[/IMG]

 

[JLGWhiz]

If row 7 is the last row in your table and your table is created using the list method, then by inserting a row, you move the existing row 7 and its contents out of the table and off the list. Same is true for the last column contents if inserting a column.

 

[Med4040]

Is there anyway to auto copy the formula when inserting a new column? I am sure there must be something!

 

[JLGWhiz]

You can copy formats it using VBA

Range("B2:B7").Insert xlShiftToRigh, xlFormatFromLeftOrAbove

But I don't think you can get the cell contents to automatically copy using a function argument. I am not sure that the formulas can be put in an array to be pasted after the insert, but it seems feasible. Maybe somebody smarter than me can help with that.

 

[Med4040]

JLGWhiz,

Do I just add this code in VBA and that's it?
That's what I did but nothing happened.

I am not sure if it helps but the formulas I have are

SUMPRODUCT((G13:G65="Y")*$D$13:$D$65)
and
VLOOKUP(G5,APIData!$C$2:$S$199,17,FALSE)
in two subsequent rows.

Anyways, I really appreciate you trying to help me out.

 

[JLGWhiz]

JLGWhiz,

Do I just add this code in VBA and that's it?
That's what I did but nothing happened.

I am not sure if it helps but the formulas I have are

SUMPRODUCT((G13:G65="Y")*$D$13:$D$65)
and
VLOOKUP(G5,APIData!$C$2:$S$199,17,FALSE)
in two subsequent rows.

Anyways, I really appreciate you trying to help me out.

I am not sure that I understand completely which formulas are disappearing, or how you have your table arranged formula wise. It might just be a matter of highlighting a column and then dragging it to the newly inserted column. But I cannot recommend that without knowing which formulas are in which cells and if they are constructed so that they can be dragged over and automatcally adjust to the new address. For example, column G is not shown on your example in the OP, but is referenced in one of the formulas, so it is confusing to me as to what your objective is and where which formulas would be used.

 

[Med4040]

The image I showed in thread #1 is just an example. In my table, there are few formulas in multiple rows. Perhaps I should've not said that the formulas are disappearing but rather the formulas are not auto copied to any new inserted column. Dragging across is definitely something I can do but I was wondering if this can be automatic, without dragging across.

 

[JLGWhiz]

The 'CopyOrigin' argument for the 'Insert' function is for formats only, no formulas or data. that is why you did not see any change when you tried the code. It might be possible to write code that would use the change event created by the 'Insert' execution, but I would not want to do it. It is just as easy to highlight the adjacent range and drag it over to the new column.

 

[Med4040]

I understand. I thought there is a way to do this. The reason I don't prefer dragging because I want to protect the sheet so users don't mess it up.

 

[JLGWhiz]

Actually, you can use a pretty simple code if you are inserting a range within a column:
Example

Range("D2:D7").Insert xlShiftToRight
Range("C2:C7").Copy
Range("D2.D7").PasteSpecial xlPasteFormulas

This is assuming you are already doing the insert as part of an existing code. You could work this into the existing code. But again, I don't have a good understanding of what prompts you to insert a column, the extent of the range that is inserted, the form of the formulas that will be copied, etc. that all affect how the results of such action would affect your data. You could try the above and see if it works for you. But getting it to work automatically is another matter.