Auto row insertion based on a number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to calculate certain number of employee wages and have Excel
automatically total them up.

My case, I have in cell A3 to input the number of employee. How do I create
an auto row insertion if for example in A3 I put in 10, Excel will auto
insert 10 rows starting from A9 and A12 and to retain the formula originally
in row A9 and A12?

Any advice would be much appreciated. Thanks!

Eugene
 
Hi Eugene,

Firstly I need to confirm exactly what is required. If I understand
correctly, you want to insert 10 rows after the existing row 9 and then
another 10 rows after the existing row 12 (which will actually become row 22
after the first insertion.)

Then you want existing formulas in A9 to be copied to the A10, A11, A12 etc
and adjusted to take into account the changed rows. Similarly for the formula
in A12.

Please confirm if the above is correct and if it is then also post a copy of
the formulas in A9 and A12.

Also I am not really comfortable with your suggested method because it would
need a worksheet change event and if a value is entered accidently then the
inserts are going to take place. While a confirmation message could be
displayed, I would prefer a button to click and let the system ask how many
rows to insert and also give you the opportunity to cancel.

Regards,

OssieMac
 
Hi OssieMac,

Thank you for your reply. You have replied exactly to what I have wanted.
I need the row from A9 and A12 to be expanded accordingly to the number
which is entered in cell A3 and A6. Which means if I put 5 in cell A3, the
row in A9 will automatically increased to A9, A10, A11, A12 and A13. And the
same will happen to the row in A12 when a number is entered in cell A6.

My formula in row A9 as follow;
Cell B9 is =B3
Cell E9 is =IF(C9*0.8>6,B9*6,B9*C9*0.8)
Cell F9 is =A9*E9
**Cell A9 is an autonumber, C9 is the hourly salary which we will enter, D9
is an empty cell.

Rather similarly, my formula in row A12 as follow;
Cell B12 is =B6
Cell E12 is =IF(C12*0.9>6.8,B12*6.8,B12*C12*0.9)
Cell F12 is =A12*E12
**Cell A12 is an autonumber, C12 is the hourly salary which we will enter,
D12 is an empty cell.

The purpose of doing the auto row insertion method is because we only need
to key in the hourly salary in column C9 and C12 downwards, depending on the
number of employees in cell A3 and A6. I'm not too familiar with Excel and I
hope to learn something out of this, so do suggest an alternative if you have
a more simple method. I'm all ears. Cheers!

Regards,
Eugene
 
Hi again Eugene,

My apologies for not getting back to you sooner but unfortunately it was
beyond my control.

Make sure that you back up your workbook before copying or running the
macros below.

Firstly I think that you need to change your formula in cells B9 and B12 to
absolute addressing. That is:-

B9 should be =$B$3
B12 should be =$B$6

If you are not familiar with absolute addressing, it is so that =B3 will not
become =B4, =B5 etc as the formula is copied down the page.

I am not sure if you need to change any others but after you run a test and
insert some rows, then check carefully that your formulas refer to the
correct cells.

When running tests, you can simply delete the inserted rows and re-run if
you want to.

The initialize macro you will need to run ONCE ONLY manually but the other
one will run automatically when you change the value in either A3 or A6. It
will only run for the cell you change so if you change A3 then the rows below
row 9 are inserted and if you change A6 the rows below row 12 (or what was
row 12 before inserting from row 9) will change.

You will get a message to confirm that you want to insert the rows. This is
needed in case someone makes a change by error (If the number of rows appears
wrong then accept it during the test stages and we will worry about that
later). Note even if the cell contains 6 and if you insert 6 again then that
is a change which will run the macro and insert additional rows. (Inserting
additional rows is OK if that is what you want to do.)

Instructions to copy and run the macros:

Open the workbook and select the required worksheet. (It is essential that
you have the required worksheet selected.)

Press Alt/F11 to open the VBA editor.

Select menu item Insert then click Module and a white area will appear on
the right of the screen.

Copy the following macro and paste it into the white area. (Copy from Sub
Initialize to End Sub)


Sub Initialize()
Sheets("Sheet1").Select
Rows("9:9").Select
ActiveWorkbook.Names.Add Name:="Row_9", RefersToR1C1:="=Sheet1!R9"
Rows("12:12").Select
ActiveWorkbook.Names.Add Name:="Row_12", RefersToR1C1:="=Sheet1!R12"
MsgBox "Initialize has finished." & Chr(13) _
& "Click OK then close the VBA editor"
End Sub


The above macro only has to be run ONCE to initialize your worksheet by
naming the existing rows 9 and 12. To run the macro while still in the VBA
editor:-

1. Click anywhere within the macro.
2. Press F5.

You can now close the VBA editor. (Click the X in the red box far top right
of screen)

You should now be back to the worksheet.
Right click on the worksheet name tab.
Select View code and you will be back in the VBA editor but this time if you
look at the project explorer down the left side you will see that the
selection is the worksheet name.

If a Private Sub – End Sub appears with nothing in between then delete both
these lines. (Don’t panic if not there because that is OK.)

Copy the following macro and paste into the white space. (Copy from Private
Sub to End Sub)


Private Sub Worksheet_Change(ByVal Target As Range)
Dim row9 As Single
Dim row12 As Single
Dim insertRows As Single
Dim response As Variant

Select Case Target.Address
Case "$A$3"
'Following line determins number of rows to insert
insertRows = Target.Value - 1
response = MsgBox("Confirm that you want a total of " _
& Target.Value & " rows" & Chr(13) & _
"Cancel to abort", vbOKCancel)

If response = vbOK Then
row9 = Range("Row_9").Row
Rows(row9).Copy
Range(Rows(row9 + 1), _
Rows(row9 + insertRows)) _
.Insert Shift:=xlDown
End If

Case "$A$6"
'Following line determins number of rows to insert
insertRows = Target.Value - 1
response = MsgBox("Confirm that you want a total of " _
& Target.Value & " rows" & Chr(13) & _
"Cancel to abort", vbOKCancel)

If response = vbOK Then
row12 = Range("Row_12").Row
Rows(row12).Copy
Range(Rows(row12 + 1), _
Rows(row12 + insertRows)) _
.Insert Shift:=xlDown
End If
End Select

End Sub



You can now close the VBA editor and you should be back at your worksheet.

If you have not already done so then edit cells B9 and B12 so that they have
absolute references as per instructions above.

Change the value in cell A3 and your first set of lines should be inserted.
Repeat for cell A6.

Note that it inserts one less line than the number entered because if I
understand correctly, the total number of lines required includes the
original.

Check that your formulas look right.

Writing the instructions feel like writing a book but I like to feel
confident that I have included everything.

Feel free to get back to me if you need any changes. However, if you do then
please include the version of Excel are you using?

Regards,

OssieMac
 
Hi OssieMac,

Thank you so much for your reply. You definitely have no obligation to reply
me on my problem but you have spent the trouble to. I'm certain appreciative
of your help.

I have done what you have suggested and it works like a charm! It is
certainly what I am expecting to see. However there is one tiny problem to
fine tune.

Originally in Cell F9 and Cell F12 is a simple calculation of =A9*E9 and
=A12*E12 respectively. And the total add up of Cell F9 and Cell F12 is
reflected in Cell A16 (=F9+F12).

However, when the rows are auto inserted, the total add up doesn't much
tally as it doesn't take into account of the added cells F10, F11, F12, so on
and so on.

Is there any kind of formula which I can use to put in Cell A16 to track the
change in rows and do the total add up of newly inserted rows? Please let me
give an example.

When I put 3 in both Cell A3 and A6, the rows will auto expand, row 9 to 12
and row 14 to 16. I need to have the total add up of cells F9 to F12 and F14
to F16 and display it in Cell A20 (Originally it's Cell A16, before the rows
are inserted). But currently now it just adds up Cell F9 and Cell F14.

Would it be possible to fine tune on this? Sorry for the trouble again.

Regards,
Eugene Wong
 
Hi again Eugene,


Open the workbook and select the required worksheet.

Delete any rows that you have inserted below the original row 9 and row 12
so that you have only the original rows displayed.

Press Alt/F11 to open the VBA editor.

If Module1 where you put the Initialize macro is not open then open it from
the Project Explorer in the left column. (Expand Modules and double click
Module1)

Delete all of the existing Initialize macro and then copy the new one below
and paste it in.

Sub Initialize()
Sheets("Sheet1").Select
Rows("9:9").Select
ActiveWorkbook.Names.Add Name:="Row_9", RefersToR1C1:="=Sheet1!R9"
Rows("12:12").Select
ActiveWorkbook.Names.Add Name:="Row_12", RefersToR1C1:="=Sheet1!R12"
Range("F9").Select
ActiveWorkbook.Names.Add Name:="Cell_F9", RefersToR1C1:=ActiveCell
ActiveWorkbook.Names.Add Name:="Cell_F9_Last", RefersToR1C1:=ActiveCell
Range("F12").Select
ActiveWorkbook.Names.Add Name:="Cell_F12", RefersToR1C1:=ActiveCell
ActiveWorkbook.Names.Add Name:="Cell_F12_Last", RefersToR1C1:=ActiveCell
Range("A16").Select
ActiveCell.FormulaR1C1 = _
"=SUM(Cell_F9:Cell_F9_Last)+SUM(Cell_F12:Cell_F12_Last)"
MsgBox "Initialize has finished." & Chr(13) _
& "Click OK then close the VBA editor"
End Sub


Click anywhere within the module and press F5 to run it.

As before the above macro only has to be run once to initialize your
worksheet by naming ranges and cells.

You can now close the VBA editor.

You should now be back to the worksheet.

Right click on the worksheet name tab.

Select View code and you will be back in the VBA editor where you inserted
the last macro.

Delete the entire existing macro and copy the one below and paste it in.



Private Sub Worksheet_Change(ByVal Target As Range)

Dim row9 As Single
Dim row12 As Single
Dim insertRows As Single
Dim response As Variant

Select Case Target.Address
Case "$A$3"
'Following line determins number of rows to insert
insertRows = Target.Value - 1
If insertRows < 1 Then
MsgBox "Cannot enter less than 2 for total rows." _
& Chr(13) & "No rows inserted." & Chr(13) & _
"Processing terminated."
Exit Sub 'Abort processing
End If
response = MsgBox("Confirm that you want a total of " _
& Target.Value & " rows" & Chr(13) & _
"Cancel to abort", vbOKCancel)

If response = vbOK Then
row9 = Range("Row_9").Row
Rows(row9).Copy
Range(Rows(row9 + 1), _
Rows(row9 + insertRows)) _
.Insert Shift:=xlDown

Range("Cell_F9").Offset(Cells(3, 1) - 1, 0).Select
ActiveWorkbook.Names.Add Name:="Cell_F9_Last", _
RefersToR1C1:=ActiveCell

End If

Case "$A$6"
'Following line determins number of rows to insert
insertRows = Target.Value - 1
If insertRows < 1 Then
MsgBox "Cannot enter less than 2 for total rows." _
& Chr(13) & "No rows inserted." & Chr(13) & _
"Processing terminated."
Exit Sub 'Abort processing
End If
response = MsgBox("Confirm that you want a total of " _
& Target.Value & " rows" & Chr(13) & _
"Cancel to abort", vbOKCancel)

If response = vbOK Then
row12 = Range("Row_12").Row

Rows(row12).Copy
Range(Rows(row12 + 1), _
Rows(row12 + insertRows)) _
.Insert Shift:=xlDown

Range("Cell_F12").Offset(Cells(6, 1) - 1, 0).Select
ActiveWorkbook.Names.Add Name:="Cell_F12_Last", _
RefersToR1C1:=ActiveCell


End If
End Select
Application.CutCopyMode = False

End Sub


You can now close the VBA editor and you should be back at your worksheet.

Change the value in cell A3 and your first set of lines should be inserted.
Repeat for cell A6.

Check that your formulas look right. Your formula in cell A16 now references
named ranges. It is modified by the macro.

Feel free to get back to me if you need any changes. However, please include
the version of Excel are you using?

I have also added some validation because if you try to enter a number less
than 2 which inserts one extra row, then it tries to enter zero or negative
number of rows and comes up an error.

Regards,

OssieMac
 
Hi OssieMac,

It works perfectly. You have solved my problem. I cannot thank you more.
You are great! Looks like I have to enroll myself for Excel and Visual Basic
course soon...

Regards,
Eugene Wong
 
Back
Top