VBA Code to insert row after a specific Cell

F

FelixExcel

New Member
Joined
Sep 14, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hey all,

I created two command buttons in excel that both insert a row at different points in the excel sheet and add formulas and format the cells in that newly inserted row. I was very happy with how it worked until I noticed that when I use the upper button to insert a row at the top of my excel page, the second command button doesnt do its job correctly anymore, as the number of rows changed, so the second command button inserts the new row at the wrong place. In the code below you can see that the command buttons are linked to exact cell locations (A21 for example). Therefore, when a new row is added from the first command button cell locations from the second command button are wrong. Is there a way I can solve this? I already had some success using Cells.find("Auftrag").EntireRow.Insert but using this I dont know how to format the newly added row and add formulas to specific cells in that row.

Can someone help?

Private Sub CommandButton1_Click()

Sheets("Tabelle1").Range("A21").Select
ActiveCell.EntireRow.Insert Shift:=x1Down

Sheets("Tabelle1").Range("A21").Select
ActiveCell.Value = " Nachtrag"

Sheets("Tabelle1").Range("G21").Select
ActiveCell.Formula = "=(e21*0.19)"

Sheets("Tabelle1").Range("H21").Select
ActiveCell.Formula = "=(E21*1.19)"

Sheets("Tabelle1").Range("E21").Select
ActiveCell.Borders(xlEdgeLeft).Weight = xlThin

Sheets("Tabelle1").Range("E21").Select
ActiveCell.Borders(xlEdgeRight).Weight = xlThin

Sheets("Tabelle1").Range("E21").Select
ActiveCell.Borders(xlEdgeTop).Weight = xlThin

Sheets("Tabelle1").Range("E21").Select
ActiveCell.Borders(xlEdgeBottom).Weight = xlThin

Sheets("Tabelle1").Range("E21").Select
ActiveCell.Interior.ColorIndex = 24


End Sub

Private Sub CommandButton2_Click()

Sheets("Tabelle1").Range("A41").Select
ActiveCell.EntireRow.Insert Shift:=x1Down

Sheets("Tabelle1").Range("A41").Select
ActiveCell.Value = "Abschlagsrechnung"

Sheets("Tabelle1").Range("G41").Select
ActiveCell.Formula = "=(e41*0.19)"

Sheets("Tabelle1").Range("H41").Select
ActiveCell.Formula = "=(E41*1.19)"

Sheets("Tabelle1").Range("E41").Select
ActiveCell.Borders(xlEdgeLeft).Weight = xlThin

Sheets("Tabelle1").Range("E41").Select
ActiveCell.Borders(xlEdgeRight).Weight = xlThin

Sheets("Tabelle1").Range("E41").Select
ActiveCell.Borders(xlEdgeTop).Weight = xlThin

Sheets("Tabelle1").Range("E41").Select
ActiveCell.Borders(xlEdgeBottom).Weight = xlThin

Sheets("Tabelle1").Range("E41").Select
ActiveCell.Interior.ColorIndex = 24


End Sub
 

Attachments

  • 1.PNG
    1.PNG
    34.5 KB · Views: 16
  • 2.PNG
    2.PNG
    50.1 KB · Views: 18

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Can you elaborate on what is significant about rows 21 and 41 ?
For example using Auftrag in a find statement will get you row 20 but after you insert a row, using find would not work a second time.
If row 21 is significant is there nothing in it we could be searching for ? (if so in what column should we search)

Alternatively how about giving the significant rows a Range Name ?

Also are rows 21 and 41 merged cells (hopefully not) ?
 
Upvote 0
See if this gives you any ideas:

VBA Code: 
Private Sub CommandButton1_Click()

    Dim shtTab As Worksheet
    Dim searchFor As String, colToSearch As String
    Dim rowToUse As Long
   
    Set shtTab = Worksheets("Tabelle1")
    searchFor = "Auftrag"               ' <--- Change this to the relevant search word
    colToSearch = "A"                   ' <--- Change this to the column to search
   
    With Application
            rowToUse = .IfError(.Match("*" & searchFor & "*", shtTab.Columns(colToSearch), 0), 0)
    End With
    If rowToUse = 0 Then Exit Sub

    rowToUse = rowToUse + 1             ' <--- Assumes search term is row before required position hence + 1
    With shtTab
        .Range("A" & rowToUse).EntireRow.Insert

        .Range("A" & rowToUse).Value = " Nachtrag"
        .Range("G" & rowToUse).Formula = "=(E" & rowToUse & "*0.19)"
        .Range("H" & rowToUse).Formula = "=(E" & rowToUse & "*1.19)"

        With .Range("E" & rowToUse)
            .Borders(xlEdgeLeft).Weight = xlThin
            .Borders(xlEdgeRight).Weight = xlThin
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeBottom).Weight = xlThin
            .Interior.ColorIndex = 24
        End With
    End With

End Sub

Private Sub CommandButton2_Click()

    Dim shtTab As Worksheet
    Dim searchFor As String, colToSearch As String
    Dim rowToUse As Long
   
    Set shtTab = Worksheets("Tabelle1")
    searchFor = "Aktuelle"               ' <--- Change this to the relevant search word
    colToSearch = "A"                    ' <--- Change this to the column to search
   
    With Application
            rowToUse = .IfError(.Match("*" & searchFor & "*", shtTab.Columns(colToSearch), 0), 0)
    End With
    If rowToUse = 0 Then Exit Sub

    rowToUse = rowToUse + 1             ' <--- Assumes search term is row before required position hence + 1
    With shtTab
        .Range("A" & rowToUse).EntireRow.Insert

        .Range("A" & rowToUse).Value = "Abschlagsrechnung"
        .Range("G" & rowToUse).Formula = "=(E" & rowToUse & "*0.19)"
        .Range("H" & rowToUse).Formula = "=(E" & rowToUse & "*1.19)"

        With .Range("E" & rowToUse)
            .Borders(xlEdgeLeft).Weight = xlThin
            .Borders(xlEdgeRight).Weight = xlThin
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeBottom).Weight = xlThin
            .Interior.ColorIndex = 24
        End With
    End With

End Sub
 
Upvote 0
Alex Blakenburg said:
Can you elaborate on what is significant about rows 21 and 41 ?
For example using Auftrag in a find statement will get you row 20 but after you insert a row, using find would not work a second time.
If row 21 is significant is there nothing in it we could be searching for ? (if so in what column should we search)

Alternatively how about giving the significant rows a Range Name ?

Also are rows 21 and 41 merged cells (hopefully not) ?
Click to expand...
Sorry for the late respond alex. I solved the problem by myself. Thank you anyways for your response. :)
 
Upvote 0
Alex Blakenburg said:
See if this gives you any ideas:

VBA Code: 
Private Sub CommandButton1_Click()

    Dim shtTab As Worksheet
    Dim searchFor As String, colToSearch As String
    Dim rowToUse As Long
  
    Set shtTab = Worksheets("Tabelle1")
    searchFor = "Auftrag"               ' <--- Change this to the relevant search word
    colToSearch = "A"                   ' <--- Change this to the column to search
  
    With Application
            rowToUse = .IfError(.Match("*" & searchFor & "*", shtTab.Columns(colToSearch), 0), 0)
    End With
    If rowToUse = 0 Then Exit Sub

    rowToUse = rowToUse + 1             ' <--- Assumes search term is row before required position hence + 1
    With shtTab
        .Range("A" & rowToUse).EntireRow.Insert

        .Range("A" & rowToUse).Value = " Nachtrag"
        .Range("G" & rowToUse).Formula = "=(E" & rowToUse & "*0.19)"
        .Range("H" & rowToUse).Formula = "=(E" & rowToUse & "*1.19)"

        With .Range("E" & rowToUse)
            .Borders(xlEdgeLeft).Weight = xlThin
            .Borders(xlEdgeRight).Weight = xlThin
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeBottom).Weight = xlThin
            .Interior.ColorIndex = 24
        End With
    End With

End Sub

Private Sub CommandButton2_Click()

    Dim shtTab As Worksheet
    Dim searchFor As String, colToSearch As String
    Dim rowToUse As Long
  
    Set shtTab = Worksheets("Tabelle1")
    searchFor = "Aktuelle"               ' <--- Change this to the relevant search word
    colToSearch = "A"                    ' <--- Change this to the column to search
  
    With Application
            rowToUse = .IfError(.Match("*" & searchFor & "*", shtTab.Columns(colToSearch), 0), 0)
    End With
    If rowToUse = 0 Then Exit Sub

    rowToUse = rowToUse + 1             ' <--- Assumes search term is row before required position hence + 1
    With shtTab
        .Range("A" & rowToUse).EntireRow.Insert

        .Range("A" & rowToUse).Value = "Abschlagsrechnung"
        .Range("G" & rowToUse).Formula = "=(E" & rowToUse & "*0.19)"
        .Range("H" & rowToUse).Formula = "=(E" & rowToUse & "*1.19)"

        With .Range("E" & rowToUse)
            .Borders(xlEdgeLeft).Weight = xlThin
            .Borders(xlEdgeRight).Weight = xlThin
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeBottom).Weight = xlThin
            .Interior.ColorIndex = 24
        End With
    End With

End Sub
Click to expand...
Alex Blakenburg said:
See if this gives you any ideas:

VBA Code: 
Private Sub CommandButton1_Click()

    Dim shtTab As Worksheet
    Dim searchFor As String, colToSearch As String
    Dim rowToUse As Long
  
    Set shtTab = Worksheets("Tabelle1")
    searchFor = "Auftrag"               ' <--- Change this to the relevant search word
    colToSearch = "A"                   ' <--- Change this to the column to search
  
    With Application
            rowToUse = .IfError(.Match("*" & searchFor & "*", shtTab.Columns(colToSearch), 0), 0)
    End With
    If rowToUse = 0 Then Exit Sub

    rowToUse = rowToUse + 1             ' <--- Assumes search term is row before required position hence + 1
    With shtTab
        .Range("A" & rowToUse).EntireRow.Insert

        .Range("A" & rowToUse).Value = " Nachtrag"
        .Range("G" & rowToUse).Formula = "=(E" & rowToUse & "*0.19)"
        .Range("H" & rowToUse).Formula = "=(E" & rowToUse & "*1.19)"

        With .Range("E" & rowToUse)
            .Borders(xlEdgeLeft).Weight = xlThin
            .Borders(xlEdgeRight).Weight = xlThin
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeBottom).Weight = xlThin
            .Interior.ColorIndex = 24
        End With
    End With

End Sub

Private Sub CommandButton2_Click()

    Dim shtTab As Worksheet
    Dim searchFor As String, colToSearch As String
    Dim rowToUse As Long
  
    Set shtTab = Worksheets("Tabelle1")
    searchFor = "Aktuelle"               ' <--- Change this to the relevant search word
    colToSearch = "A"                    ' <--- Change this to the column to search
  
    With Application
            rowToUse = .IfError(.Match("*" & searchFor & "*", shtTab.Columns(colToSearch), 0), 0)
    End With
    If rowToUse = 0 Then Exit Sub

    rowToUse = rowToUse + 1             ' <--- Assumes search term is row before required position hence + 1
    With shtTab
        .Range("A" & rowToUse).EntireRow.Insert

        .Range("A" & rowToUse).Value = "Abschlagsrechnung"
        .Range("G" & rowToUse).Formula = "=(E" & rowToUse & "*0.19)"
        .Range("H" & rowToUse).Formula = "=(E" & rowToUse & "*1.19)"

        With .Range("E" & rowToUse)
            .Borders(xlEdgeLeft).Weight = xlThin
            .Borders(xlEdgeRight).Weight = xlThin
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeBottom).Weight = xlThin
            .Interior.ColorIndex = 24
        End With
    End With

End Sub
Click to expand...
Thanks again for the big effort Alex, sorry for my late response :(
 
Upvote 0
Thanks for following up on this. If you want to post what you came up to solve this, it may help others and you can mark it as the solution.

If you do post code, please use the VBA buttton on the toolbar so the code retains its formatting and is easier for others to read.
 
Upvote 0
Alex Blakenburg said:
Thanks for following up on this. If you want to post what you came up to solve this, it may help others and you can mark it as the solution.

If you do post code, please use the VBA buttton on the toolbar so the code retains its formatting and is easier for others to read.
Click to expand...
The way I solved it works but it is not really a good way to solve it. I just added 200 rows to the excel sheet and gave them the height 0 so they are not visiable. Now, whenever I press the command button to add a new row it also deletes one of the 200 invisable rows. This way the number of rows in the excel always stays the same.
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
345
RoryA
RoryA
P
  • Question
Issue with button not running in spread sheet only in test mode
Replies
1
Views
202
6StringJazzer
6StringJazzer
R
  • Question
Macro not working after adding two new items
Replies
4
Views
128
myall_blues
myall_blues
F
  • Solved
VBA Inserting a row
2
Replies
11
Views
988
mumps
mumps
S
  • Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
348
sspatriots
S

Forum statistics

Threads
1,215,228
Messages
6,123,747
Members
449,118
Latest member
kingjet

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top