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, ...Staff12

Please, help.


See More: EXCEL-Insert Rows Below Based On Criteria

Report •


#1
May 3, 2016 at 05:27:30
✔ Best Answer
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 useful

What the code does is
Gets the number of rows to insert from sheet1 range C24
Inserts the rows in sheet2 C9
Enters the staff number in column B from C9 to number of rows inserted
Enters 1000 in all rows bar the last
Enters 800 in the last inserted row

I 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


Report •
Related Solutions


Ask Question