Auto-insert row based on cell input.

November 23, 2012 at 14:06:50
Specs: Window 7

How to auto-insert an row with column range x-x to include formulas from those columns below the previous row if a cell in the previous row have value inputed?

My manual process has been selecting the cell range of the row, copy, and insert copy cells...shift down.

1. Let's say row 1 have column A through H and E is the input column. As soon as E cell have an input data it trigger the following macro.
2. Once a value is inputed in row 1 E4 then a new row (just A-H column) with it's formulas inserted right below.
3. The new row 2 is empty waiting for new entry into E4 cell but the formula carry over with current row cell location instead of the previous cell location in the formula. i.e. A4 row: =A5-A4. the inserted A5 row: =A6-A5
4. The process continue...

I hope that was clear. I know it's a macro thing but I have yet to understand it well enough to modify the similar post I have read so far. Also how to assign the macro to the column?

Thank you in advance!!:)


See More: Auto-insert row based on cell input.

Report •


#1
November 23, 2012 at 15:41:23

It's hard to offer any solution since your example doesn't make sense:

"2. Once a value is inputed in row 1 E4..."

E4 is in row 4, not row 1.

"3. The new row 2 is empty waiting for new entry into E4..."

E4 is still in row 4, not row 2.

Please revise your explanation so that we can understand what you are trying to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 23, 2012 at 19:26:55

Sorry about that...

1. Let's say row 1 have column A through H and E is the input column. As soon as E cell have an input data it trigger the following macro.
2. Once a value is inputed in E1 then a new row (just A-H column) with it's formulas inserted right below.
3. The new row 2 will be empty and waiting for a new entry into E2 cell. All the formulas will carry over with current row cell location instead of the previous cell location in the formula. i.e. A1 row: =A2-A1. the inserted A2 row: =A3-A2
4. The process continue...

I hope this cleared up the confusion and thank you for your help:)


Report •

#3
November 24, 2012 at 10:16:04

Once again, you have a posted a unclear requirement.

i.e. "A1 row: =A2-A1. the inserted A2 row: =A3-A2"

First, A1 and A2 are not rows, A1 and A2 are cells. I don't know what you mean by "A1 row" and "A2 row"

If you mean the cells A1 and A2, then you have another problem. You can't have =A2-A1 in A1 or =A3-A2 in A2.

That will cause a circular reference since you are asking Excel to calculate a value using a value from the same cell in which you want the answer. That can't be done.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 24, 2012 at 11:53:43

Ahh...where is my mind?!! Once again my apology.

The cell C1 formula is =A2-A1. So carried over to the cell C2 will be =A3-A2.

I hope that is it, again thank you so much for being patient with me. It's the macro to trigger the copy of the current row with column range A-H, insert shift down, clear the inserted row of data but the formula carry over with the changes above there are giving me a hard time.


Report •


Ask Question