I have the follwing code which will the data from sheet1 and count the occurence in sheet 2 and then copy the data multiple times based on that countif value. But Instead of just copying it needs to insert rows and then copy the data based on the countif value from sheet 2. Can anyone help me on this?
Sub test()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Byte, StartCell As Range
Dim nList As Range, Ce As Range
Dim LastRow As Range
Set ws1 = Sheets("Sheet2")
Set ws2 = Sheets("Sheet1")
Set nList = ws2.Range("C5:C9")
Set StartCell = ws2.Range("D5")
For Each Ce In nList
i = Application.WorksheetFunction.CountIf(ws1.Columns(1), Ce)
If i > 0 Then StartCell.Rows.Resize(i, 1) = Ce
Set StartCell = StartCell.Offset(i, 0)
Next Ce
End Sub
How I am getting the result is:
Expected is :
Sub test()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Byte, StartCell As Range
Dim nList As Range, Ce As Range
Dim LastRow As Range
Set ws1 = Sheets("Sheet2")
Set ws2 = Sheets("Sheet1")
Set nList = ws2.Range("C5:C9")
Set StartCell = ws2.Range("D5")
For Each Ce In nList
i = Application.WorksheetFunction.CountIf(ws1.Columns(1), Ce)
If i > 0 Then StartCell.Rows.Resize(i, 1) = Ce
Set StartCell = StartCell.Offset(i, 0)
Next Ce
End Sub
How I am getting the result is:
Expected is :