35 views

Skip to first unread message

Mar 11, 1998, 3:00:00 AM3/11/98

to

I would like to insert rows into my spreadsheet and fixup the formulas that

might otherwise be destroyed. I do not want to copy cells with data. I only

want to copy the formulas. If it is not a formula leave or make the cell

null.

I am pretty sure that the SOLUTION WILL REQUIRE CHECKING CELLS TO SEE IF

INSERTED CELL

IS BELOW A FORMULA OR NOT. Please keep in mind that a generic solution is

wanted not a test to match this particular example which only has formulas in

one column (col E).

Mark Hill <mark...@charm.net.noSpam> proposed a solution to a problem

sometime back that I will use to illustrate what I want and don't want.

Representation of Original Data

--C-- --D-- --E-- Formula in E

13 0 13 =C2-D2

0 14 -1 =E2+C3-D3 <--- Select this row insert 3 lines

25 0 24 =E3+C4-D4

37 0 61 =E4+C5-D5

38 0 99 =E5+C6-D6 0's would normally be null cells.

Mark's Macro: (9 non blank lines)

Sub InsertRowsAndFillFormulas()

' Re: Insert Rows -- 1997/09/24 Mark Hill <mark...@charm.net.noSpam>

Dim vRows%

vRows = Application.InputBox(prompt:="How many rows do you want to add?",

Title:="Add Rows", default:=1, Type:=1) 'type 1 is number

If vRows = False Then Exit Sub

'if you just want to add cells and not entire rows then delete ".EntireRow"

in the following line

Selection.Resize(rowsize:=2).Rows(2).EntireRow.Resize(rowsize:=vRows).Insert

shift:=xlDown

Selection.AutoFill Selection.Resize(rowsize:=vRows + 1), xlFillDefault

End Sub

Results:

--C-- --D-- --E-- Formula in E

13 0 13 =C2-D2

0 14 -1 =E2+C3-D3

-----------------------------------------------------

1 15 -15 =E3+C4-D4 Three inserted lines

2 16 -29 =E4+C5-D5

3 17 -43 =E5+C6-D6

-------------------------------------------------------

25 0 24 =E3+C7-D7 <-- note formula incorrect.

37 0 61 =E7+C8-D8

38 0 99 =E8+C9-D9

This is what I want "--" represents null cells

--C-- --D-- --E-- Formula in E

13 0 13 =C2-D2

0 14 -1 =E2+C3-D3

-----------------------------------------------------

-- -- -- =E3+C4-D4 Three insert lines

-- -- -- =E4+C5-D5 "--" are null cells

-- -- -- =E5+C6-D6

-------------------------------------------------------

25 0 24 =E6+C7-D7 <- formula is "corrected"

37 0 61 =E7+C8-D8 (as if vRows + 2)

38 0 99 =E8+C9-D9

After inserting lines the results should show inserted cells below non-formulas

to be empty, and below formulas to have an inserted formula. The formula must

be extended one row additionally to fix the original formula below the inserted

lines.

One more comment. The solution might be more Excel like if the rows were

selected BELOW the insertion point, in this case select three rows below the

insert point. Just a thought. This eliminates the request for number of rows

to insert and makes it easy to install macro on a toolbar button. Disadvantage

if line below is unlike the lines above as a cell contain totals =SUM(....

Column F in my example uses a macro to display column E.

Should make testing easier.

Function GetFormula(Cell)

'Documented in http://members.aol.com/dmcritchie/excel/formula.htm

GetFormula = Cell.Formula

End Function

I hope this is clear

TIA,

// David McRitchie DMcRi...@aol.com

Mar 11, 1998, 3:00:00 AM3/11/98

to

' to remove the non-formulas

Selection.Offset(1).Resize(vRows).EntireRow.SpecialCells(xlConstants).ClearContents

If you wrote the formula in E3 as

=OFFSET(E3,-1,0)+C3-D3

and copied it down, you would not need to correct the formula in the

row after the inserted rows.

Bill Manville

Oxford, England

Microsoft Excel - MVP

Mar 12, 1998, 3:00:00 AM3/12/98

to

That was a quick reply Bill,

It certainly works and I picked up how to code the spreadsheet in a manner more

conducive to adding and deleting rows.

I think I would still like to have a coding though that would work on my

simple formulas so that it would be more "foolproof" in invoking the macro.

But the point is well taken that the coding of the spreadsheet could be easily

changed.

using your suggested

=OFFSET(E3,-1,0)+C3-D3

instead of

=E2+C3-D3

so that all the parts name cells on the same row so that the row can copied

down without correcting a formula below the inserted lines afterwards.

I was hoping to learn how to code and test some of these things in Visual Basic

--- you make it seem so simple with your shortcut. :-)

I guess I did better in explaining what I wanted this week. Thanks again.

Thanks,

David McRitchie DMcRi...@aol.com

Mar 13, 1998, 3:00:00 AM3/13/98

to

David:

Here is a subroutine that might do what you want. I agree that inserting

above the selection is more like the normal Excel Insert Rows function,

and that is how this routine works. Select an area that marks the rows

to be inserted and the columns that have formulas or data to copy.

This routine does little checking, and assumes that the formulas on the

row above the selection are what you want in all of the inserted rows.

'=============================================

' Insert rows in selection and copy formulas from the row above.

' This subroutine will fix up formula references in the row below

' the inserted rows that should refer to the previous row. It will

' NOT handle references to any other rows.

'=============================================

Sub InsertRows()

Dim R As Range

Dim C As Object

Dim i As Long

Dim NumRows As Long

Dim SavedRow As Variant

With Selection

NumRows = .Rows.Count

' Save formulas and data for top row of the selection

' (saves value for cells that don't have formulas)

' SavedRow will be an array with two dimensions, 1 row

' by however many columns are selected

SavedRow = .Resize(RowSize:=1).Formula

' Insert new rows

.EntireRow.Insert

End With

' Save the selected range - PasteSpecial will modify it

Set R = Selection

' Copy formulas and data from row above selection

R.Offset(-1, 0).Resize(RowSize:=1).Copy

' Paste formulas in inserted rows and row below where

' formula reference will have been corrupted. Data in

' the last row will be restored later

R.Resize(RowSize:=NumRows + 1).PasteSpecial

' Clear data and leave formulas in inserted rows

' (but NOT in the row below the inserted rows!)

For Each C In R

If Left(C.Formula, 1) <> "=" Then

C.Formula = Empty

End If

Next C

' Restore data (but not formulas!) to the row below the

' inserted rows

With R.Offset(NumRows, 0)

For i = 1 To UBound(SavedRow, 2)

If Left(SavedRow(1, i), 1) <> "=" Then

.Cells(1, i).Formula = SavedRow(1, i)

End If

Next i

End With

' Restore selection and clear copy mode marker

R.Select

Application.CutCopyMode = False

End Sub

In article <19980311213...@ladder03.news.aol.com>, dmcri...@aol.com

(DMcRitchie) wrote:

>I would like to insert rows into my spreadsheet and fixup the formulas that

>might otherwise be destroyed. I do not want to copy cells with data. I only

>want to copy the formulas. If it is not a formula leave or make the cell

>null.

>

<< Lines deleted from original post >>

>Representation of Original Data

>--C-- --D-- --E-- Formula in E

>13 0 13 =C2-D2

>0 14 -1 =E2+C3-D3 <--- Select this row insert 3 lines

>

>25 0 24 =E3+C4-D4

>37 0 61 =E4+C5-D5

>38 0 99 =E5+C6-D6 0's would normally be null cells.

>

<< Lines deleted from original post >>

>This is what I want "--" represents null cells

>--C-- --D-- --E-- Formula in E

>13 0 13 =C2-D2

>0 14 -1 =E2+C3-D3

>-----------------------------------------------------

>-- -- -- =E3+C4-D4 Three insert lines

>-- -- -- =E4+C5-D5 "--" are null cells

>-- -- -- =E5+C6-D6

>-------------------------------------------------------

>25 0 24 =E6+C7-D7 <- formula is "corrected"

>37 0 61 =E7+C8-D8 (as if vRows + 2)

>38 0 99 =E8+C9-D9

>

>After inserting lines the results should show inserted cells below non-formulas

>to be empty, and below formulas to have an inserted formula. The formula must

>be extended one row additionally to fix the original formula below the inserted

>lines.

>

>One more comment. The solution might be more Excel like if the rows were

>selected BELOW the insertion point, in this case select three rows below the

>insert point. Just a thought. This eliminates the request for number of rows

>to insert and makes it easy to install macro on a toolbar button. Disadvantage

>if line below is unlike the lines above as a cell contain totals =SUM(....

>

<< Lines deleted from original post >>

>

>TIA,

>// David McRitchie DMcRi...@aol.com

Brent Miller bmi...@scruznet.com

Reply all

Reply to author

Forward

0 new messages

Search

Clear search

Close search

Google apps

Main menu