Excel - insert row & copy values & formulas from row above

rradelet · Feb 3, 2005

I am a VB illiterate and need to create a macro in Excel 2000 that will do the following:

1) Find the value 234 in column B.
2) Insert 1 row below that row.
3) Copy the value from column A from the row above the
new row.
4) Insert the value 234A in column B.
5) Copy the values from columns C D E F G from the row above
and add 5 to each of those values.
6) Copy the formulas from columns H I J K L from the row
above.
7) Repeat steps 1 to 6 to the last row (first empty row)

See before & after example below:

Before:

101 232 21.50 35.75 54.00 75.50 93.50 36 79 96 90 94
101 233 21.50 37.75 59.50 81.00 91.50 36 83 99 96 98
101 234 10.75 16.25 20.50 30.25 45.25 13 27 38 40 49
101 235 10.75 16.25 20.50 30.25 45.25 13 27 38 40 49

After:

101 232 21.50 35.75 54.00 75.50 93.50 36 79 96 90 94
101 233 21.50 37.75 59.50 81.00 91.50 36 83 99 96 98
101 234 10.75 16.25 20.50 30.25 45.25 13 27 38 40 49
101 234A 15.75 21.25 25.50 35.25 50.25 18 32 43 48 54
101 235 10.75 16.25 20.50 30.25 45.25 13 27 38 40 49

TonyJollans · Feb 3, 2005

Hi rradelet,

As a first step, try using the Macro Recorder to record yourself doing it manually. When you have some code someone will be able to help you tweak it.

- Select Tools > Macros > Record New Macro from the Menu.
- Do your stuff - and do all of it in Excel. Don't skip the search just because you can see the row you want.
- Press the button on the Stop Recording Toolbar
- Go into the VBE and look at your code, try running it, and come back when you're stuck.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

rradelet · Feb 3, 2005

I started by recording a macro, but I don't know how to
edit the result so that the macro doesn't execute using only
the original cell coordinates.

Here is the result of the recorded macro:

Sub InsertRow()
'
' InsertRow Macro
' Macro recorded 2/03/2005 by Richard Radelet
'
' Keyboard Shortcut: Ctrl+r
'
Range("B2").Select
Cells.Find(What:="234", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
Range("B124").Select
Selection.EntireRow.Insert
Range("A123").Select
Selection.Copy
Range("A124").Select
ActiveSheet.Paste
Range("B124").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = """234A"
Range("C124").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+5"
Range("C124").Select
Selection.Copy
Range("D124:G124").Select
ActiveSheet.Paste
Range("H123:L123").Select
Application.CutCopyMode = False
Selection.Copy
Range("H124").Select
ActiveSheet.Paste
Range("B124").Select
Cells.FindNext(After:=ActiveCell).Activate
End Sub

TonyJollans · Feb 3, 2005

Hi rradelet,

OK, you've got a start. It's generally best to avoid using Select and Activate but recorded macros are full of them and that can be changed later so, for now, let's look at what you've got and generalise it..

Code:

    Range("B2").Select
    Cells.Find(What:="234", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
        .Activate

This is OK and activates the cell with 234 in. It does, however, (a) look in the whole sheet instead of just column B and (b) finds, for example, 1234 if it exists before 234. I would change it, and I got this by recording precisely what I wanted:

Code:

    Columns("B:B").Select
    Selection.Find(What:="234", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

The next step, unfortunately, doesn't record and there are several ways to go to the cell below. Although not the most obvious, I like to use the shortcut:

Code:

Activecell(2,1).select

but it is probably better to use

Code:

Activecell.offset(1,0).select

After this you can insert your row as recorded. And then instead of explicitly selecting the cell in column A, select it relative to the current cell:

Code:

ActiveCell.offset(0,-1).Select

Next, instead of copying and pasting, enter a formula

Code:

ActiveCell.FormulaR1C1 = "=R[-1]C"

Now move one cell to the right:

Code:

ActiveCell.offset(0,1).Select

and enter a formula there

Code:

ActiveCell.FormulaR1C1 = "=R[-1]C&""A"""

Repeat the process again for the cell in column C

Code:

ActiveCell.offset(0,1).Select
ActiveCell.FormulaR1C1 = "=R[-1]C+5"

Now, instead of copying and pasting, it is easier to Autofill (bothin Excel and in VBA):

Code:

Selection.AutoFill Destination:=Selection.Resize(1, 5)

Now, column C is still selected so we need to move to column H and to the row above to copy the formula

Code:

ActiveCell.offset(-1,5).Select

and then, using autofill, copy the formula

Code:

Selection.autofill Selection.Resize(2, 1)

You could then repeat column by column as above but it is easier to do the autofill for all 4 columns in one

Code:

Selection.Resize(1, 4).AutoFill Selection.Resize(2, 4)

Put all that together and you get this:

Code:

[blue]    Columns("B:B").Select
    Selection.Find(What:="234", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    Selection.EntireRow.Insert
    ActiveCell.Offset(0, -1).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C&""A"""
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+5"
    Selection.AutoFill Destination:=Selection.Resize(1, 5)
    ActiveCell.Offset(-1, 5).Select
    Selection.Resize(1, 4).AutoFill Selection.Resize(2, 4)[/blue]

That's not the best explanation I've ever given but I hope it helps. Come back if you have any questions.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

rradelet · Feb 3, 2005

Thanks Tony.

I have tweaked what you provided and now get most of the
result that I need and executing the macro performs one update after the first instance of 234.

I changed the line:

ActiveCell.FormulaR1C1 = "=R[-1]C&""A"""

to

ActiveCell.FormulaR1C1 = """234A"

since I will later use this to insert for other values
(201S etc.) I will edit the value as needed.

I still have 2 problems:

1) I get a "Runtime error 1004 - application-defined or
object-defined error if the macro is executed after the
last instance of 234 occurs in column B.

2) How do I make the macro continue through to insert and
update rows for all the instances of the value 234
in Column B?

Below is the code so far:

Sub InsertZone()
'
' InsertZone Macro
' Macro recorded 2/2/2005 by Richard Radelet
'
' Keyboard Shortcut: Ctrl+r
'
Cells.Find(What:="234", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate

ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = """234A"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=R[-1]C+5"
Selection.AutoFill Destination:=Selection.Resize(1, 5)
ActiveCell.Offset(-1, 5).Select
Selection.Resize(1, 5).AutoFill Selection.Resize(2, 5)
ActiveCell.Offset(1, -6).Select

End Sub

TonyJollans · Feb 4, 2005

Hi Richard,

To take your issues one at a time. The error occurs because the code assumes the value 234 will always be found. If that's not the case then it needs to check for the situation.

The Find statement: [blue]Cells.Find(What:="234", ....)[red].Activate[/red][/blue] is a shortcut. It runs the Find and then Activates the Cell found. If no cell is found, the Find returns Nothing, which can't be activated. If you split the line into its two parts:

Code:

    [blue]Dim FoundCell As Range
    Set FoundCell = [/blue]Cells.Find(What:="234", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False)
    [blue]FoundCell.[/blue]Activate

you can then add a check for Nothing:

Code:

    Dim FoundCell As Range
    Set FoundCell = Cells.Find(What:="234", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False)
    [blue]If FoundCell is Nothing Then[/blue]
        [green]' Some sort of error logic or a message - and quit[/green]
    [blue]Else[/blue]
        FoundCell.Activate
        [green]' And go on to do the rest of your stuff[/green]
    [blue]End If[/blue]

Your second issue, to do with looping, needs an understanding of how Find operates. It looks forward, starting after the active cell and when it gets to the end, it starts at the beginning again. In other words it never returns any indication that it has found everything, it just keeps on going. You must, therefore, code to remember the first cell found and stop when it is found again, something like this

Code:

    Dim FirstCellFound As String
    Set FoundCell = Cells.Find(What:="234", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False)
    [blue]If FirstCellFound = "" Then [green]' First Time[/green]
        If FoundCell is Nothing Then
            [green]' Some sort of error logic or a message - and quit[/green]
        Else
            FirstCellFound = FoundCell.Address [green]' Save First Cell Found[/green]
        End If
    Else [green]' Every Time except first time[/green]
        If FirstCellFound = FoundCell.Address Then
            [green]' All done - just quit?[/green]
        Else
            [green]' Another occurrence found - continue with code[/green]
        End If
    End If[/blue]
    FoundCell.Activate
    [green]' And go on to do the rest of your stuff[/green]

The last thing to do is to put all the code in a big loop. As there is code in the above logic to break out at end, the whole code can just be wrapped in Do .. Loop:

Code:

Do

    Dim FirstCellFound As String
    Set FoundCell = Cells.Find(What:="234", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False)
    If FirstCellFound = "" Then [green]' First Time[/green]
        If FoundCell is Nothing Then
            [green]' Some sort of error logic or a message[/green]
            [blue]Exit Do[/blue][green]' Exit[/green]
        Else
            FirstCellFound = FoundCell.Address [green]' Save First Cell Found[/green]
        End If
    Else [green]' Every Time except first time[/green]
        If FirstCellFound = FoundCell.Address Then
            [green]' All done[/green]
            [blue]Exit Do[/blue][green]' Exit[/green]
        Else
            [green]' Another occurrence found - continue with code[/green]
        End If
    End If
    FoundCell.Activate

    ActiveCell.Offset(1, 0).Select
    Selection.EntireRow.Insert
    ActiveCell.Offset(0, -1).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = """234A"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+5"
    Selection.AutoFill Destination:=Selection.Resize(1, 5)
    ActiveCell.Offset(-1, 5).Select
    Selection.Resize(1, 5).AutoFill Selection.Resize(2, 5)
    ActiveCell.Offset(1, -6).Select

Loop[green] ' Just keep going round[/green]

I have not tested this - just typed it in - so may have made a mistake or two. My apologies if so; please come back with any further questions.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Excel - insert row & copy values & formulas from row above

rradelet

IS-IT--Management

TonyJollans

Programmer

rradelet

IS-IT--Management

TonyJollans

Programmer

rradelet

IS-IT--Management

TonyJollans

Programmer

Similar threads

Part and Inventory Search

Sponsor