0Hi All,
This is a requirement from one of our student and here we are posting the question along with solution (this can be achieved using 4 different loops). In Column “J” there are few employee names which need to be repeated for “n” (this “n” is in cell “O1”) number of times in column “L”. Check the below screenshot for understanding the requirement.
Above requirement can be done using 4 different VBA loops. Here you go
Solving the above task using 1. For Next Loop
Sub EmpName_Recurring1()
Dim emp_loop As Integer
Dim source_lr As Integer
Dim destination_lr As Integer
ThisWorkbook.Sheets("Task").Range("L2:L10000").ClearContents
source_lr = ThisWorkbook.Sheets("Task").Cells(Rows.Count, "J").End(xlUp).Row
For emp_loop = 1 To source_lr
destination_lr = ThisWorkbook.Sheets("Task").Cells(Rows.Count, "L").End(xlUp).Row + 1
Range(Cells(destination_lr, "L"), Cells(destination_lr + Range("Repeat_Count").Value - 1, "L")).Value = Cells(emp_loop + 1, "J").Value
Next emp_loop
MsgBox "Task completed using For Next loop"
End Sub
Solving the above task using 2. For Each Loop
Sub EmpName_Recurring2()
Dim rng As Range
Dim source_lr As Integer
Dim destination_lr As Integer
ThisWorkbook.Sheets("Task").Range("L2:L10000").ClearContents
source_lr = ThisWorkbook.Sheets("Task").Cells(Rows.Count, "J").End(xlUp).Row
For Each rng In Sheets("Task").Range("J2:J" & source_lr)
destination_lr = ThisWorkbook.Sheets("Task").Cells(Rows.Count, "L").End(xlUp).Row + 1
Range(Cells(destination_lr, "L"), Cells(destination_lr + Range("Repeat_Count").Value - 1, "L")).Value = rng.Value
Next rng
MsgBox "Task completed using For Each loop"
End Sub
Solving the above task using 3. Do While Loop
Sub EmpName_Recurring3()
Dim rng As Range
Dim row_increment As Integer
Dim destination_lr As Integer
ThisWorkbook.Sheets("Task").Range("L2:L10000").ClearContents
Do While Cells(row_increment + 2, "J").Value <> ""
destination_lr = ThisWorkbook.Sheets("Task").Cells(Rows.Count, "L").End(xlUp).Row + 1
Range(Cells(destination_lr, "L"), Cells(destination_lr + Range("Repeat_Count").Value - 1, "L")).Value = Cells(row_increment + 2, "J").Value
row_increment = row_increment + 1
Loop
MsgBox "Task completed using Do While loop"
End Sub
Solving the above task using 4. Do Until Loop
Sub EmpName_Recurring4()
Dim rng As Range
Dim row_increment As Integer
Dim destination_lr As Integer
ThisWorkbook.Sheets("Task").Range("L2:L10000").ClearContents
Do Until Cells(row_increment + 2, "J").Value = ""
destination_lr = ThisWorkbook.Sheets("Task").Cells(Rows.Count, "L").End(xlUp).Row + 1
Range(Cells(destination_lr, "L"), Cells(destination_lr + Range("Repeat_Count").Value - 1, "L")).Value = Cells(row_increment + 2, "J").Value
row_increment = row_increment + 1
Loop
MsgBox "Task completed using Do Until loop"
End Sub
