# Solved EXCEL-Insert Rows Below Based On Criteria

April 23, 2016 at 10:53:26
Specs: Windows 7
 Good aft all,I'm not a good user of VBA but I have a task to deliver on monday. What I need is a macro that will look at the value in C24 (say C24=12), and based on that value, insert a number of rows (say 12 rows) below C8 in sheet2 of the same workbook, and fill in the newly inserted 12 rows with data. Assuming: - (A1=11 800) - each of the 12 rows represents one staff and each staff cannot handle more than 1000 units,I want to put in C9, C10, ...C20 of sheet 2, the values 1 000, 1 000, 1000 ....... 800 so that the total sum up to 11 800, ...... and in B9, B10, ...B20 of sheet 2 the Staffs: Staff1, Staff2, ...Staff12Please, help.

See More: EXCEL-Insert Rows Below Based On Criteria

#1
May 3, 2016 at 05:27:30
 This solution works, may not be very pretty but I does what you ask. I know its late as you needed it last week, but it may still be usefulWhat the code does isGets the number of rows to insert from sheet1 range C24Inserts the rows in sheet2 C9Enters the staff number in column B from C9 to number of rows insertedEnters 1000 in all rows bar the lastEnters 800 in the last inserted rowI assume this is what you wanted please let us know if this works```Sub InsertRowsAndData() Dim StaffNum As Integer Dim Bcell As Range Dim LastRow As Integer StaffNum = 1 For i = 1 To Range("C24").Value Sheet2.Range("C9").EntireRow.Insert 2 Next i LastRow = 9 + Range("C24").Value - 1 For Each Bcell In Sheet2.Range("B9:B" & LastRow) Bcell.Value = "Staff" & StaffNum StaffNum = StaffNum + 1 Next Bcell Sheet2.Range("C9:C" & LastRow - 1).Value = 1000 Sheet2.Range("C" & LastRow).Value = 800 End Sub ```You can also add the code under the Worksheet_Change event so that whenever you change the number in C24 the code kicks in```Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "\$C\$24" Then Dim StaffNum As Integer Dim Bcell As Range Dim LastRow As Integer StaffNum = 1 For i = 1 To Range("C24").Value Sheet2.Range("C9").EntireRow.Insert 2 Next i LastRow = 9 + Range("C24").Value - 1 For Each Bcell In Sheet2.Range("B9:B" & LastRow) Bcell.Value = "Staff" & StaffNum StaffNum = StaffNum + 1 Next Bcell Sheet2.Range("C9:C" & LastRow - 1).Value = 1000 Sheet2.Range("C" & LastRow).Value = 800 End If End Sub```message edited by AlwaysWillingToLearn