This appears to be a template that is fixed as far as vertical size is
concerned. In other words, do you really want to insert a new row and
possibly cause the printout to wrap onto a second page, or do you simply
want to have the formulas show values when you start entering data on a new
row?
In this situation, I usually enter formulas in ALL of the cells (rows 8 thru
15 in your JPG screenshot), even though most of them will not have data. Set
up the formulas so that any values are 0 (or some other sentinel value) if
column $A or $B is blank. So, the formula in cell $L$10 would be:
=IF(ISBLANK(A10),0,0.375*K10)
Then format the values so that they do not show when the value is 0. See
the "Create a custom number format" topic in Excel Help. The format for this
example would be something like the following (notice the 2 semi-colons next
to each other near the end of the line for the 0 format):
#,##0.00;[Red] #,##0.00;;@
This completely eliminates the need to write any macros to add or delete a
line, which can get very messy when you have a "fixed size" form like this
to populate.
--
Regards,
Bill
"iltf124 no spam @hotmail.com" wrote in message
m...
Hello,
I know that something relative to this has been posted before but I
need to get a little more specific in this newsgroup in hopes that I
can get some help. Please keep in mind that I am not a developer, nor
an expert with Excel.
I have (somewhat unsuccessfully) written an excel macro to Insert a
black row while copying the formula and formatting from the row above
it. The macro is assigned to a button that....when the button is hit
....there is one row added at the row of the "Active Cell".
Problem: Whenever I insert a row, instead of the macro inserting a
blank row containing only the formula from the row above, it will copy
the data along with the formula...which is not what I'm looking
for....
Here is the snippet of code that goes with my InsertRow macro:
Sub InsertCopiedRow()
ActiveSheet.Unprotect "****"
ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveSheet.Protect "****"
End Sub
I have scoured newsgroups and MSVP Excel pages....and it seems like
everyone is pointing at doing some sort of changes with the formulas
AND the actual macro.....especially with some sort of OFFSET function.
I am open to making this work, but I cannot figure out how to insert
OFFSET properly into my formulas/macros if necessary....
Please see to get a visual of the worksheet:
http://php.indiana.edu/~cawagner/macro.jpg
Any help would be greatly appreciated......
Roger