Forum Discussion
Nested Loop
Hi, I am trying to do a nested Loop with a Do Until and a While. See attached. when starting the loop it ignores the outside loop but do the inside loop perfectly. Outside loop to be done fo...
If order isn't important, and assuming your names are in B4:B7, number of tickets is in Column H, and you want the list to start in O3 (change ranges as needed), then you could try:
Sub test()
Dim cell As Range
Dim dest As Range
Set dest = Range("O3")
For Each cell In Range("B4:B7")
If Len(dest.Value) > 0 Then
Set dest = Cells(Rows.Count, dest.Column).End(xlUp)(2, 1)
End If
dest.Resize(Intersect(cell.EntireRow, Range("H:H")).Value, 1).Value = cell.Value
Next cell
End Sub
Thanks for your help. It works perfectly. I just had to remove the "end if" statement. It presented a compile error " End If without block If "
In your answer, how did you tell the system to dublicate the name eg 7 times ? I don't get that part of the makro.
Regards
- By resizing the range: destCell.Resize(rowsize, colsize) where rowsize equals the value that is in Column H. Excel can read/write a block of cells at once - it's Range("O3:O9").Value = "Name" instead of doing one cell at a time.