InsertRowsAndFillFormulas

[KC Cheung]

Good morning,

I wish to seek advice on this procedure as in
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

I made two alterations to facilitate testing:

Delete Optional vRows As Long = 0 to
Sub InsertRowsAndFillFormulas()

Set vRows = 1 in place of the If...End If msgbox

Results:
insert row, OK
autofill row, OK
clear constants, OK
BUT
the formulae in the original rows (now pushed down) remain as they were.

I also cannot understand why we have:
x = Sheets(sht.Name).UsedRange.Rows.Count
unless there is a missing procedure to replace formulae from relevant cells
down to row(x)

This procedure has been looked at so many times since 1987 and am sure it is
working.
What have I missed please?

Regards
KC

 

[dmcritchie_xlmvp]

I wish to seek advice on this procedure as in

http://www.mvps.org/dmcritchie/excel/insrtrow.htm

Hi KC,
The explaination for the optional parameters is immeditely below the
two procedures at the top of the web page, but here is different
wording:

The optional parameter is so that you do not need to specify how many
rows to insert if called from another macro. The default of zero
simply
indicates that no choice was made and so you would have to tell it how
many rows to insert in your response.

Macros with optional parameters perform like functions and will not
show up in the list the of macros Alt+F8 (Tools, macro, macros) and so
that you will see the macro in that list we need the small macro of the
pair of macros. When you invoke macro from
Alt+F8 macro list you cannot specify the values for any parameters,
hence the
need for a default value which essentially says we don't have one so
ask me.

You might want to read Chip Pearson's page on the difference between
Macros and Functions http://www.cpearson.com/excel/differen.htm

The following line is to reduce last cell problems, the explanation
for the
x = Sheets(sht.name).UsedRange.Rows.Count 'lastcell fixup
can be found on the link beside it (as seen on web page) in John
Walkenbach's
Excel Developer Tip: Automatically Resetting the Last Cell (tip
73)
http://j-walk.com/ss/excel/tips/tip73.htm

The above trick does not alwys work and if you want to fix all pages at
once with something more reliable, that fixes all pages in a workbook,
and that takes more time (not that you would be bothered by the time
when you have such problems) see
Debra Dalgleish's page (use the macro, forget about manual
method)
Why do my scrollbars go to row 500 -- my data ends in cell E50?
http://www.contextures.com/xlfaqApp.html#Unused

The better place to pick up the actual macro code my pages is from the
code directory, which may have additional macros and functions:
http://www.mvps.org/dmcritchie/excel/code/insrtrow.txt
not included on the descriptive HTML web pages which have explanations
and links, in this case
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

The macro inserts new lines BELOW the original row (row with active
cell), so it the original row is moved down additional changes (or
deletions) were made or the perception of the original row may be off.
Try coloring row and try using marked cells with original addresses in
them in your testing, see
http://www.mvps.org/dmcritchie/excel/join.htm#markcells

 

[KC Cheung]

Good morning Mr McRitchie

Thank you for the advice.
For some reason unknown to me, I could not find your response to the news
group and I cut and paste relevant parts from my inbox.

I like the quote from Pete Seeger. While studying this procedure purely for
education, I gain at the same time some experience.

[Macros with optional parameters perform like functions...]
This is the first time I come across it.

[The following line is to reduce last cell problems]
but it seems to serve no purpose in the procedure unless there is a missing
procedure to replace formulae from relevant cells
down to row(x)

Explicitly
I can get this
A B C D E F G H I
1 Date Check# Descriptions DR CR Balance Reconciled stmt
2 3 01/20/1998 DEP Initial Balance 2000.00 2000.00 02/27/98 2000.00
3 7 2000.00 2000.00
4 6 02/06/1998 Debit Phone Co. 18.22 1981.78 02/27/98 1981.78
5 2 02/23/1998 2619 Gas/Electricity 117.97 1863.81 1981.78


but I cannot get 1954.83 in G4 if I enter ONLY 26.95 in F3
A B C D E F G H I
1 Date Check# Descriptions DR CR Balance Reconciled stmt
2 3 01/20/1998 DEP Initial Balance 2000.00 2000.00 02/27/98 2000.00
3 5 02/05/1998 2618 Subscription 26.95 1973.05 1981.78
4 6 02/06/1998 Debit Phone Co. 18.22 1954.83 02/27/98 1981.78
5 2 02/23/1998 2619 Gas/Electricity 117.97 1836.86 1981.78


I resolved this issue with
for each cell in row 3
if cell.hasformula then autofill from cell to lastcell in column
next cell

I will read slower from now on.
Thank you again for your advice.

Regards
KC Cheung