Rewritten Post of Adding Macros for Adding Rows

[donnyray]

My original Post was apparently confusing those trying to help me figure out this dilemma, so I am reposting a more broken down version of the post

I'm new to registering to this site but have used the related posts on the forums before but I can't seem to find something more specific to my needs. I am still new to using macros in Microsoft Excel so the coding is difficult for me to write.

I have values in column A that I use to add rows and the macro works for that need. The macro is as follows:

Sub Insert()
Dim End_Row As Long, n As Long, Ins As Long
End_Row = Range("A" & Rows.Count).End(xlUp).Row


For n = End_Row To 1 Step -1


Ins = Cells(n, 1).Value


If Ins > 1 Then Range("A" & n + 1 & ":A" & n + Ins).EntireRow.Insert


Next n
End Sub

Needed:

Example:
Column A Column B Column C Column D
# of Areas Name Title Areas
7 John Doe Area Manager *=offset formula*
******Insert 6 additional rows with populated formula in Column D in new rows and no longer adds additional rows

Problem 1:

Example:
Column A Column B Column C Column D
# of Areas Name Title Areas *Original Entry
8 John Doe Area Manager *=offset formula*
--Run Macro
******Insert 6 additional rows no formula

I have a formula in column D that I want to populate when the new rows are added.


Problem 2:

Example:
Column A Column B Column C Column D
# of Areas Name Title Areas *Original Entry
7 John Doe Area Manager *=offset formula*
--Run Macro
******Insert 6 additional rows no formula
******Inserts 6 more rows when running the macro for the entry below.
8 Jane Doe Area Manager *=offset formula* *New Entry After Original Macro Runs
******Insert 7 additional rows no formula


One problem I have is that if I have to add entries that need to have additional rows added with the macro, the Macro adds more rows to the entries that I already have the appropriate additional rows. How can I have the Macro ignore those entries? Is there a delimiter I can add for it to disregard those entries to not add additional rows to those entries?


I would greatly appreciate the help. Thanks.

 

[svendiamond]

Get the Mr Excel HTML Maker from this link: http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

Follow the directions to install it and use it to copy and paste your data/tables into the forum so we can see it.

 

[mikerickson]

Change this section of code,

For n = End_Row To 1 Step -1
    Ins = Cells(n, 1).Value
    If Ins > 1 Then 
        Range("A" & n + 1 & ":A" & n + Ins).EntireRow.Insert
        [COLOR="#FF0000"]Cells(n,1).Value = 0[/COLOR]
    End If
Next n

It changes Cells(n,1) so the next time you press the button, the >1 test fails.
If you want to preserve the number for some other use, you might use Cells(n,1).Value = -1 * Ins, which is also not >1, but ABS will restore the data.