Inserting rows and mending formula references
DMcRitchie
I would like to insert new lines into a spread sheet that has references that
total something on the current line with something on the previous line.
If I have lines with formula, I can drag them down BEFORE entering data,
everything works fine, this is so easy and Excel knows what I want. A total
involving something on the line is combined with a total on the previous line.
Question: If I insert 5 lines is there an easy way to mend the formulas
without affecting existing data or data yet to be entered on the new rows.
Bonus points if I can sort the data and apply the same method. Don't want a
macro that that would be unique to the structure of one sheet.
2 =B17+A18 =D17+B18*2 This is row 18
4 =B18+A19 =D18+B19*2 This is row 19
6 =B19+A25 =D19+B25*2 Was 20 is now row 25.
8 =B25+A26 =D25+B26*2
10 =B26+A27 =D26+B27*2
The best that I can think of is to use the formula view as above and copy down
the specific lines with such formulas, until they are matched -- copy cell
above inserted rows in one column down through inserted cells + one more row.
Do same for each of any other columns with formulas referencing (former)
"previous" cell rows.
Sorting presents additional problems: Say I sorted the above with the inserted
blank lines this is what I would get in formula view.
2 =B17+A18 =D17+B18*2
4 =B18+A19 =D18+B19*2
6 =B14+A20 =D14+B20*2
8 =B20+A21 =D20+B21*2
10 =B21+A22 =D21+B22*2
No bonus points here have to retype the formulas on the top line shown and drag
down the formulas.
Need a reason for the first case, something similar to a check book. I forgot
to enter a check. Or automatic billing takes place on a date that I can't
predict easily, and want it to enter it after the fact on the correct date.
Also different segments have different formulas so can't copy down the entire
sheet.
David McRitchie < DMcRi...@aol.com >
Harald Staff
David:
If I understand the task correctly: After inserting the five new rows,
say 20 to 24, then select row 19. Down right on the row number tab is a
little black square that is a fill handle. Drag this handle down to row
24 to copy the formulas into the new rows -OR down to row 25 to also
alter the formulas in row 25. It shouls now read =D24+B25*2
Sorting sheets like this (with formulas across rows, not only columns)
is indeed a very dangerous act with several points of no return. Try to
make a system that copies the information (values, preferrably) into
another sheet in the workbook before sorting. The macro recorder and the
paste special > values is a good place to start.
Hope this helps. Harald
DMcRitchie
Sorry Harald,
I already included something that indicated I was aware of fill handles in the
original post, though I didn't call them by name. Also using fill handles is
how I filled in the formulas in the first place.
Restatement of Problem:
I can create the a whole series of rows by using the fill handle and dragging
down the entire row to create lots of new rows.
When I insert new rows within existing rows, I have to handle with care and
only drag down columns with formulas not columns with data. I have to fill
down ONE COLUMN at a time from the cell above the inserted row(s) to the cell
below the inserted row(s) and then do the same for EACH of the other columns
with formulas. I also do not want to do the entire sheet because there may
be unique formulas elsewhere or other areas, but for a small area there should
be no problem.
Means to a solution:
Anyway I see the means to a solution with a general purpose macro, meaning it's
not next to impossible.
1. Use of a macro button to insert ONE row after the row containing a single
selected cell. Comments: I only need to insert a single row at a time. The
example enters several rows because manually it is easier to deal with all
insertions at one time. The location of the inserted line will be below a
single selected cell. Failure to select only a single cell or the selection
of cells on multiple rows need not be tested for -- user problem and
restriction is acceptable.
2. Identification: The column that I want to fill down through the affected
area will be columns with a formula in cells above the row above being
inserted row, which can be identified by =CELL, or by beginning with an "="
sign.
3.The macro will identify its range across columns. An alternative would be to
select the rightmost cell for the fill. But I don't want this restriction.
4.The macro button that I would use would be drawn with football stitches
across it +++ (like visible surgical stitches). Maybe with letters INS
above if it will fit.
It won't handle modified formulas like =c2+.20*b3 changed to =c2+.32*b3 but
special modifications would NOT have been created by fill handles only anyway.
It would be better if user switch to formula view when inserting columns for
visual verification, but not otherwise important.
I will put how to do this on my web site when I have a solution. Anyone care
to do the macro or already have done this?
My outline for a solution does not get any bonus points to handle the sort with
the same solution but a separate solutions could be designed using the range
used in the sort and using the first row in the range as the pattern. Not
important right now and has additional problems like the formulas in the first
row may differ from subsequent rows because there is no total above the first
row.
This is the Example that I might use:
=WEEKDAY(B214,1) [date] [chk#/DEP/ATM] [description] [blank] [amt]
=G213-F214+E214 [unused col] [reconciled date]
=IF(I214="","",F214-E214) =IF(H214<>"",M213+E214-F214,M213)
The row attempts to indicate my checkbook balance and finally the banks balance
for my account. Inserting a missed check or automatic debit entry after the
fact is not easy (time-consuming).
David McRitchie <DMcRi...@aol.com>
Harald Staff
David
Retry. You will have to excuse if I got it wrong this time too. English
is not my native tongue.
This macro inserts a row below the cell You doubleclick (run Dblclk to
get this to work), and copies the formula from the row above it in
columns 2 and 4 only; the B14 formula into B15, B16, B17 when inserting
after B15.
This is ONLY to be used in sheets where the formulas in these columns
point to themselves, constants and one row above, f.ex in Cell D14 = B14
- 2/C13 + $A$1. Your examples indicate this is the matter.
Sub Dblclk()
ActiveSheet.OnDoubleClick = "copyform"
End Sub
Sub copyform()
R = ActiveCell.Row
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
With ActiveSheet
.Cells(R, 4).Activate
.Cells(R, 2).FormulaR1C1 = .Cells(R - 1, 2).FormulaR1C1
.Cells(R + 1, 2).FormulaR1C1 = .Cells(R - 1, 2).FormulaR1C1
.Cells(R + 2, 2).FormulaR1C1 = .Cells(R - 1, 2).FormulaR1C1
.Cells(R, 4).FormulaR1C1 = .Cells(R - 1, 4).FormulaR1C1
.Cells(R + 1, 4).FormulaR1C1 = .Cells(R - 1, 4).FormulaR1C1
.Cells(R + 2, 4).FormulaR1C1 = .Cells(R - 1, 4).FormulaR1C1
End With
End Sub
Just a suggestion. Harald
DMcRitchie
Harold,
Thanks, you inserted a row and mended the formulas, but I am looking for
something more generic (universal).
The row should be inserted below the selected cell -- yes.
The inserted row should receive the full PasteSpecial treatment to copy
formulas, colors, fonts etc., which you did but I want it to be entirely
generic and not dependent on the structure of a particular spreadsheet or a
portion of it..
Now going through the inserted row from left to right (by columns)
If the cell is a formula then copy the cell down pasteSpecial for
formulas
If the cell is not a formula then clear the cell as if the Delete key had
been used.
Do this until no more cells are involved on the row (active cells only).
I think cell.FORMULA might be able to be used to determine if a cell is a
formula
or something in SpecialCells might be used to identify three items needed the
cells with formulas, with constants, and the last cell in the row. The
example for SpecialCells is just an Excel example, what I need would relate to
a row, or a cell.
Worksheets("Sheet1").Activate
ActiveSheet.Cells.SpecialCells(xlLastCell).Activate
If it is too hard to find where the range ends then select the right most used
cell on the row and programmatically work up to this cell.
Creating a macro the basis might look something like the following before being
made generic as described above. It includes code especially at the top that
I don't know what it is and is probably irrelevant. Separate question in VBA
is indentation critical to function or just to readability?
Sub MacroXX()
'no idea what this is...
With Toolbars(8)
.Left = 469
.Top = 10
End With
'Selected a cell E5 in in
Range("E5").Select
Selection.EntireRow.Insert
Rows("4:4").Select
Selection.Copy
Rows("5:5").Select
ActiveSheet.Paste
' A5 on the inserted row is not a formula so clear A5
'it has to be tested for to determine this, of course, but I don't know how
Range("A5").Select
Application.Run Macro:=Range("AUTOSAVE.XLA!mcs02.OnTime")
Selection.ClearContents
'B5 on the inserted row is not a formula so clear B5
Range("B5").Select
Selection.ClearContents
'C5 on the inserted row IS A FORMULA so fill-down to cell below
Range("C5").Select
Selection.AutoFill Destination:=Range("C5:C6"), Type:=xlFillDefault
Range("C5:C6").Select
'D5 on the inserted row is a formula so fill-down to cell below
Range("D5").Select
Selection.AutoFill Destination:=Range("D5:D6"), Type:=xlFillDefault
Range("D5:D6").Select
'E5 on the inserted row is not a formula so clear E5
Range("E5").Select
Selection.ClearContents
End Sub
My way of thinking is that I would like to insert the new row after the line,
but since Excel inserts a row BEFORE the selected line that is probably what
should be done. It also takes care of the problem where the first line,
correctly, would not be referencing a line above it.
Thanks for your help, I'm sure when this is completed it will help a lot of
people, I know I'll sure be using it.
David McRitchie < DMcRi...@aol.com >
Harald Staff
Hi D
Glad I was closer. Most of Your formatting goals are easy, just a
modification of the existing macro.
BUT: You can not make it universal. Because:
If the formula in A10 says =B5+B10+B15
an insert between 11 and 15 will affect the formula to =B5+B10+B16 ,no
longer the steps of five. Another row, =B5+B10+B17. The only reasonable
thing to do then is to insert and reformat, and then replace ALL the
formulas in the sheet with a FormulaLocal function. I am really not sure
this would be a good idea in every case, most cells point to another for
a secific reason. But i will be happy if You prove me wrong.
Best wishes Harald
DMcRitchie wrote:
>
> Harold,
> Thanks, you inserted a row and mended the formulas, but I am looking for
> something more generic (universal).
>
> The row should be inserted below the selected cell -- yes.
> The inserted row should receive the full PasteSpecial treatment to copy
> formulas, colors, fonts etc., which you did but I want it to be entirely
> generic and not dependent on the structure of a particular spreadsheet or a
> portion of it..
(snip)