12 views

Skip to first unread message

Feb 23, 1998, 3:00:00 AM2/23/98

to

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 >

Feb 23, 1998, 3:00:00 AM2/23/98

to

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

Feb 23, 1998, 3:00:00 AM2/23/98

to

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>

Feb 23, 1998, 3:00:00 AM2/23/98

to

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

Feb 24, 1998, 3:00:00 AM2/24/98

to

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 >

Feb 25, 1998, 3:00:00 AM2/25/98

to

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)

Reply all

Reply to author

Forward

0 new messages

Search

Clear search

Close search

Google apps

Main menu