complexe insertion messes up the formulas

[MarieBocc]

Hello everyone,

I am having a hard time figuring out how to deal with a too zealous characteristic of Excel…
I am programming a macro that is basically just an advanced insertion : instead of just copying and inserting a range of cells, I want to allow the user to modify a certain number of values, add or remove rows etc... before inserting it back to the worksheet.

In order to do this, I insert the rows one by one while modifying what should be changed.

The problem with this solution it that it messes my formulas quite a bit.

For instance :
- If the row I am selecting (let's call it n) has a cell (for exemple A:n) whichs formula is refering to the following cell (A:n+1). It inserts it without a problem.
- Then, I insert the second row (n+1) of my selection, which is the one supposedly refered to by the cell A:n.

But since this is considered by Excel like a different insertion, when I insert my n+1th row, the formula in the A:nth cell is automatically changed so that it will refer to the row that was its n+1th at the time it was inserted - which is now n+2.

This functionnality that would be very useful in other situations is a real problem for me as my "special insertion" should keep the formulas well-formed like in a normal insertion.

What should I change so that it will work the way I wish it to ? Any ideas ?

My very first idea was to create a "virtual" range with my special insertion and then to insert it in the worksheet but I found no information anywhere on how to create these kinds of virtual ranges, which is why I came up with this not-so-satisfying one by one rows insertion.

Do you know a way to implement this instead ?

Thank you for reading,

Marie

 

[Snakehips]

Marie,
Does it help if you insert your last row first? Then select that row when inserting the second-to-last row above it etc, etc, until you eventually insert your top row, last.

Edit: Probably not! I did a simple test and it did the trick because my formulas were referencing cells below.
After posting the above I tested with formulas referencing cells above and it messed up formulas relativity,

 

[MarieBocc]

Thank you for your answer Tony,

I thought about it too, unfortunately after analysing the worksheet I have to work with, it appears to me that some formulas reference cells above them and some reference cells below them.
I posted my question on StackOverflow too (does that make me a traitor?) and someone recommended me to use the INDIRECT() function :

"

It would be volatile, but ​

<code style="background-color: rgb(239, 240, 241); border-bottom-color: currentColor; border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: currentColor; border-left-style: none; border-left-width: 0px; border-right-color: currentColor; border-right-style: none; border-right-width: 0px; border-top-color: currentColor; border-top-style: none; border-top-width: 0px; box-sizing: inherit; color: rgb(36, 39, 41); font-family: Consolas,Menlo,Monaco,Lucida Console,Liberation Mono,DejaVu Sans Mono,Bitstream Vera Sans Mono,Courier New,monospace,sans-serif; font-size: 100%; font-size-adjust: none; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; letter-spacing: normal; line-height: inherit; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 1px; padding-left: 5px; padding-right: 5px; padding-top: 1px; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; vertical-align: baseline; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;">INDIRECT()</code>

function could solve this, when you 'hardcode' the cell reference as text!" (answer from JvdV on StackOverflow)

(here is the link to the web page : [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://stackoverflow.com/questions/56971341/freeze-cells-formula-while-inserting-rows-one-by-onehttps://stackoverflow.com/questions/56971341/freeze-cells-formula-while-inserting-rows-one-by-one)

Unfortunately I am new to Excel VBA and English not being my mother language doesn't really help either : I don't really understand how to use the INDIRECT function or whether it works on cells or range, its effects on formulas strings or numbers etc... nor what it means for a value to be "volatile".

Do you happen to know about this ? Do you understand how this function could help me ?

Thank you again for you answer.

Marie_{^{<strike>
</strike>}}[/FONT]​

 

[Snakehips]

Marie, not a traitor but, it is forum protocol to flag up if you have posted similar elsewhere. That can save somebody from taking time to work on an issue that may have already been solved elsewhere.

The INDIRECT function does perhaps have the potential to help but, it will depend on the structure and volume of your data and exactly how you are wanting to manipulate it.


INDIRECT lets you use strings or expressions that resolve to strings to create a range address. A simple example of using IDIRECT() would be to replace
=A2 + B2 - B3 which would give you your problem in your insertion scenario, with
=A2 + B2 - INDIRECT("B" & Row() +1) which wouldn't, as ROW() +1 will always resolve to one more than the row it is placed in and preserve row relativity to other cells of that row.

Most Excel functions are -'non-volatile' and Excel need only re-calculate them when a directly referenced parameter in it or a related function is known to have changed.
INDIRECT, however, is a 'volatile' function in as much as Excel doesn't have, complete, direct reference so needs re-evaluate it every time it calculates. Thus, if over utilised, it can be resource heavy and slow things down.

At the moment, I do not have sufficient understanding of what you have and are trying to do, to be able to advise you further.
Maybe you can post before and after examples?
Am I right in thinking that you need the newly inserted rows to have active formulas?
If not and you just want to preserve the values in the new rows then that is easy.

 

[MarieBocc]

Thank you for this answer.

I will try to explain thoroughly what I am trying to and provide you some exemples.

I am working on a planning containing different contracts, each implying different tasks that have to take place at a specific time and in a specific order in order for the contract to be fulfilled. It is thoses dates that are an obstacle for me during the insertion. This is how the worksheet looks like (I'd recommend you to look at it with a full screen window) :

A B C D E F G H I J K L M N
9 Blatier Blatier BLA BLA Engineering Blatier
10 Blatier BLA1 BLA1 Engineering BLA1
11 Blatier BLA1 PLO Contract K8462
12 Blatier BLA1 PLO pre-intervention engineering 19-may-21 25-may-21 5

13 Blatier BLA1 PLO peri-intervention engineering 11-jun-21 17-jun-21 10

14 Blatier BLA1 PLO post-intervention engineering 13-feb-21 12-mar-21 5
15 Blatier BLA1 PLO pre-ordering 14-may-21 17-jun-21 20
16 Blatier BLA1 PLO AT assembly VD 2021 14-may-21 29-may-21 10-jun-21 10​

​

17 Blatier BLA1 BLA1 VCI Contract U5541
18 Blatier BLA1 VCI pre-intervention engineering 9-feb-23 15-feb-23 5
19 Blatier BLA1 VCI peri-intervention engineering 19-feb-23 3-mar-23 10

20 Blatier BLA1 VCI post-intervention engineering 4-mar-23 10-mar-23 5

21 Blatier BLA1​

​

VCI​

investigation 3-nov-21 30-nov-21 20​

_{^{<strike>
</strike>}}22 Blatier BLA1 VCI pre-ordering 1-feb-22 7-mar-22 25
23 Blatier BLA1 VCI AT assembly VP 2023 4-feb-23 19-feb-23 3-mar-23 10​

​

24 Blatier BLA2 BLA2 Engineering BLA2
25 Blatier BLA2 ​

SDU Contract K8462

26 Blatier BLA2 SDU pre-intervention engineering

20-may-21 26-may-21 5​

​

<strike></strike>

27 Blatier BLA2 SDU peri-intervention engineering

12-jun-21 18-jun-21 10
28 Blatier BLA2 SDU post-intervention engineering

14-feb-21 13-mar-21 5​

29 Blatier BLA2 SDU pre-ordering

15-may-21 18-jun-21 20​

_{^{<strike>
</strike>}}30 Blatier BLA2 SDU AT assembly ASR 2021

15-may-21 30-may-21 11-jun-21 10​

​

<strike></strike>

31 Blatier BLA2 BLA2 VCO Contract L9671
32 Blatier BLA2 VCO pre-intervention engineering

9-fev-23 15-fev-23 5​

​

<strike></strike>

33 Blatier BLA2 VCO peri-intervention engineering

19-fev-23 3-mar-23 10​

34

Blatier BLA2 VCO post-intervention engineering

4-mar-23 10-mar-23 5​

_{^{<strike>
</strike>}}35 Blatier BLA2 VCO pre-ordering

3-nov-21 30-nov-21 20
36 Blatier BLA2 ​

VCO

TEM Assembly TEM 2019 8-feb-23 15-fev-23 15​

_{^{<strike>
</strike>}}37 Blatier BLA2 VCO AT assembly VD 2019

4-feb-23 19-feb-23 3-mar-23 10​

_{^{<strike>
</strike>}}​

​

38 Blatier BLA3 BLA3 Engineering BLA3
39 Blatier BLA3 CRI​

Contract K8462

40 Blatier BLA3 CRI pre-intervention engineering

19-fev-20 25-fev-20 5​

​

<strike></strike>

41 Blatier BLA3 CRI peri-intervention engineering

29-fev-20 13-mar-20 10
42 Blatier BLA3 CRI post-intervention engineering

14-mar-20 20-mar-20 5​

_{^{<strike>
</strike>}}43 Blatier BLA3 CRI pre-ordering

13-nov-18 10-nov-18 20​

_{^{<strike>
</strike>}}44 Blatier BLA3 CRI AT assembly VP 2020

10-feb-20 29-feb-20 13-mar-20 10​

​

<strike></strike>

45 Blatier BLA3 BLA3 CTI Contract U5541
46 Blatier BLA3 CTI pre-intervention engineering

​

12-dec-18 28-dec-18 15​

​

47 Blatier BLA3 CTI peri-intervention engineering

​

14-jan-19 25-jan-19 10
48 Blatier BLA3 CTI post-intervention engineering

30-mar-19 14-apr-19 20​

​

<strike></strike>
49 Blatier BLA3 CTI AT assembly VD 2019

10-dec-18 29-feb-19 13-mar-19 10
​

​

​

​

50 Bugmeis- Bugm-BUG BUG Engineering Bugm-​

_{^{<strike>
</strike>}}

51 Bugmeist BUG2 BUG2 Engineering BUG2
52 Bugmeist BUG2 FTR​

Contract K8462

53 Bugmeist BUG2

FTR​

pre-intervention engineering

​

12-dec-18 28-dec-18 15​

​

<strike></strike>

54 Bugmeist BUG2

FTR​

peri-intervention engineering

​

14-jan-19 25-jan-19 10​

55

Bugmeist BUG2

FTR​

post-intervention engineering

​

14-jan-19 25-jan-19 20​

_{^{<strike>
</strike>}}​

56 Bugmeist BUG2

FTR​

pre-ordering

​

30-mar-19 14-apr-19 10​

_{^{<strike>
</strike>}}​

57 Bugmeist BUG2

FTR​

AT assembly ASR 2018

​

10-dec-18 29-feb-19 13-mar-19 15​

_{^{<strike>
</strike>}}​

58 Bugmeist BUG2

FTR​

pre-intervention engineering

19-fev-20 25-fev-20 5​

​

<strike></strike>

59 Bugmeist BUG2

FTR​

peri-intervention engineering

29-fev-20 13-mar-20 10​

60

Bugmeist BUG2

FTR​

post-intervention engineering

14-mar-20 20-mar-20 5​

_{^{<strike>
</strike>}}61 Bugmeist BUG2

FTR​

analysis

13-nov-18 10-nov-18 20​

_{^{<strike>
</strike>}}62 Bugmeist BUG2

FTR​

TEM assembly TEM 2020

29-feb-20 13-mar-20 10​

​

​

<strike></strike>

63 B

ugmeist​

BUG2 BUG2 VCO Contract L9671
64 B

ugmeist​

BUG2 VCO pre-intervention engineering

​

12-dec-18 28-dec-18 15​

​

65 B

ugmeist​

BUG2 VCO peri-intervention engineering

​

14-jan-19 25-jan-19 10
66 B

ugmeist​

BUG2 VCO post-intervention engineerin

g​

14-jan-19 25-jan-19 15​

_{^{<strike>
</strike>}}​

67 B

ugmeist​

BUG2 VCO pre-ordering

​

30-mar-19 14-apr-19 10​

_{^{<strike>
</strike>}}​

68 B

ugmeist​

BUG2 VCO AT assembly

VD 2019 ​

10-dec-18 29-feb-19 13-mar-19 10
...this goes on for about 700 more rows...


Let's say that the user right clicks on the selected range (rpresented in blue font here) and choses the special insertion option in the menu. An UserForm appears on screen. This UserForm allows the user to add new tasks to the selection, change the order, remove tasks etc... The users choses to add an investigation to it's contract and to insert it after the pre-intervention engineering. The range I now want to insert is as follows :

​

​

​

​

​

​

​

pre-intervention engineering
investigation
peri-intervention engineering

post-intervention engineering
​

investigation​

_{^{<strike>
</strike>}}pre-ordering
AT assembly​

​

_{^{<strike>
</strike>}}​

​

​

​

​

<strike></strike><strike></strike>
Let us call this list listTasks.

The user

also choses in the UserForm that he wants the insertion to be done on BUG2 (see column C).​

He wants to insert an altered version of the selection (containing the tasks in the order stated in listTasks) to BUG2. BUG2 starts at row 50. We will want to add this contract at the end of BUG2, so to insert this range from row 69 onward.​

We now have all the required informations to insert it. Since the range we want to insert is no longer the same as the one selected we will need to recreate it from zero.

We parse listTasks.
For each item, if the item corresponds to one of the items in the original selection, we copy and then insert the row of the initial selection at the right position. In this exemple, the first task will be pre-intervention engineering. We will quickly find it in the original selection. We copy and insert it at row 69.
Note that in cell L:18, the value we have is either 09/02/2023, L:19-10, L:19-N:18-5 or L:19-5-5 and we have no way to know before hand which format the date will take, it varies from contract to contract. But when copying a contract, it is important that the same formula should appear in the formula bar (with the different row position taken into account of course). For the purpose of showing this specific problem, let us consider thet the formula in L18 is L19-N18-5. We have to insert a row for the title of the contract so the insertion of the tasks will start a row 70. When inserting the copied row at row 70, the formula in cell L:70 will become L:71-N:70-5. This is the formula that I would like to keep in this cell.

Unfortunately I have to keep going and we now reach the second task of listTasks : investigation. (This task was not part of the original selection so I search for it elsewhere in the worksheet and insert the found row, or, if not present in the worksheet at all, insert an empty row and set al the cells whose value I know but this part of the algorithm is irrelevant here.) Here another row is inserted. Les us not focus on this row but on the previous one.
Excel considers that L:70, being setted with the formula

L:71-N:70-5, shall be calculated with the value that was in L71 when the first insertion occured. This row was moved down by one row because of the second insertion. The formula in L:70 thus became L:71-N:70-5 when we wanted it to remain the same after the second insertion.


In the end, even tough we wanted to obtain the following duplication of contract :

69 Bugmeist BUG2 BUG2 VCI Contract U5541​

70

Bugmeist BUG2 VCI pre-intervention engineering 9-feb-23 15-feb-23 5
71 Bugmeist BUG2 VCI investigation

3-nov-21 30-nov-21 20​

​

<strike></strike>
72

Bugmeist BUG2 VCI peri-intervention engineering 19-feb-23 3-mar-23 10
73

Bugmeist BUG2 VCI post-intervention engineering 4-mar-23 10-mar-23 5
74

Bugmeist BUG2 ​

​

​

​

​

VCI​

​

​

investigation 3-nov-21 30-nov-21 20​

_{^{<strike>
</strike>}}75 Bugmeist BUG2 VCI pre-ordering 1-feb-22 7-mar-22 25
76 Bugmeist BUG2 VCI AT assembly VP 2023 4-feb-23 19-feb-23 3-mar-23 10​

​

​

This will cause the inserted range to look like this when the macro finish running :​

_{^{<strike>
</strike>}}​

​

​

69 Bugmeist BUG2 BUG2 VCI Contract U5541​

70

Bugmeist BUG2 VCI pre-intervention engineering ​

​

​

###### #NUMBER!​

​

5
71 Bugmeist BUG2 VCI investigation

3-nov-21 30-nov-21 20​

​

<strike></strike>
72

Bugmeist BUG2 VCI peri-intervention engineering ​

​

​

0-jan-00 #NUMBER!​

​

10
73

Bugmeist BUG2 VCI post-intervention engineering ​

​

​

​

#NUMBER! #NUMBER!​

​

5
74

Bugmeist BUG2 ​

​

​

​

​

VCI​

​

​

investigation 3-nov-21 30-nov-21 20​

_{^{<strike>
</strike>}}75 Bugmeist BUG2 VCI pre-ordering 1-feb-22 7-mar-22 25
76 Bugmeist BUG2 VCI AT assembly VP 2023 4-feb-23 19-feb-23 3-mar-23 10

One more constraint I have is that after a new contract has been inserted to the worksheet, when the user clicks on the cell L70, the exact same formula (with the different row position taken into account) shall be displayed in the formula bar. After the macro finished running, the user should also be able to copy-insert our range like any other.
​

​

​

So from what I understood of your explaination about the indirect() function, for every row inserted, for the cells L:rowNumber and M:rowNumber I should get the formula in the cell as a string, parse it and if "L:rowNumber+1" shows up, replace it with

INDIRECT("L" & Row() +1). I have the feeling that the value in the cell would end up being the right one but...W

on't this alter the formula displayed in the cells ? Because if the formula is changed my work won't be accepted. The worksheet after my macro should have the exact same values and formulas as it would have if the user had copied the rows and inserted the himself at the wanted position. Also if it has negative repercutions like a longer processing time, considering the length of my worksheet which is already quite slow to process any action, I fear this wouldn't be a viable solution.

What do you think ?


Sorry for the very long post and thank you for reading if you ever gather the courage to do so, (also sorry for bad grammar or spelling mistakes)

Marie​

​

​

 

[MarieBocc]

...
The formating was absolutely perfect when I posted it but if you see it the same way as I do right now, don't bother even reading it ; this is hopeless, I am sorry for having wasted your time. I Don't know how to import images that would come from my PC nor how to correctly insert a worksheet so nevermind.

Thank you again for the answer you gave me,

Best regards,

Marie

 

[MarieBocc]

For the ones who will want to do the same thing as me,

I ended up inserting all the empty rows first and then copy/pasting all the rows I wanted in them.
This way to resolve the issue can only be done when you know beforehand the number of rows that will be inserted so it does not solve the issue highlighted by the topic but still…
It might help someone to see it from a different angle in the future and it might help for his case too, so I still post the solution I ended up using !

Good luck !

Marie

 

[Snakehips]

@MarieBocc Unfortunately the above did not render well and as you might suspect, has not really improved my understanding.
Does the following give you any clues for a solution?

Test on like the below data. Copy D2 formula down but then edit the formula in D15 to be slightly different.
If you were to Copy/ insert rows 4:6 at row 15 in the normal way, manually, it will give you the 'preserved' references for formulas the new rows 14 and 18, that you don't want.
Alternatively, run the below code on this data set and see if that gives you more like you are wanting.

Excel 2010

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]16[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=A2+B3-C1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D15[/TH]
[TD="align: left"]=A15+B16-C1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Sub MarieTest()

'Assumes formulas in column D
'Assumes inserting with row 15 selected
InsertRow = 15


F1 = Cells(InsertRow - 1, 4).FormulaR1C1
F2 = Cells(InsertRow, 4).FormulaR1C1


'Select eg rows 4, 5, 6 to insert
    Rows("4:6").Select
    NumRows = Selection.Rows.Count
    Selection.Copy
    
    Rows(InsertRow & ":" & InsertRow).Insert Shift:=xlDown
Application.CutCopyMode = False
Cells(InsertRow - 1, 4).FormulaR1C1 = F1
Cells(InsertRow + NumRows, 4).FormulaR1C1 = F2
End Sub